Pro*C, CALL y REF CURSOR como argumento.

Una de las formas, quizá la mejor, de implementar un intercambio eficaz de información entre una aplicación cliente y una base de datos es desarrollar un interfaz en forma de paquetes/procedimientos/funciones que eviten el uso de sentencias SQL desde el cliente: esto simplifica las llamadas (las encapsula), evita la necesidad de construcción de las sentencias SQL en el cliente (con todo lo que ello conlleva: necesidad de conocer la estructura de la base de datos, nombres de tablas, gestión de permisos etc… por no hablar de la corrección de dichas sentencias) y retiene el control de las reglas de negocio dentro de la base de datos.

Para ello se pueden utilizar variables cursor (‘REF CURSORS’) como argumentos o retornos de estos paquetes/procedimientos/funciones. Hay que tener en cuenta que la variable cursor debe estar definida en el código Pro*C. Una llamada a uno de estos paquetes/procedimientos/funciones devolverá un puntero a un ‘REF CURSOR’ abierto por esos mismos paquetes/procedimientos/funciones que el cliente se encargará de procesar y finalmente cerrar.

La documentación Oracle en la guía del precompilador Pro*C/C++ para programadores (Pro*C/C++ Precompiler Programmer’s Guide) apunta que la utilización de CALL es idéntica a la utilización de bloques anónimos PL/SQL. Según esto, una llamada a un procedimiento almacenado se puede hacer de dos formas:

Como bloque anónimo PL/SQL:

EXEC SQL EXECUTE
BEGIN
   procedimiento_almacenado(:parametro1, :parametro2);
END;
END-EXEC;

Como llamada ‘CALL’:

EXEC SQL CALL procedimiento_almacenado(:parametro1, :parametro2);

Pero hay una principal diferencia entre un método y otro. Para ejecutar bloques anónimos el precompilador debe tener activada la opción SQLCHECK=SEMANTICS que hace un chequeo semántico en la compilación, mientras que con llamadas CALL no es necesario esta opción (no se necesita una conexión en el momento de la compilación). Además de esto, el método ‘CALL’ se considera un estándar, mientras que los bloques PL/SQL son ‘propietarios’.

Por otra parte, tenemos el asunto de los REF CURSORs como argumentos de procedimientos: Oracle indica cómo utilizar esta técnica en la misma guía del precompilador Pro*C/C++ para programadores (Pro*C/C++ Precompiler Programmer’s Guide):

Primero hay que crear un tipo ‘REF CURSOR’ en la base de datos y utilizarlo como argumento de un procedimiento almacenado. Así que lo hacemos:

SQL*Plus: Release 9.2.0.8.0 - Production on Lun Nov 20 16:32:09 200

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Conectado a:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

sql> CREATE OR REPLACE
2  PACKAGE PRUEBA_REF_CURSOR
3  AS
4     TYPE RESULT IS REF CURSOR;
5     PROCEDURE SP01 (p_result IN OUT RESULT);
6  END PRUEBA_REF_CURSOR;
7  /
Paquete creado.

sql> CREATE OR REPLACE
2  PACKAGE BODY PRUEBA_REF_CURSOR
3  AS
4     PROCEDURE SP01 (p_result IN OUT RESULT)
5     IS
6     BEGIN
7        OPEN p_result FOR
8                   SELECT 1, 'Hola'
9                     FROM DUAL;
10      END SP01;
11
12  END PRUEBA_REF_CURSOR;
13  /

Cuerpo del paquete creado.

Para implementar una pequeña aplicación cliente de ejemplo, creamos un pequeño fichero Pro*C:

#include "stdio.h"
#include "stdlib.h"
#include "string.h"

EXEC SQL INCLUDE sqlca;

EXEC SQL WHENEVER SQLERROR DO sql_error();

void sql_error() {
   char msg[200];
   size_t buf_len, msg_len;
   buf_len = sizeof (msg);
   sqlglm(msg, &buf_len, &msg_len);
   printf("%.*snn", msg_len, msg);
   exit(1);
}

void main()
{

   EXEC SQL BEGIN DECLARE SECTION;
   char username[] = "xxxxxxxx/yyyyyyyy@zzzzzzzz";

   SQL_CURSOR pet_cursor;

   int i_v_dummy1;
   char c_v_dummy2[5];

   EXEC SQL END DECLARE SECTION;

   EXEC SQL CONNECT :username;
   printf("nConectado a ORACLEn");

   EXEC SQL ALLOCATE :pet_cursor; // Constructor - ALLOCATE Cursor.

   printf("nLlamando a bloque PL/SQL:n");

   EXEC SQL EXECUTE
   BEGIN
      PRUEBA_REF_CURSOR.SP01(:pet_cursor);
   END;
   END-EXEC;

   //Loop:
   EXEC SQL WHENEVER NOT FOUND DO break;

   for (;;){

      EXEC SQL FETCH :pet_cursor
                INTO :i_v_dummy1, c_v_dummy2;

      printf("%d %sn", i_v_dummy1, c_v_dummy2);
   }

   EXEC SQL CLOSE :pet_cursor;
   EXEC SQL FREE :pet_cursor; // Destructor - FREE Cursor.

   EXEC SQL COMMIT WORK RELEASE;
   exit(0);
}

Tras precompilarlo, compilarlo y ejecutarlo la salida es:

D:Pruebas_ProC>prueba.exe

