INTO, BULK COLLECT INTO y NO_DATA_FOUND

El comportamiento de SELECT … INTO y SELECT … BULK COLLECT INTO en PL/SQL es muy diferente en cuanto al tratamiento de errores/excepciones (y en particular en lo referente al famoso NO_DATA_FOUND)

Si estamos haciendo un simple SELECT … INTO en una variable, se lanzará la excepción NO_DATA_FOUND si dicha SELECT no devuelve datos. Pero, por el contrario, si estamos haciendo un SELECT … BULK COLLECT INTO en una colección pero no se encuentran datos, el motor PL/SQL silenciosamente continuará la ejecución sin levantar una excepción. En realidad, se limitará a ‘inicializar’ la colección a NULL.

Veamos un ejemplo:

sql> DECLARE

2 SUBTYPE EJEMPLO IS VARCHAR2(1);

3 TYPE TAB_EJEMPLO IS TABLE OF EJEMPLO;

4 v_EJEMPLO EJEMPLO;>

5 v_TAB_EJEMPLO TAB_EJEMPLO;

6 v_traza VARCHAR2(25);

7 BEGIN

8 v_traza := 'BULK COLLECT INTO';

9 SELECT DUMMY

10 BULK COLLECT

11 INTO v_TAB_EJEMPLO

12 FROM DUAL

13 WHERE 1 = 0;

14 dbms_output.put_line('No error en ' || v_traza);

15 dbms_output.put_line('v_TAB_EJEMPLO.COUNT: ' || v_TAB_EJEMPLO.COUNT);

16 v_traza := 'INTO';

17 SELECT DUMMY

18 INTO v_EJEMPLO

19 FROM DUAL

20 WHERE 1 = 0;

21 dbms_output.put_line('No error en ' || v_traza);

22 EXCEPTION

23 WHEN NO_DATA_FOUND THEN

24 dbms_output.put_line('NO_DATA_FOUND en ' || v_traza);

25 --Solo saltará en el SELECT ... INTO

26 END;

27 /

No error en BULK COLLECT INTO

v_TAB_EJEMPLO.COUNT: 0

NO_DATA_FOUND en INTO

Procedimiento PL/SQL terminado correctamente.

sql>

Así pues, debemos cambiar el tratamiento del retorno de los SELECT para los BULK COLLECT: en vez de capturar la excepción de NO_DATA_FOUND, deberemos preguntar por el COUNT de la colección receptora.

Saludos.

Carlos.

Anuncios

2 Responses to INTO, BULK COLLECT INTO y NO_DATA_FOUND

  1. KAREN dice:

    Precisamente, estaba buscando referencias sobre como programar en PL usando el BULK COLLECT INTO (el cual es muchisimo mas rapido que usar CURSORES y luego FOR…LOOP). Recientemente lo descubri ya que estoy trabajando en una Migración de Datos, la cual no sera una vulgar copia de tablas de un ambiente a otro, si no que hay que seleccionar muestras y depurarlas previamente y ya solo los datos depurados son los que seran insertados en tablas destino. En este momento nuestros programas usan CURSORES y luego mediante FOR..LOOP efectuamos los INSERT en las tablas, pero vemos que al mover gran cantidad de registros se nos consume mucho tiempo por lo que buscamos otras alternativas que mejoren el rendimiento y asi dimos con el tema en cuestion, el BULK COLLECT INTO.

    Ahora mi consulta, como hago con el manejo de las excepciones de PL / Oracle, en particular : WHEN DUP_VAL_ON_INDEX, WHEN OTHERS,… tambien buscaba el manejo del WHEN NO_DATA_FOUND pero tomare el ejemplo publicado aqui, pero como hago con el resto? cuales serian sus equivalentes?.

    Espero puedan aclararme, gracias.

  2. carlosal dice:

    KAREN:

    La principal ventaja del ‘BULK COLLECT’ es evitar los continuos cambios de contexto ( ‘switch context’ ) entre el motor SQL y el motor PL/SQL.

    De todas las maneras, ¿has intentado hacerlo con INSERT…SELECT? En la mayoría de los casos suele ser lo más rápido.

    En cuanto a las excepciones, deberás tratarlas como siempre.

    Una cosa: si vas a hacer un BULK…COLLECT INTO y luego vas a hacer un
    FORALL i IN 1…COUNT
    INSERT…
    deberías utilizar SAVE EXCEPTIONS, que, en vez de abortar el proceso INSERT en cada error, guarda los errores en una tabla SQL%BULK_EXCEPTIONS, que deberás tratar más tarde en la sección EXCEPTION

    Saludos.

    Carlos.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: