Valor NULL en sentencia IF

Necesitamos evaluar si contenido de una variable denominada nombre contiene la cadena de caracteres 'JAVIER'.
En el primer ejemplo asignamos a la variable nombre el valor NULL. La condición nombre != 'JAVIER' se evalúa como NULL != 'JAVIER' y resulta FALSA! El valor NULL es distinto de la cadena de caracteres 'JAVIER', sin embargo la ejecución se bifurca por la rama del ELSE. Se muestra el mensaje 'CONDICION FALSA, SON IGUALES. EJECUTA ELSE.'. Dónde está el error?

SQL> RUN
  1  DECLARE
  2     nombre VARCHAR2(40) := NULL; 
  3  BEGIN
  4     IF nombre != 'JAVIER' THEN
  5        DBMS_OUTPUT.PUT_LINE('CONDICION VERDADERA, SON DISTINTOS. EJECUTA THEN.');
  6     ELSE
  7        DBMS_OUTPUT.PUT_LINE('CONDICION FALSA, SON IGUALES. EJECUTA ELSE.');
  8     END IF;
  9* END;
 
CONDICION FALSA, SON IGUALES. EJECUTA ELSE.
 
Procedimiento PL/SQL terminado con éxito.

En el segundo ejemplo asignamos a la variable nombre la cadena de caracteres 'NULL'. La condición nombre != 'JAVIER' se evalúa como una comparación entre dos cadenas de caracteres 'NULL' != 'JAVIER' y resulta VERDADERA! Se muestra el mensaje 'CONDICION VERDADERA, SON DISTINTOS. EJECUTA THEN.'.

SQL> RUN
  1  DECLARE
  2     nombre VARCHAR2(40) := 'NULL';
  3  BEGIN
  4     IF nombre != 'JAVIER' THEN
  5        DBMS_OUTPUT.PUT_LINE('CONDICION VERDADERA, SON DISTINTOS. EJECUTA THEN.');
  6     ELSE
  7        DBMS_OUTPUT.PUT_LINE('CONDICION FALSA, SON IGUALES. EJECUTA ELSE.');
  8     END IF;
  9* END;
 
CONDICION VERDADERA, SON DISTINTOS. EJECUTA THEN.
 
Procedimiento PL/SQL terminado con éxito.

La clave es entender que la evaluación de condiciones que contienen al menos un elemento con valor NULL, SIEMPRE resultan FALSAS. La única excepción es la cláusula IS NULL.

NULL != 'JAVIER' es FALSO
NULL = 'JAVIER' es FALSO
NULL = NULL es FALSO

NULL IS NULL es VERDADERO
NULL IS NOT NULL es FALSO

En el tercer ejemplo tomamos precauciones para no repetir el comportamiento inesperado del primer ejemplo. Modificamos la línea 4 de evaluación de la condición agregando la función NVL que reemplazará los valores nulos de la variable nombre por la cadena de caracteres 'VALOR NULO'. Ahora si, la condición 'VALOR NULO' != 'JAVIER' es VERDADERA.

SQL> RUN
  1  DECLARE
  2     nombre VARCHAR2(40) := NULL;
  3  BEGIN
  4     IF NVL(nombre,'VALOR NULO') != 'JAVIER' THEN
  5        DBMS_OUTPUT.PUT_LINE('CONDICION VERDADERA, SON DISTINTOS. EJECUTA THEN.');
  6     ELSE
  7        DBMS_OUTPUT.PUT_LINE('CONDICION FALSA, SON IGUALES. EJECUTA ELSE.');
  8     END IF;
  9* END;
 
CONDICION VERDADERA, SON DISTINTOS. EJECUTA THEN.
 
Procedimiento PL/SQL terminado con éxito.

De FULL TABLE SCAN a INDEX RANGE SCAN

Necesitamos reducir el tiempo de ejecución de un SELECT que accede a una tabla filtrando filas por la columna definida PRIMIRY KEY que posee un índice único.
La tabla ESTADO_APLICACION posee una fila por cada medición de disponibilidad de una aplicación. Cada fila de la tabla detalla la fecha y hora de medición (columna FECHA) y el valor "ACTIVA" o "INACTIVA" (columna ESTADO) según correponda al resultado del proceso de verificación de disponibilidad de la aplicación.
Veamos un ejemplo del contenido de esta tabla y las disponibilidades registradas:

SQL> RUN
  1  SELECT TO_CHAR(FECHA,'DD/MM/YYYY HH24:MI:SS') AS FECHA,
  2         ESTADO
  3    FROM ESTADO_APLICACION
  4*  WHERE ROWNUM < 11
 
FECHA               ESTADO
------------------- ----------------
23/11/2007 20:24:34 ACTIVA
23/11/2007 20:23:34 INACTIVA
23/11/2007 20:22:34 INACTIVA
23/11/2007 20:21:34 INACTIVA
23/11/2007 20:20:34 ACTIVA
23/11/2007 20:19:34 ACTIVA
23/11/2007 20:18:34 ACTIVA
23/11/2007 20:17:34 ACTIVA
23/11/2007 20:16:34 ACTIVA
23/11/2007 20:15:34 ACTIVA
 
10 filas seleccionadas.

El objetivo de la sentencia SELECT es contar la cantidad de filas con fecha 23/11/2007. La columna FECHA posee un índice único y la sentencia SELECT filtra las filas seleccionadas por esta misma columna. Sin embargo el optimizador de ejecución de consultas decide recorrer toda la tabla para recuperar las filas seleccionadas. Ejecutamos la sentencia SELECT sin recuperar las filas pero mostrando el plan de ejecución del optimizador para recuperarlas configurando SET AUTOTRACE TRACEONLY EXPLAIN. Podemos observar que se accederá de manera TABLE ACCESS FULL al la tabla ESTADO_APLICACION para ejecutar la sentencia SELECT. Es decir, se leerán en forma secuencial todos los bloques de datos asociados a las filas de la tabla y se evaluará el criterio de selección a cada fila. Estas lecturas pueden representar un gran volumen de transferencias de datos de entrada aumentando el tiempo de ejecución de la sentencia SELECT.

SQL>  SET AUTOTRACE TRACEONLY EXPLAIN
 
SQL> RUN
 1 SELECT COUNT(*)
 2   FROM ESTADO_APLICACION
 3* WHERE TO_CHAR(FECHA,'DD/MM/YYYY')  = '23/11/2007'
 
Plan de Ejecucion
----------------------------------------------------------
Plan hash value: 111243894
 
-----------------------------------------------------------------------------
| Id|Operation          | Name              |Rows|Bytes|Cost (%CPU)|Time    |
-----------------------------------------------------------------------------
|  0|SELECT STATEMENT   |                   |   1|    9|   24   (5)|00:00:01|
|  1| SORT AGGREGATE    |                   |   1|    9|           |        |
|* 2|  TABLE ACCESS FULL|ESTADO_APLICACION  |1829|16461|   24   (5)|00:00:01|
-----------------------------------------------------------------------------

El optimizador de ejecución de sentencias no puede utilizar el índice sobre la columna FECHA evaluada por el criterio de selección porque esta columna es convertida del tipo de dato original de la tabla DATE a VARCHAR2 con una función TO_CHAR para comparar con la cadena de caracteres '23/11/2007' y el índice almacena los valores en el tipo DATE.
Modificamos la sentencia para no utilizar una función sobre la columna FECHA y asi poder evaluar el criterio de seleccion sobre los valores contenidos en el índice de la PRIMARY KEY. Para esto filtramos las filas con valores entre el 23 de noviembre de 2007 a la hora 00:00:00 y el 23 de noviembre a la hora 23:59:59 ambos límites definidos en tipos de dato DATE y comparados con una columna de tipo de dato DATE. Mostramos el plan de ejecución de la sentencia modificada y podemos ver que la forma de acceso ahora es INDEX RANGE SCAN al índice ESTADO_APLICACION_PK.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> RUN
 1 SELECT COUNT(*)
 2   FROM ESTADO_APLICACION
 3  WHERE FECHA BETWEEN TO_DATE('23/11/2007 00:00:00','DD/MM/YYYY HH24:MI:SS')
 4*                 AND TO_DATE('23/11/2007 23:59:59','DD/MM/YYYY HH24:MI:SS')