Conectado a ORACLE

Llamando a bloque PL/SQL:
1 Hola

D:Pruebas_ProC>

Hasta aquí todo OK, vamos ahora con la versión ‘CALL’:

#include "stdio.h"
#include "stdlib.h"
#include "string.h"

EXEC SQL INCLUDE sqlca;

EXEC SQL WHENEVER SQLERROR DO sql_error();

void sql_error() {
   char msg[200];
   size_t buf_len, msg_len;
   buf_len = sizeof (msg);
   sqlglm(msg, &buf_len, &msg_len);
   printf("%.*snn", msg_len, msg);
   exit(1);
}

void main()
{

   EXEC SQL BEGIN DECLARE SECTION;
   char username[] = "xxxxxxxx/yyyyyyyy@zzzzzzzz";

   SQL_CURSOR pet_cursor;

   int i_v_dummy1;
   char c_v_dummy2[5];

   EXEC SQL END DECLARE SECTION;

   EXEC SQL CONNECT :username;
   printf("nConectado a ORACLEn");

   EXEC SQL ALLOCATE :pet_cursor; // Constructor - ALLOCATE Cursor.

   printf("nLlamando a bloque PL/SQL:n");

   EXEC SQL CALL PRUEBA_REF_CURSOR.SP01(:pet_cursor);

   //Loop:
   EXEC SQL WHENEVER NOT FOUND DO break;

   for (;;){

      EXEC SQL FETCH :pet_cursor
                INTO :i_v_dummy1, c_v_dummy2;

      printf("%d %sn", i_v_dummy1, c_v_dummy2);
   }

   EXEC SQL CLOSE :pet_cursor;
   EXEC SQL FREE :pet_cursor; // Destructor - FREE Cursor.

   EXEC SQL COMMIT WORK RELEASE;
   exit(0);
}

Tras precompilarlo, compilarlo y ejecutarlo la salida es:

D:Pruebas_ProC>prueba.exe

Conectado a ORACLE

Llamando a bloque PL/SQL:
ORA-01002: recuperación fuera de secuencia

D:Pruebas_ProC>

¡ERROR! ¿Pero, no dice Oracle que cualquiera de los dos métodos dará los mismos resultados? No lo parece…

Se intuye que los problemas aparecen al efectuar el ‘fetch’ dentro del ‘loop’. Así que, búsquedas en Google, búsquedas en la documentación, pruebas, pruebas, pruebas… al final encontramos una referencia en Metalink que puede estar relacionada: un ‘bug’, aunque es de 2001 y se refiere a Solaris!

Probamos la solución, que no es otra que incrementar el valor de la estructura sql_cursor ‘curocn’. Parece ser que las estructuras ‘C’ sql_cursor generadas en el caso del ‘CALL’ son basadas en cero (zero-based), mientras que el puntero interno de los ‘REF CURSORS’ es basado en uno (one-based). De ahí que los ‘fetch’ canten error de secuencia de recuperación…

Así pues, añadimos este incremento justo antes de comenzar el ‘loop’ en el que hacemos los ‘fetch’:

#include "stdio.h"
#include "stdlib.h"
#include "string.h"

EXEC SQL INCLUDE sqlca;

EXEC SQL WHENEVER SQLERROR DO sql_error();

void sql_error() {
   char msg[200];
   size_t buf_len, msg_len;
   buf_len = sizeof (msg);
   sqlglm(msg, &buf_len, &msg_len);
   printf("%.*snn", msg_len, msg);
   exit(1);
}

void main()
{

   EXEC SQL BEGIN DECLARE SECTION;
   char username[] = "xxxxxxxx/yyyyyyyy@zzzzzzzz";

   SQL_CURSOR pet_cursor;

   int i_v_dummy1;
   char c_v_dummy2[5];

   EXEC SQL END DECLARE SECTION;

   EXEC SQL CONNECT :username;
   printf("nConectado a ORACLEn");

   EXEC SQL ALLOCATE :pet_cursor; // Constructor - ALLOCATE Cursor.

   printf("nLlamando a bloque PL/SQL:n");

   EXEC SQL CALL PRUEBA_REF_CURSOR.SP01(:pet_cursor);

   //Loop:
   EXEC SQL WHENEVER NOT FOUND DO break;
   ++pet_cursor.curocn;                                  //Magia Metalink!
   for (;;){

      EXEC SQL FETCH :pet_cursor
                INTO :i_v_dummy1, c_v_dummy2;

      printf("%d %sn", i_v_dummy1, c_v_dummy2);
   }

   EXEC SQL CLOSE :pet_cursor;
   EXEC SQL FREE :pet_cursor; // Destructor - FREE Cursor.

   EXEC SQL COMMIT WORK RELEASE;
   exit(0);
}

Precompilar, compilar, enlazar y…

D:Pruebas_ProC>prueba.exe

Conectado a ORACLE

Llamando a bloque PL/SQL:
1 Hola

D:Pruebas_ProC>

Voilà! ¡Todo funciona de maravilla!

Son este tipo de cosas las que nos hacen la vida tan apasionante a los desarrolladores Oracle…

Finalmente, los hechos:
Versión del Preprocesador Oracle Pro*C: 9.2.0.0
Compilador MicroSoft Visual C++ 6.0
Versión de la base de datos: 9.2.0.8

Saludos.

Carlos.