Plan de EjecuciOn
----------------------------------------------------------
Plan hash value: 4117644086

-----------------------------------------------------------------------------
|Id|Operation          | Name               |Rows|Bytes|Cost (%CPU)|Time    |
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT  |                    |   1|    8|    8   (0)|00:00:01|
| 1|  SORT AGGREGATE   |                    |   1|    8|           |        |
|*2|   INDEX RANGE SCAN|ESTADO_APLICACION_PK|1441|11528|    8   (0)|00:00:01|
-----------------------------------------------------------------------------

Variables de ambiente, binds y sqlplus

Queremos realizar múltiples ejecuciones de un Unix shell script que a su vez ejecuta una sentencia SQL. Deseamos facilitar la reutilización de recursos de la base de datos asociados a la sentencia SQL y lograr una performance de ejecución razonable.

Los valores variables de la cláusula WHERE que utilizamos para filtrar las filas seleccionadas los representamos con bind variables o variables de enlace. Es importante no utilizar valores fijos en lugar de variables. De esta manera facilitamos al motor de base de datos el reconocimiento de la múltiple ejecución de la misma sentencia SQL con distintas condiciones y podrá reutilizar recursos asignados en la primera ejecución de la sentencia y saltear algunos pasos en las ejecuciones siguientes.

Listamos el fichero que contiene el shell script (comando ls) y mostramos el contenido (comando cat).
Utilizamos dos variables de shell (shell_usuario y shell_numero) y asignamos su contenido a dos variables de sqlplus (sql_usuario y sql_numero) mediante el comando EXECUTE.
Luego ejecutamos la sentencia SQL utilizando las dos variables de sqlplus precedidas por dos puntos, es decir, bind variables.

monitor@DESA] $ ls -l consulta5.sh
-rwxr-xr-x   1 monitor  dba          356 Nov  2 18:09 consulta5.sh
 
[monitor@DESA] $ cat consulta5.sh
#!/bin/ksh
 
shell_usuario=SYS
shell_numero=0
 
sqlplus -s monitor/monitor <<FIN_SQL
 
VARIABLE sql_usuario  VARCHAR2(30)
VARIABLE sql_numero   NUMBER
 
EXECUTE :sql_usuario := '${shell_usuario}'
EXECUTE :sql_numero  := ${shell_numero}
 
SELECT USERNAME, USER_ID, CREATED
FROM   ALL_USERS
WHERE  USERNAME = :sql_usuario
AND    USER_ID  = :sql_numero
/
FIN_SQL

Ejecutamos el shell script, que ejecuta el sqlplus que realiza las dos asignaciones de variables y ejecuta la sentencia SQL. Muestra el resultado de la sentencia SQL en pantalla.

monitor@DESA] $ consulta5.sh
 
PL/SQL procedure successfully completed.
 
 
PL/SQL procedure successfully completed.
 
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ----------
SYS                                     0 12/05/2002

Podemos leer este tema en la documentación estandar de Oracle:

Manual: SQL*Plus User's Guide and Reference
Capítulo: 5 Using Scripts in SQL*Plus
Título: Using Bind Variables

PL/SQL Tutorial

Esta entrada está dedicada a organizar los temas publicados asociados al tutorial de PL/SQL:

Primer procedimiento PL/SQL
Cursores PL/SQL notación SELECT .. INTO
Cursores PL/SQL notación SELECT .. INTO .. EXCEPTION
Cursores PL/SQL notación FOR .. LOOP
Cursores PL/SQL notación CURSOR .. FOR .. LOOP
Cursores PL/SQL notación OPEN .. FETCH .. CLOSE
Cursores PL/SQL notación FETCH .. BULK COLLECT INTO ..
Cursores PL/SQL notación FETCH .. BULK COLLECT INTO .. LIMIT

Unix shell script para Oracle (IV)

Necesitamos determinar en el shell script si la sentencia SQL ejecutada tuvo errores. Debemos evaluar el valor de retorno del sqlplus luego de ejecutar la sentencia para saber se generaron errores.
Cada comando que ejecutamos devuelve al terminar un valor de retorno que es recibido por el Korn shell. Por ejemplo al ejecutar el comando ls para saber si la carpeta actual contiene el fichero de nombre fichero_inexistente.txt.

[monitor@DESA] $ ls fichero_inexistente.txt
fichero_inexistente.txt: No such file or directory

La variable de ambiente $? nos muestra el valor retornado por el último comando ejecutado. Si el valor o estado de retorno es 0 (cero) la ejecución fue exitosa y un valor distinto de cero representa un error de ejecución. En este caso el comando ls no encontró ficheros con el nombre fichero_inexistente.txt por lo tanto retornó un valor distinto de 0 (cero).

[monitor@DESA] $ echo $?
2

La carpeta actual contiene un fichero de nombre fichero_xx.txt por lo tanto la ejecución del comando ls es exitosa y el estado de retorno del último comando ejecutado reflejado en la variable de ambiente $? es 0 (cero).

[monitor@DESA] $ ls fichero_xx.txt
fichero_xx.txt

Verificamos el valor de retorno del comando ls.

[monitor@DESA] $ echo $?
0

Para consultar la base de datos utilizaremos dos scripts denominados consulta3.sh y consulta4.sh.

[monitor@DESA] $ ls -l consulta3.sh consulta4.sh
-rwxrwxrwx   1 monitor  dba          178 Oct 25 15:30 consulta3.sh
-rwxrwxrwx   1 monitor  dba          168 Oct 25 15:30 consulta4.sh

Agregamos la línea WHENEVER SQLERROR EXIT SQL.SQLCODE para indicar al sqlplus que en caso de producirse un error de SQL como consultar una tabla inexistente debe terminar la ejecución y retornar el código de error asociado. De esta forma el Korn shell recibe el código de error del sqlplus y puede ser consultado en la variable de ambiente $?. Luego en el shell script podemos programar acciones correctivas en caso de detectarse un error en la ejecución de la sentencia SQL.

[monitor@DESA] $ cat consulta3.sh
#!/bin/ksh
 
sqlplus -s monitor/colopass07 <<FIN
WHENEVER SQLERROR EXIT SQL.SQLCODE
SELECT 1 AS COLUMNA FROM DUAL_NO_EXISTE
/
FIN
 
echo  'consulta3.sh EL VALOR DE RETORNO $? ES ' $?

Ejecutamos el shell script consulta3.sh y vemos el código de error que el envía el sqlplus:

[monitor@DESA] $ consulta3.sh
SELECT 1 AS COLUMNA FROM DUAL_NO_EXISTE
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

consulta3.sh EL VALOR DE RETORNO $? ES  174

Si la ejecución del sqlplus no produce errores el código de retorno es cero o ejecución exitosa. Así lo podemos ver en el ejemplo de ejecución de consulta4.sh donde la variable de ambiente $? toma el valor 0 (cero) luego de la ejecución del sqlplus.

[monitor@DESA] $ cat consulta4.sh
#!/bin/ksh
 
sqlplus -s monitor/colopass07<<FIN
WHENEVER SQLERROR EXIT SQL.SQLCODE
SELECT 1 AS COLUMNA FROM DUAL
/
FIN

echo  'consulta4.sh EL VALOR DE RETORNO $? ES ' $?

Ejecutamos el shell script consulta4.sh y vemos el código de error que envía el sqlplus:

[monitor@DESA] $ consulta4.sh

   COLUMNA
----------
         1

consulta4.sh EL VALOR DE RETORNO $? ES  0

Podemos leer este tema en la documentación estandar de Oracle:

Manual: SQL*Plus User's Guide and Reference
Capítulo: 12 SQL*Plus Command Reference
Párrafo: WHENEVER SQLERROR
Párrafo: EXIT

Unix shell script para Oracle SQL (III)

Podemos ejecutar una sentencia SQL embebida en el shell script, es decir, script y SQL todo en el mismo fichero.
Listemos el archivo de prueba llamado consulta2.sh.

[monitor@DESA]ls -l consulta2.sh
-rwxrw-r--   1 monitor  dba           69 Aug 14 12:20 consulta2.sh

Veamos el contenido del fichero que contiene el shell script. Observamos que llamada al ejecutable sqlplus monitor/colopass07 es seguida por la cadena <<FIN, la sentencia SQL y la cadena FIN. El texto encerrado entre estas dos cadenas será recibido por el fichero ejecutable por la entrada estandar (stdin).
Los primeros dos caracteres del shell script (#!) NO son un comentario, se utilizan para indicar que el resto de la línea detalla el interprete de comandos que se debe utilizar para ejecutar el script en este caso el Korn shell o /bin/ksh.

[monitor@DESA]cat consulta2.sh
#!/bin/ksh
#
sqlplus monitor/colopass07 <<FIN
SELECT 1
  FROM DUAL
/
FIN

El sqlplus recibe por la entrada estándar la sentencia SQL, la ejecuta y termina la ejecución retornando el control al ksh.

[monitor@DESA]consulta2.sh
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Aug 14 12:21:44 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: 
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
 
SQL>  
         1
----------
         1
 
SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

[monitor@DESA]

Unix shell script para Oracle SQL (II)

Queremos crear un script para el shell de Unix (podría ser el Korn shell, ksh) ejecute una sentencia SQL y nos muestre los datos en la pantalla.
Simplemente escribimos la instrucción aparecida en la entrada Oracle Database Tutorial: Unix shell script para Oracle SQL (I) en una fichero con extensión ".sh".
Para ello utilizaremos los ficheros llamados sentencia.sql y consulta1.sh.

[monitor@DESA]ls -l sentencia.sql consulta1.sh consulta2.sh
-rwxr--r--   1 monitor  dba           51 Aug 14 12:20 consulta1.sh
-rwxrw-r--   1 monitor  dba           69 Aug 14 12:20 consulta2.sh
-rw-r--r--   1 monitor  dba           29 Aug 14 12:19 sentencia.sql

El fichero sentencia.sql solo contiene la sentencia SQL a ejecutar en la base de datos.

[monitor@DESA]cat sentencia.sql
SELECT 1 
  FROM DUAL
/
EXIT

El fichero consulta1.sh sólo contiene la llamada a ejecución del SQL*Plus con el usuario monitor, la password colopass07, arroba (@) y el nombre del fichero que contiene la sentencia SQL a ejecutar sentencia.sql (los primeros dos caracteres del shell script (#!) se utilizan para indicar que el resto de la línea detalla el interprete de comandos que se debe utilizar para ejecutar el script en este caso el Korn shell o /bin/ksh).

[monitor@DESA]cat consulta1.sh
#!/bin/ksh

sqlplus monitor/colopass07 @sentencia.sql

Ejecutamos el shell script: se conecta, ejecuta el select, muestra una fila con el valor uno, nombre de la columna uno y se desconecta.

[monitor@DESA]consulta1.sh
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Aug 14 12:21:32 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

         1
----------
         1

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Unix shell script para Oracle SQL (I)

Queremos ejecutar una consulta con una sentencia SELECT almacenada en un fichero con extensión .sql en el file system. ¿Cómo ejecutamos la consulta?
Primero, listamos los ficheros a utilizar en el ejemplo y mostramos en la pantalla el contenidos de sentencia.sql.

[monitor@DESA]ls sentencia.sql
sentencia.sql

Podemos ver que el fichero sentencia1.sql sólo contiene una sentencia SELECT 1 FROM DUAL la barra (/) para ejecutarla y el comando EXIT para indicar la salida del SQL*Plus.

[monitor@DESA]cat sentencia.sql
SELECT 1 
  FROM DUAL
/
EXIT

Para ejecutar este SELECT debemos invocar al SQL*Plus pasándole por parámetro el usuario (monitor) y la password (colopass) para conectarnos y arroba (@) el nombre del fichero (sentencia.sql) que contiene la sentencia SQL. Ejecutamos la línea donde se conecta, ejecuta el select, muestra una fila con el valor uno y se desconecta.

[monitor@DESA]sqlplus monitor/colopass07 @sentencia.sql

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Sep 28 14:16:33 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

         1
----------
         1

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Cursores PL/SQL notación FETCH .. BULK COLLECT INTO .. LIMIT

Esta notación nos permite recuperar las filas asociadas a la sentencia SELECT que representa el cursor con la ejecución de varias sentencias FETCH en grupos de filas de un número determinado por la cláusula LIMIT. Los grupos de filas recuperados se asignan a un array o vector.

Resumiremos linea a línea el código fuente del procedimiento del ejemplo llamado FETCH_BULK_COLLECT_INTO_LIMIT

Línea 2: Inicio del procedimiento.
Líneas 4 a 7: Declaramos el cursor de nombre CursorVistas y lo asociamos a la sentencia SELECT que recupera las filas.
Línea 9: Declaramos un tipo de dato de array o vector que almacena filas de la tabla ALL_VIEWS y lo llamamos TipoArrayRegistros. Es decir, cada elemento del array permite almacenar todos los valores de una fila de la tabla ALL_VIEWS. Cada elemento del array contiene tantos campos como columnas tiene la tabla, con los mismos nombres y tipos de dato.
Línea 10: Declaramos la variable ArrayRegistros del tipo de datos TipoArrayRegistros.
Línea 12: Declaramos la variable indice de tipo de dato entero. La utilizaremos para hacer referencia a los valores que contiene el array o vector.
Línea 14: Abrimos el cursor. Este paso selecciona las filas de la tabla que evalúan verdadera la condición que la sentencia SELECT declara en su cláusula WHERE. Si cambian los valores de las filas antes de ser recuperadas, estos cambios no se verán reflejados. Tomamos la foto de los datos a recuperar en este punto en el tiempo. No se consideran modificaciones posteriores.
Línea 15: Iniciamos el ciclo o bucle repetitivo en el que recuperamos los grupos de filas (la línea 26 fuerza la salida del bucle o ciclo repetitivo si no hay mas filas para recuperar del cursor).
Línea 16: Recuperamos las filas que cumplen la condición de la sentencia SELECT con la instrucción FETCH. El BULK COLLECT INTO permite asignar el grupo de filas recuperadas a un array o vector. Cada elemento del array contiene una fila recuperada. Los elementos están formados por una estructura de campos con nombres y tipos de datos equivalentes a las columnas seleccionadas. La cantidad máxima del grupo de filas recuperadas por la ejecución de cada FETCH está determinada por la cláusula LIMIT. En este ejemplo recuperamos las filas en grupos de 5 filas.
Línea 17 a 25: Recorremos el array o vector ArrayRegistros que contiene las filas recuperadas por la sentencia FETCH y mostramos el contenido en pantalla.
Línea 17: Asignamos a la variable indice la posición del primer elemento almacenado en el array o vector. Si no contiene elementos se asigna el valor NULL.
Línea 18 a 25: Mientras la variable indice sea distinta de NULL iteramos dentro del ciclo definido por el LOOP .. END LOOP. Por cada ciclo mostramos en pantalla con la instrucción DBMS_OUTPUT.PUT_LINE el contenido de los campos OWNER y VIEW_NAME en la posición indice del array.
Luego asignamos a la variable indice la siguiente posición del array que contiene una fila. Si no hay mas filas en el array se asigna NULL. Al evaluarse la condición de WHILE (indice IS NOT NULL) se corta la iteración del ciclo que recorre el vector o array.
Línea 28: Cerramos el cursor. Liberamos los recursos en memoria asociados a la sentencia SELECT.
Línea 28: Fin del procedimiento FETCH_BULK_COLLECT_INTO_LIMIT.

SQL> CONNECT FERNANDEZ/FERNANDEZ
Conectado.

SQL> create or replace
  2  PROCEDURE FETCH_BULK_COLLECT_INTO_LIMIT
  3  IS
  4     CURSOR CursorVistas IS SELECT *
  5                              FROM ALL_VIEWS
  6                             WHERE OWNER = 'SYS'
  7                               AND VIEW_NAME LIKE 'ALL_TAB%';
  8
  9     TYPE TipoArrayRegistros IS TABLE OF CursorVistas%ROWTYPE;
 10     ArrayRegistros TipoArrayRegistros;
 11
 12     indice  PLS_INTEGER;
 13  BEGIN
 14      OPEN CursorVistas;
 15      LOOP
 16          FETCH CursorVistas BULK COLLECT INTO ArrayRegistros LIMIT 5;
 17          indice := ArrayRegistros.FIRST;
 18          WHILE (indice IS NOT NULL)
 19          LOOP
 20              DBMS_OUTPUT.PUT_LINE('ESQUEMA: '                  ||
 21                                   ArrayRegistros(indice).OWNER ||
 22                                   ' VISTA  : '                 ||
 23                                   ArrayRegistros(indice).VIEW_NAME);
 24              indice := ArrayRegistros.NEXT(indice);
 25          END LOOP;
 26          EXIT WHEN CursorVistas%NOTFOUND;
 27      END LOOP;
 28      CLOSE CursorVistas;
 29
 30  END FETCH_BULK_COLLECT_INTO_LIMIT;
 31  /
 
Procedimiento creado.
 
SQL> DESCRIBE FETCH_BULK_COLLECT_INTO_LIMIT;
PROCEDURE FETCH_BULK_COLLECT_INTO_LIMIT
 
SQL> SET SERVEROUTPUT ON
 
SQL> EXEC FETCH_BULK_COLLECT_INTO_LIMIT;
ESQUEMA: SYS VISTA  : ALL_TABLES
ESQUEMA: SYS VISTA  : ALL_TAB_COLS
ESQUEMA: SYS VISTA  : ALL_TAB_COLUMNS
ESQUEMA: SYS VISTA  : ALL_TAB_COL_STATISTICS
ESQUEMA: SYS VISTA  : ALL_TAB_COMMENTS
ESQUEMA: SYS VISTA  : ALL_TAB_HISTOGRAMS
ESQUEMA: SYS VISTA  : ALL_TAB_MODIFICATIONS
ESQUEMA: SYS VISTA  : ALL_TAB_PARTITIONS
ESQUEMA: SYS VISTA  : ALL_TAB_PRIVS
ESQUEMA: SYS VISTA  : ALL_TAB_PRIVS_MADE
ESQUEMA: SYS VISTA  : ALL_TAB_PRIVS_RECD
ESQUEMA: SYS VISTA  : ALL_TAB_STATISTICS
ESQUEMA: SYS VISTA  : ALL_TAB_STATS_HISTORY
ESQUEMA: SYS VISTA  : ALL_TAB_SUBPARTITIONS
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 11 Tuning PL/SQL Applications for Performance
Título: Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
Subtítulo: Retrieving Query Results into Collections with the BULK COLLECT clause
Párrafo: Limiting the Rows for a Bulk FETCH Operation with the LIMIT clause

Tiempo de respuesta lento de aplicaciones Oracle (III)

Podemos mejorar el tiempo de respuesta lento de una aplicación de gestión comercial sin mejorar la calidad del enlace ni mejorar la calidad del código fuente de la aplicación.
La opción es utilizar una implementacion de Terminal Server. Asumimos que la aplicación de gestión comercial es ejecutada en puntos de venta alejados de la base de datos. El Terminal Server debe ser instalado con un enlace de calidad y baja latencia donde la aplicación se ejecute con tiempos de respuesta satisfactorios para el usuario. Por ejemplo podría residir en el mismo centro de cómputos que la base de datos. Estamos moviendo la ejecución de la aplicación de gestión. Reducimos la distancia entre la aplicación y la base.
Los usuarios de la aplicación ubicados en los puntos de venta lejanos ejecutan un Terminal Server Client que se conecta con el Terminal Server. A través de ella los usuarios pueden ejecutar la aplicación de gestión comercial remotamente en el Terminal Server.
El enlace entre los puntos de venta y el Terminal Server de menor calidad, económico y gran longitud transmite diálogos que poco sencibles a la baja latencia. El cliente envía tipeos de teclas, movimientos y clicks del ratón. El servidor responde con las actualizaciónes gráficas de las pantallas de la aplicación visualizadas en el monitor. La aplicación de gestión comercial se ejecuta en el Terminal Server sin ser mejoradas sus ineficientes comunicaciones con la base de datos.
Esta opción es apliamente recomendada para aplicaciones de gestión comercial ya desarrolladas, que no escalan a nuevos puntos de venta alejados geográficamente y que utilizan enlaces baratos. Modificar íntegramente la aplicación puede no ser una opción económica viable por la gran cantidad de línes de código fuente involucradas. No se recomienda como solución informática para aplicaciones de gestión comercial nuevas. En este último caso la opción correcta es programar la aplicación utilizando buenas prácticas y haciendo un uso eficiente del vínculo con la base de datos.

Tiempo de respuesta lento de aplicaciones Oracle (II)

Reduciendo el tiempo de respuesta con PL/SQL

Si nuestra aplicación programada en Java o Visual Basic contiene sentencias SQL embebidas en el código, la base de datos debe recibir y procesar una por una cada sentencia SQL. Las aplicaciones que utilizan muchas sentencias SQL aisladas requieren múltiples llamadas a la base de datos, provocando overhead de red y performance.

Ver la documentación oficial:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 1 Overview of PL/SQL
Sección: Advantages of PL/SQL
Párrafo: Better Performance

Utilizando PL/SQL podemos solicitar y ejecutar un bloque entero de sentencias SQL de una sola vez. Reduciremos en gran medida el tráfico de red entre la aplicación y la base de datos. Si el enlace es lento o está muy congestionado reduciremos el tiempo de respuesta de la aplicación.
Los procedimientos PL/SQL almacenados en la base de datos o (PL/SQL stored procedures) se compilan una sóla vez y se almacenan en formato ejecutable ganando eficiencia.
Una única llamada a través de la red puede iniciar la ejecución de una gran tarea conformada por transacciones y sentencias SQL porque el procedimiento está almacenado en la base de datos. De esta forma reducimos el trafico de la red y mejoramos el tiempo de respuesta. Los stored procedures son reutilizados y compartidos entre usuarios por lo que reducimos los requerimientos de memoria y overhead de ejecución.

¿Si ningún módulo de la aplicación utiliza stored procedures? Modificar miles de líneas de código fuente de performance ineficiente y mediocre puede ser muy costoso.
¿Cómo mejorar el tiempo de respuesta de la aplicación sin modificar la calidad del enlace ni modificar la aplicación?

Tiempo de respuesta lento de aplicaciones Oracle (I)

Introducción al problema

Las aplicaciones que utilizan el protocolo SQLNet pueden ser muy sensibles a la calidad del enlace que las conecta a la base de datos. Las aplicaciones desarrolladas en Java o Visual Basic con sentencias SQL embebidas en el código utilizan una cantidad importante de mensajes para comunicarse con la base de datos y ejecutar las sentencias.
Considerando la baja latencia de una red LAN (reducida demora en recorrer dos puntos de un enlace), ésta generalmente no impacta negativamente en el tiempo de respuesta de la aplicación. No ocurre lo mismo cuando la aplicación es ejecutada en un punto de venta alejado geograficamente del centro de cómputos que aloja la base de datos. Estos puntos de venta lejanos en ocaciones utilizan enlaces de baja calidad y bajo costo. Hemos registrado latencias de red muy altas de hasta 150 milisegundos en el horario pico. Estas condiciones del enlace provocan gran lentitud del tiempo de respuesta de consultas y transacciones aunque el ancho de banda del enlace se encuentre parcialmente libre.

Podemos encontrar en la documentación oficial la siguiente recomendación:

"La latencia de red punto a punto debería estar entre 1 y 25 miliseg."

Manual: Oracle9 i Database Performance Planning
Capítulo: 2 Monitoring and Improving Application Performance
Sección: The Oracle Performance Improvement Method
Párrafo: Performance Characteristics of Hardware Configurations

La documentación oficial de 10g no vuelve a mencionar valores absolutos como 25 miliseg. sino que recomienda distintas técnicas de evaluación con distintas cargas de trabajo y la participación del usuario final para validar un tiempo de respuesta satisfactorio.

¿Cómo mejorar el tiempo de respuesta entre la aplicación cliente y el servidor de base de datos sin utilizar un enlace de mayor calidad y por ende mas costoso?

Cursores PL/SQL notación SELECT..INTO..EXCEPTION

Creamos dos procedimientos que ejecutan la misma sentencia SQL, recuperan las mismas filas y las muestran en pantalla. El primero NO gestiona las excepciones y muestra errores en pantalla. El segundo gestiona las excepciones y no deja que se vean errores en pantalla. Comparamos los funcionamientos.
El procedimiento sin gestión de excepciones se llama SELECT_INTO_SIN_EXCEPTION. Selecciona una fila y muestra el valor que contiene una columna de la fila seleccionada. Utilizamos para selecionar la fila el Parametro_Nombre que nos permite ejecutar la lógica o programa trabajando con distintas filas en cada ejecución.

SQL> CONNECT FERNANDEZ/FERNANDEZ
Conectado.
 
SQL> SET SERVEROUTPUT ON
 
SQL> create or replace
  2  PROCEDURE SELECT_INTO_SIN_EXCEPTION(Parametro_Nombre IN  ALL_VIEWS.VIEW_NAME%TYPE)
  3  AS
  4    vTEXT_LENGTH  ALL_VIEWS.TEXT_LENGTH%TYPE;
  5  BEGIN
  6    SELECT TEXT_LENGTH
  7      INTO vTEXT_LENGTH
  8      FROM SYS.ALL_VIEWS
  9     WHERE OWNER     = 'SYS'
 10       AND VIEW_NAME LIKE Parametro_Nombre;
 11
 12    DBMS_OUTPUT.PUT_LINE('La longitud del texto de la vista es:');
 13    DBMS_OUTPUT.PUT_LINE(vTEXT_LENGTH);
 14  END SELECT_INTO_SIN_EXCEPTION;
 15  /
 
Procedimiento creado.

Verificamos la existencia del procedimiento con el comando DESCRIBE. Vemos que no solo se muestra el nombre del procedimiento SELECT_INTO_SIN_EXCEPTION sino que también se detalla el nombre del parámetro PARAMETRO_NOMBRE que es sólo de entrada de datos (E/S=IN).

SQL> DESC SELECT_INTO_SIN_EXCEPTION;
PROCEDURE SELECT_INTO_SIN_EXCEPTION
 Nombre de Argumento            Tipo                    E/S   ┐Por Defecto?
 ------------------------------ ----------------------- ------ --------
 PARAMETRO_NOMBRE               VARCHAR2(30)            IN
 

Ejecutamos el procedimiento pasando por parámetro el valor 'USER_TABLES'. Sólo una fila de ALL_VIEWS evalúa verdadera la condición de la cláusula WHERE VIEW_NAME LIKE 'USER_TABLES'. Se recupera la fila y se muestra en pantalla el valor de la columna TEXT_LENGTH de dicha fila.

SQL> EXECUTE SELECT_INTO_SIN_EXCEPTION('USER_TABLES');
La longitud del texto de la vista es:
3971
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Ejecutamos el procedimiento pasando por parámetro el valor 'NOMBRE_INEXISTENTE'. No existe ninguna fila con dicho nombre en ALL_VIEWS por lo tanto se levanta la excepción "ORA-01403: No se ha encontrado ningún dato" ("ORA-01403: no data found"). El procedimiento no posee instrucciones para atrapar, capturar ni gestionar esta excepción por lo tanto se propaga haca el nivel superior. El SQL*Plus es el proceso ejecutor del procedimiento, recibe la excepción levantada por el procedimiento y muestra el mensaje asociado a la excepción en la pantalla.

SQL> EXECUTE SELECT_INTO_SIN_EXCEPTION('NOMBRE_INEXISTENTE');
BEGIN SELECT_INTO_SIN_EXCEPTION('NOMBRE_INEXISTENTE'); END;
 
*
ERROR en línea 1:
ORA-01403: No se ha encontrado ningún dato
ORA-06512: en "FERNANDEZ.SELECT_INTO_SIN_EXCEPTION", línea 5
ORA-06512: en línea 1

Ejecutamos el procedimiento pasando por parámetro el valor comodín '%'. Todas las filas comparadas con el comodín '%' evalúan la condición con el valor verdadero por lo tanto se recuperan todas las filas de ALL_VIEWS. El procedimiento ejecuta la sentencia con la notación SELECT..INTO.. contemplanto sólo la recuperación de una única fila. Como no tiene soporte para la recuperación de múltiples filas, se levanta la excepción "ORA-01403: No se ha encontrado ningún dato" ("ORA-01403: no data found"). El procedimiento no posee instrucciones para atrapar, capturar ni gestionar esta excepción por lo tanto se propaga haca el nivel superior. El SQL*Plus es el proceso ejecutor del procedimiento, recibe la excepción levantada por el procedimiento y muestra el mensaje asociado a la excepción en la pantalla.

SQL> EXECUTE SELECT_INTO_SIN_EXCEPTION('%');
BEGIN SELECT_INTO_SIN_EXCEPTION('%'); END;
 
*
ERROR en línea 1:
ORA-01422: la recuperación exacta devuelve un número mayor de filas que el solicitado
ORA-06512: en "FERNANDEZ.SELECT_INTO_SIN_EXCEPTION", línea 5
ORA-06512: en línea 1

Creamos el procedimiento SELECT_INTO_CON_EXCEPTION similar al procedimiento SELECT_INTO_SIN_EXCEPTION con el agregado del manejo de excepciones de error:
Línea 15: Inicio del bloque de gestión de excepciones.
Líneas 16 a 17: Definimos qué debe hacer el procedimiento sin entre el BEGIN y el EXCEPTION se levanta un error de excepción del tipo NO_DATA_FOUND, es decir, "ORA-01403: No se ha encontrado ningún dato". Se muestra el texto DATOS BUSCADOS NO ENCONTRADOS! y no se propaga el error al nivel superior.
Líneas 18 a 19: Si se leventa la excepción TOO_MANY_ROWS, es decir, "ORA-01422: la recuperación exacta devuelve un número mayor de filas que el solicitado" ("ORA-01422: exact fetch returns more than requested number of rows") se muestra en pantalla el mensaje SE ENCONTRO MAS DE UNA FILA! y no se propaga el error al nivel superior.

SQL> create or replace
  2  PROCEDURE SELECT_INTO_CON_EXCEPTION(Parametro_Nombre IN  ALL_VIEWS.VIEW_NAM
E%TYPE)
  3  AS
  4    vTEXT_LENGTH  ALL_VIEWS.TEXT_LENGTH%TYPE;
  5  BEGIN
  6    SELECT TEXT_LENGTH
  7      INTO vTEXT_LENGTH
  8      FROM SYS.ALL_VIEWS
  9     WHERE OWNER     = 'SYS'
 10       AND VIEW_NAME LIKE Parametro_Nombre;
 11
 12    DBMS_OUTPUT.PUT_LINE('La longitud del texto de la vista es:');
 13    DBMS_OUTPUT.PUT_LINE(vTEXT_LENGTH);
 14
 15  EXCEPTION
 16    WHEN NO_DATA_FOUND THEN
 17         DBMS_OUTPUT.PUT_LINE('DATOS BUSCADOS NO ENCONTRADOS!');
 18    WHEN TOO_MANY_ROWS THEN
 19         DBMS_OUTPUT.PUT_LINE('SE ENCONTRO MAS DE UNA FILA!');
 20  END SELECT_INTO_CON_EXCEPTION;
 21  /
 
Procedimiento creado.
 
SQL> DESCRIBE SELECT_INTO_CON_EXCEPTION
PROCEDURE SELECT_INTO_CON_EXCEPTION
 Nombre de Argumento            Tipo                    E/S   ┐Por Defecto?
 ------------------------------ ----------------------- ------ --------
 PARAMETRO_NOMBRE               VARCHAR2(30)            IN

Ejecutamos el procedimiento con los distintos valores de parámetros ejecutados anteriormente y vemos que no se muestran errores en pantalla. El manejo de excepciones las detecta y toma acciones específicas para cada una de ellas.

SQL> EXECUTE SELECT_INTO_CON_EXCEPTION('NOMBRE_INEXISTENTE');
DATOS BUSCADOS NO ENCONTRADOS!
 
Procedimiento PL/SQL terminado correctamente.
 
SQL> EXECUTE SELECT_INTO_CON_EXCEPTION('%');
SE ENCONTRO MAS DE UNA FILA!
 
Procedimiento PL/SQL terminado correctamente.
 
SQL> EXECUTE SELECT_INTO_CON_EXCEPTION('USER_TABLES');
La longitud del texto de la vista es:
3971
 
Procedimiento PL/SQL terminado correctamente.

Cursores PL/SQL notación SELECT..INTO

Creamos un procedimiento almacenado PL/SQL o PL/SQL stored procedure de nombre SELECT_INTO. El procedimiento recupera una columna de una fila dada, lo muestra en pantalla con un mensaje y termina.

Líneas 1 a 2: Crean un procedimiento nuevo o lo reemplazan si ya existe uno anteriormente almacenado con el nombre SELECT_INTO.
Línea 3: Declara la variable vTEXT_LENGTH del mismo tipo de datos que la columna TEXT_LENGTH de la vista ALL_VIEWS.
Líneas 5 a 9: Declaran la sentencia SELECT que recupera la longitud del texto de la vista USER_TABLES y lo asigna a la variable vTEXT_LENGTH.
Líneas 11 y 12: Muestran en pantalla un mensaje y el valor recuperado por la sentencia SELECT.

SQL> CREATE OR REPLACE
  2  PROCEDURE SELECT_INTO AS
  3    vTEXT_LENGTH  ALL_VIEWS.TEXT_LENGTH%TYPE;
  4  BEGIN
  5    SELECT TEXT_LENGTH
  6      INTO vTEXT_LENGTH
  7      FROM ALL_VIEWS
  8     WHERE OWNER     = 'SYS'
  9       AND VIEW_NAME = 'USER_TABLES';
 10
 11    DBMS_OUTPUT.PUT_LINE('La longitud del texto de la vista es:');
 12    DBMS_OUTPUT.PUT_LINE(vTEXT_LENGTH);
 13
 14  END SELECT_INTO;
 15  /
 
Procedimiento creado.
 
SQL> DESCRIBE SELECT_INTO
PROCEDURE SELECT_INTO

Normalmente se encuentra deshabilitada la salida de mensajes por pantalla. Asignando el valor ON a la variable de ambiente SERVEROUTPUT, SQL*Plus muestra los mensajes de salida de los procedimientos almacenados.

SQL> SET SERVEROUTPUT ON

Ejecutamos el procedimiento con el comando EXECUTE.

SQL> EXECUTE SELECT_INTO
La longitud del texto de la vista es:
3971
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 6 Performing SQL Operations from PL/SQL
Título: Querying Data with PL/SQL
Subtítulo:Selecting At Most One Row: SELECT INTO Statement

Entradas relacionadas:
PL/SQL Tutorial
Cursores PL/SQL notación SELECT..INTO
Cursores PL/SQL notación FOR...LOOP
Cursores PL/SQL notación CURSOR...FOR...LOOP
Cursores PL/SQL notación OPEN...FETCH...CLOSE
Cursores PL/SQL notación FETCH...BULK COLLECT INTO...

Primer procedimiento PL/SQL

Creamos un procedimiento almacenado PL/SQL o PL/SQL stored procedure de nombre PRIMERO. El procedimiento sólo muestra el mensaje Hola mundo! y termina.

Líneas 1 a 2: Crean un procedimiento nuevo o lo reemplazan si ya existe uno anteriormente almacenado con el nombre PRIMERO.
Línea 3: La palabra reservada BEGIN determina el inicio del cuerpo del procedimiento que contiene la lógica o algoritmo a ejecutar.
Línea 4: Una única línea de codigo con la instrucción DBMS_OUTPUT.PUT_LINE define el cuerpo del procedimiento con la sola intención de mostrar en pantalla el mensaje Hola mundo!.
Línea 5: Fin del procedimiento.
Línea 6: Ejecutar sentencia de creación del procedimiento.

SQL> CONNECT FERNANDEZ/FERNANDEZ
Conectado.
 
SQL> CREATE OR REPLACE
  2  PROCEDURE PRIMERO AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('Hola mundo!');
  5  END PRIMERO;
  6  /
 
Procedimiento creado.

Normalmente se encuentra deshabilitada la salida de mensajes por pantalla. Asignando el valor ON a la variable de ambiente SERVEROUTPUT, SQL*Plus muestra los mensajes de salida de los procedimientos almacenados.

SQL> SET SERVEROUTPUT ON

Ejecutamos el procedimiento con el comando EXECUTE.

SQL> EXECUTE PRIMERO
Hola mundo!
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 8 Using PL/SQL Subprograms
Título:Understanding PL/SQL Procedures

Entradas relacionadas:
PL/SQL Tutorial

Cursores PL/SQL notación FETCH...BULK COLLECT INTO...

Esta notación nos permite recuperar todas las filas asociadas a la sentencia SELECT que representa el cursor con la ejecución de una única instrucción FETCH.
Resumiremos linea a línea el código fuente del procedimiento del ejemplo:

Línea 2: Inicio del procedimiento.
Línea 4: Declaramos un tipo de dato de array o vector que almacena filas de la tabla ALL_VIEWS y lo llamamos TipoArrayRegistros. Es decir, cada elemento del array permite almacenar todos los valores de una fila de la tabla ALL_VIEWS. Cada elemento del array contiene tantos campos como columnas tiene la tabla, con los mismos nombres y tipos de dato.
Línea 5: Declaramos la variable ArrayRegistros del tipo de datos TipoArrayRegistros.
Líneas 7 a 10: Declaramos el cursor de nombre CursorVistas y lo asociamos a la sentencia SELECT que recupera las filas.
Línea 11: Declaramos la variable indice de tipo de dato entero. La utilizaremos para hacer referencia a los valores que contiene el array o vector.
Línea 14: Abrimos el cursor. Este paso selecciona las filas de la tabla que evalúan verdadera la condición de la sentencia SELECT. Si cambian los valores de las filas antes de ser recuperadas, estos cambios no se verán reflejados. Tomamos la foto de los datos a recuperar en este punto en el tiempo. No se consideran modificaciones posteriores.
Línea 15: Recuperamos las filas que cumplen la condición de la sentencia SELECT con la instrucción FETCH. El BULK COLLECT INTO permite asignar todas las filas recuperadas a un array o vector. Cada elemento del array contiene una fila recuperada. Los elementos están formados por una estructura de campos con nombres y tipos de datos equivalentes a las columnas seleccionadas.
Línea 16: Cerramos el cursor. Liberamos los recursos en memoria asociados a la sentencia SELECT.
Línea 18: Asignamos a la variable indice la posición del primer elemento almacenado en el array o vector. Si no contiene elementos se asigna el valor NULL.
Línea 19 a 26: Mientras la variable indice sea distinta de NULL iteramos dentro del ciclo definido por el LOOP...END LOOP. Por cada ciclo mostramos en pantalla con la instrucción DBMS_OUTPUT.PUT_LINE el contenido de los campos OWNER y VIEW_NAME en la posición indice del array. Luego asignamos a la variable indice la siguiente posición del array que contiene una fila. Si no hay mas filas en el array se asigna NULL. Al evaluarse la condición de WHILE (indice IS NOT NULL) se corta la iteración de los ciclos.
Línea 28: Fin del procedimiento.

SQL> CONNECT FERNANDEZ/FERNANDEZ
Conectado.
 
SQL> create or replace
 2  PROCEDURE FETCH_BULK_COLLECT_INTO
 3  IS
 4     TYPE TipoArrayRegistros IS TABLE OF ALL_VIEWS%ROWTYPE;
 5     ArrayRegistros TipoArrayRegistros;
 6
 7     CURSOR CursorVistas IS SELECT *
 8                              FROM ALL_VIEWS
 9                             WHERE OWNER = 'SYS'
10                               AND VIEW_NAME LIKE 'ALL_TAB%';
11     indice  PLS_INTEGER;
12  BEGIN
13
14      OPEN  CursorVistas;
15      FETCH CursorVistas BULK COLLECT INTO ArrayRegistros;
16      CLOSE CursorVistas;
17
18      indice := ArrayRegistros.FIRST;
19      WHILE (indice IS NOT NULL)
20      LOOP
21          DBMS_OUTPUT.PUT_LINE('ESQUEMA: '                  ||
22                               ArrayRegistros(indice).OWNER ||
23                               ' VISTA  : '                 ||
24                               ArrayRegistros(indice).VIEW_NAME);
25          indice := ArrayRegistros.NEXT(indice);
26      END LOOP;
27
28  END FETCH_BULK_COLLECT_INTO;
29  /
 
Procedimiento creado.
 
SQL> SET SERVEROUTPUT ON
  
SQL> EXECUTE FETCH_BULK_COLLECT_INTO;
ESQUEMA: SYS VISTA  : ALL_TABLES
ESQUEMA: SYS VISTA  : ALL_TAB_COLS
ESQUEMA: SYS VISTA  : ALL_TAB_COLUMNS
ESQUEMA: SYS VISTA  : ALL_TAB_COL_STATISTICS
ESQUEMA: SYS VISTA  : ALL_TAB_COMMENTS
ESQUEMA: SYS VISTA  : ALL_TAB_HISTOGRAMS
ESQUEMA: SYS VISTA  : ALL_TAB_MODIFICATIONS
ESQUEMA: SYS VISTA  : ALL_TAB_PARTITIONS
ESQUEMA: SYS VISTA  : ALL_TAB_PRIVS
ESQUEMA: SYS VISTA  : ALL_TAB_PRIVS_MADE
ESQUEMA: SYS VISTA  : ALL_TAB_PRIVS_RECD
ESQUEMA: SYS VISTA  : ALL_TAB_STATISTICS
ESQUEMA: SYS VISTA  : ALL_TAB_STATS_HISTORY
ESQUEMA: SYS VISTA  : ALL_TAB_SUBPARTITIONS
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 6 Performing SQL Operations from PL/SQL
Título: Managing Cursors in PL/SQL
Subtítulo: Explicit Cursors
Párrafo: Fetching Bulk Data with a Cursor

Entradas relacionadas:
PL/SQL Tutorial
Cursores PL/SQL notación SELECT..INTO
Cursores PL/SQL notación FOR...LOOP
Cursores PL/SQL notación CURSOR...FOR...LOOP
Cursores PL/SQL notación OPEN...FETCH...CLOSE
Cursores PL/SQL notación FETCH...BULK COLLECT INTO...

Cursores PL/SQL notación OPEN...FETCH...CLOSE

Esta notación de cursores PL/SQL nos brinda flexibilidad agregando a cambio cierta complejidad. Declaramos el cursor y la sentencia SELECT asociada en la línea 4. Esta declaración es independiente de la ejecución de la sentencia y del recupero de sus filas. La variable Registro es declarada en forma explícita en la línea 8. Abrimos explícitamente el cursor en la línea 10 con la instrucción OPEN. Este es el momento donde se seleccionan la filas que luego seán recuperadas. Si se agregan filas a la tabla antes de ser recuperadas no serán mostradas. La foto de los datos a recuperar se toma en un punto en el tiempo y los cambios posteriores no son reflejados. Recuperamos las filas seleccionadas con la instrucción FETCH de la línea 12. Cada FETCH recupera una fila. Luego preguntamos en la línea 13 si no se encontraron filas para procesar, si es así salimos el ciclo de procesamiento de filas. Mostramos los valores de cada fila recuperada en la línea 16. Cerramos el cursor en forma explícita en la línea 21.

SQL> conn fernandez/fernandez
Conectado.
 
SQL> create or replace
 2  PROCEDURE CURSOR_OPEN_FETCH
 3  IS
 4       CURSOR CursorVistas
 5           IS (SELECT OWNER, VIEW_NAME
 6                 FROM ALL_VIEWS
 7                WHERE OWNER = 'SYS'
 8                  AND VIEW_NAME LIKE 'ALL_TAB%');
 9       Registro CursorVistas%ROWTYPE;
10  BEGIN
11       OPEN CursorVistas;
12       LOOP
13           FETCH CursorVistas INTO Registro;
14           IF CursorVistas%NOTFOUND THEN
15               EXIT;
16           END IF;
17           DBMS_OUTPUT.PUT_LINE('ESQUEMA: '          ||
18                                Registro.OWNER       ||
19                                ' VISTA: '           ||
20                                Registro.VIEW_NAME);
21       END LOOP;
22       CLOSE CursorVistas;
23  END CURSOR_OPEN_FETCH;
24  /
 
Procedimiento creado.

Verificamos la existencia del procedimiento creado.

SQL> DESCRIBE CURSOR_OPEN_FETCH
PROCEDURE cursor_open_fetch

Verificamos si están habilitados los mensajes por pantalla. Si no lo están, los habilitamos.

SQL> SHOW SERVEROUTPUT
serveroutput OFF
 
SQL> SET SERVEROUTPUT ON

Ejecutamos el procedimiento y vemos los mensajes en pantalla asociados a la recuperación de cada fila del cursor.

SQL> EXECUTE CURSOR_OPEN_FETCH
ESQUEMA: SYS VISTA: ALL_TABLES
ESQUEMA: SYS VISTA: ALL_TAB_COLS
ESQUEMA: SYS VISTA: ALL_TAB_COLUMNS
ESQUEMA: SYS VISTA: ALL_TAB_COL_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_COMMENTS
ESQUEMA: SYS VISTA: ALL_TAB_HISTOGRAMS
ESQUEMA: SYS VISTA: ALL_TAB_MODIFICATIONS
ESQUEMA: SYS VISTA: ALL_TAB_PARTITIONS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_MADE
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_RECD
ESQUEMA: SYS VISTA: ALL_TAB_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_STATS_HISTORY
ESQUEMA: SYS VISTA: ALL_TAB_SUBPARTITIONS
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 6 Performing SQL Operations from PL/SQL
Título: Managing Cursors in PL/SQL
Subtítulo: Explicit Cursors

También podemos leer una descripción amplia sobre cómo decide el motor de la base de datos qué datos mostrar al ejecutar el OPEN del cursor en la documentación estandar de Oracle:

Manual: Oracle Database Concepts
Capítulo: 13 Data Concurrency and Consistency
Título: How Oracle Manages Data Concurrency and Consistency
Subtítulo: Statement-Level Read Consistency

Entradas relacionadas:
PL/SQL Tutorial
Cursores PL/SQL notación SELECT..INTO
Cursores PL/SQL notación FOR...LOOP
Cursores PL/SQL notación CURSOR...FOR...LOOP
Cursores PL/SQL notación OPEN...FETCH...CLOSE
Cursores PL/SQL notación FETCH...BULK COLLECT INTO...

Cursores PL/SQL notación CURSOR...FOR...LOOP

Esta notación de cursores PL/SQL agrega a la notación mas simple FOR...LOOP la posibilidad de definir la sentencia SELECT asociada al cursor en forma independiente del recupero de las filas. Podemos reutilizar el cursor definido ganando flexibilidad y facilidad de mantenimiento. Nuevamente la variable Registro es automática creándose y destruyéndose en forma implicita. Sólo es referenciable dentro del contexto del FOR...LOOP...END LOOP. Los miembros o campos de la variable Registro están definidos por las columnas detalladas en la lista de la sentencia SELECT asociada al cursor (OWNER y VIEW_NAME).

SQL> create or replace
 2  PROCEDURE CURSOR_FOR_LOOP
 3  IS
 4    CURSOR CursorVistas
 5        IS (SELECT OWNER, VIEW_NAME
 6              FROM ALL_VIEWS
 7             WHERE OWNER = 'SYS'
 8               AND VIEW_NAME LIKE 'ALL_TAB%');
 9  BEGIN
10    FOR Registro IN CursorVistas
11    LOOP
12        DBMS_OUTPUT.PUT_LINE('ESQUEMA: '     ||
13                              Registro.OWNER ||
14                              ' VISTA: '     ||
15                              Registro.VIEW_NAME);
16    END LOOP;
17  END CURSOR_FOR_LOOP;
18  /
 
Procedimiento creado.

Verificamos con el comando DESCRIBE si el procedimiento fue creado, luego lo ejecutamos.

SQL> DESCRIBE CURSOR_FOR_LOOP
PROCEDURE CURSOR_FOR_LOOP
 
SQL> EXECUTE CURSOR_FOR_LOOP;
 
Procedimiento PL/SQL terminado correctamente.

Vemos que el procedimiento no muestra los mensajes en pantalla, habilitamos la salida de mensajes y volvemos a ejecutar el procedimiento.

SQL> SET SERVEROUTPUT ON
 
SQL> EXECUTE CURSOR_FOR_LOOP;
ESQUEMA: SYS VISTA: ALL_TABLES
ESQUEMA: SYS VISTA: ALL_TAB_COLS
ESQUEMA: SYS VISTA: ALL_TAB_COLUMNS
ESQUEMA: SYS VISTA: ALL_TAB_COL_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_COMMENTS
ESQUEMA: SYS VISTA: ALL_TAB_HISTOGRAMS
ESQUEMA: SYS VISTA: ALL_TAB_MODIFICATIONS
ESQUEMA: SYS VISTA: ALL_TAB_PARTITIONS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_MADE
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_RECD
ESQUEMA: SYS VISTA: ALL_TAB_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_STATS_HISTORY
ESQUEMA: SYS VISTA: ALL_TAB_SUBPARTITIONS
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Esta notación de cursores nos permite definir el cursor una vez y ejecutarlo múltiples veces. Se facilita al motor de la base de datos la tarea de reconocer la ejecución frecuente de la misma sentencia SELECT, simplificando la reutilización y ahorrando recursos.

También podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 6 Performing SQL Operations from PL/SQL
Título: Querying Data with PL/SQL
Subtítulo: Querying Data with PL/SQL: Explicit Cursor FOR Loops

Entradas relacionadas:
PL/SQL Tutorial
Cursores PL/SQL notación SELECT..INTO
Cursores PL/SQL notación FOR...LOOP
Cursores PL/SQL notación CURSOR...FOR...LOOP
Cursores PL/SQL notación OPEN...FETCH...CLOSE
Cursores PL/SQL notación FETCH...BULK COLLECT INTO...

Cursores PL/SQL notación FOR...LOOP

La selección mas simple de múltiples filas de una tabla se define mediante la notación FOR...LOOP...END LOOP.
El primer paso que mostramos es la creación de un procedimiento almacenado o stored procedure de nombre FOR_LOOP. La variable Registro es automática, sólo existe dentro del alcance del FOR...LOOP...END LOOP. No puede ser referenciada fuera del mismo. Los miembros o campos de la variable Registro están definidos por las columnas detalladas en la lista de la sentencia SELECT asociada al cursor (OWNER y VIEW_NAME).

SQL> create or replace
 2 PROCEDURE FOR_LOOP
 3 IS
 4 BEGIN
 5  FOR Registro IN (SELECT OWNER, VIEW_NAME
 6                      FROM ALL_VIEWS
 7                     WHERE OWNER = 'SYS'
 8                       AND VIEW_NAME LIKE 'ALL_TAB%')
 9  LOOP
10   DBMS_OUTPUT.PUT_LINE('ESQUEMA: '       ||
11                        Registro.OWNER    ||
12                        ' VISTA: '        ||
13                        Registro.VIEW_NAME);
14  END LOOP;
15 END FOR_LOOP;
16 /
 
Procedimiento creado.

Luego verificamos si el procedimiento fue efectivamente creado con el comando DESCRIBE.

SQL> DESCRIBE FOR_LOOP
PROCEDURE FOR_LOOP

Una vez confirmada su existencia habilitamos la salida de mensajes en pantalla y ejecutamos el procedimiento con el comando EXECUTE.

SQL> SET SERVEROUTPUT ON
 
SQL> EXECUTE FOR_LOOP;
ESQUEMA: SYS VISTA: ALL_TABLES
ESQUEMA: SYS VISTA: ALL_TAB_COLS
ESQUEMA: SYS VISTA: ALL_TAB_COLUMNS
ESQUEMA: SYS VISTA: ALL_TAB_COL_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_COMMENTS
ESQUEMA: SYS VISTA: ALL_TAB_HISTOGRAMS
ESQUEMA: SYS VISTA: ALL_TAB_MODIFICATIONS
ESQUEMA: SYS VISTA: ALL_TAB_PARTITIONS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_MADE
ESQUEMA: SYS VISTA: ALL_TAB_PRIVS_RECD
ESQUEMA: SYS VISTA: ALL_TAB_STATISTICS
ESQUEMA: SYS VISTA: ALL_TAB_STATS_HISTORY
ESQUEMA: SYS VISTA: ALL_TAB_SUBPARTITIONS
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

Esta forma de recupero de filas es la mas simple pero la menos flexible de las notaciones de PL/SQL para cursores. Inmediatamente después de definida la sentencia SELECT implementamos el ciclo de procesamiento de las filas recuperadas. Esta proximidad nos facilita la lectura del código fuente pero nos reduce las opciones.

También podemos leer este tema en la documentación estandar de Oracle:

Manual: Oracle Database PL/SQL User's Guide and Reference
Capítulo: 6 Performing SQL Operations from PL/SQL
Título: Querying Data with PL/SQL
Subtítulo: Querying Data with PL/SQL: Implicit Cursor FOR Loop

Entradas relacionadas:
PL/SQL Tutorial
Cursores PL/SQL notación SELECT..INTO
Cursores PL/SQL notación FOR...LOOP
Cursores PL/SQL notación CURSOR...FOR...LOOP
Cursores PL/SQL notación OPEN...FETCH...CLOSE
Cursores PL/SQL notación FETCH...BULK COLLECT INTO...

Primer manual de Oracle para inexpertos

Es muy importante que siendo aprendices tomemos pronto contacto con el manual Oracle Database Concepts. Comprende una extensa guía general sobre la que se apoya el resto de la documentación oficial. Es la base de todo conocimiento de desarrollo y administración de bases de datos Oracle.

- Nos contará el ABC de la base de datos y los conceptos fundamentales.
- No nos dirá cómo codificar aplicaciones ni cómo realizar tareas de administración.

Podemos encontrar el manual en el sitio de la Oracle Technology Network. El acceso aunque gratuito requiere una suscripción por única vez.

Acceso al manual Oracle Database Concepts en OTN
Acceso a la biblioteca de documentación en OTN

Hemos sido espectadores de situaciones como ésta que habrían sido evitadas con una simple lectura previa al alcance la mano:

Gerente de IT: ¿Qué sucede Pérez? Los usuarios de todas las sucursales acusan lentitud general en el módulo de ventas y facturación...
DBA Senior: No sabemos. No tocamos nada. Habrá sido la implementación de la última versión del sistema de gestión comercial.
... horas después...
DBA Senior: Señor Gerente, DESCUBRIMOS que si no agregamos un índice a las columnas que componen la clave foránea de cierta tabla las consultas recorren toda la tabla de principio a fin generando una altísima transferencia de entrada/salida a la memoria y a los discos...