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.

16 respuestas a Pro*C, CALL y REF CURSOR como argumento.

  1. antonio dice:

    Excelente material, me saco de un apuro

    Gracias!!!

  2. Nacho dice:

    Sabes tengo un problema con los include en ProC, sabes algo de eso? ya que he encontrado muy poca información en la red.

    Vale y muy buen material es q has puesto

  3. carlosal dice:

    Nacho:

    “Sabes tengo un problema con los include en ProC, sabes algo de eso? ya que he encontrado muy poca información en la red.”

    Tendrás que ser algo más preciso. ¿Qué problema?¿Algún error? ¿Versión? ¿S.O.? ¿Qué compilador? etc, etc…

    Un saludo.

    Carlos.

  4. max dice:

    Quien sabe de donde puedo descargar un compilar?

    se agradeceria mucho

  5. Mefisto dice:

    Estuve siguiendo el tema del CALL. No me funciona con variables normales llamando a un package procedure con variables in y out. Ej.:

    EXEC SQL INCLUDE ORACLE_DECLARE_SECTION;
    VARCHAR SQL_lage[100];
    VARCHAR SQL_tageper[5];
    VARCHAR SQL_tageaut[5];
    VARCHAR SQL_matricula[16];
    VARCHAR SQL_ruc[12];
    VARCHAR SQL_cage[5];
    VARCHAR SQL_date[20];

    EXEC SQL INCLUDE ORACLE_SQL_SECTION;

    EXEC SQL CALL PA_AGE.P_OBTNR_AGEDAT_OTROS(:SQL_ruc, :SQL_cage,
    :SQL_date, :SQL_lage, :SQL_tageper, :SQL_tageaut, :SQL_matricula);

    /*Los 4 últimos son output,los 3 primeros input*/
    Compila, crea el ejecutable, chcihe bombón, pero al llamarlo, por ejemplo desde el WinTpCall, crea un error:

    ExeFile : prueba – Thu May 17 15:57:10 2007
    Error. Funcion: – Mensaje: FILE NAME: sd_traeaduana.pc
    LINE IN FILE: 96
    SQL STMT: ORA-06576: not a valid function or procedure name

    El paquete se llama PA_AGE y el procedimiento P_OBTNR_AGEDAT_OTROS, cuál sería el problema??

  6. carlosal dice:

    No tengo la bola de cristal a mano, pero yo miraría:

    Que P_OBTNR_AGEDAT_OTROS es un procedimiento y no una función.
    Que los argumentos y tipos de los argumentos coinciden con los de la especificación del paquete (esta podría ser la causa más probable) y el orden es el correcto.
    Que la sesión utilizada por el programa pro*C tiene visibilidad y privilegios sobre el paquete.
    Etc, etc, etc…

    Prueba a invocarlo desde SQL*Plus y mira a ver qué pasa.

    Saludos.

    Carlos.

  7. masterpc dice:

    estoy trabajando en c#.net y oracle quiero que me ayuden con el siguietne problemita:
    quiero realizar un SP_cliente, quier mandarle y recibir parametros todo en oracle, me ayudan porfa.

  8. Juan Alonso dice:

    S.O.S :
    Buenas, necesito saber como se ejecuta un PL/SQL que está en BBDD desde un .cpp en Pro *C

    Gracias

  9. carlosal dice:

    >>“Buenas, necesito saber como se ejecuta un PL/SQL que está en BBDD desde un .cpp en Pro *C”

    ¿Qué parte de EXEC SQL CALL PRUEBA_REF_CURSOR.SP01(:pet_cursor);
    no has entendido?

    Saludos.

    Carlos.

  10. Graces dice:

    Hola, Gracias por compartir esta informacion me sirvio mucho, lo de tip de incrementar el cursor al inicio…

  11. Iosmio dice:

    Por casualidad tenéis información o manuales en Español de Pro*C? estoy empezando con el y ando muy pegado.

  12. carlosal dice:

    Iosmio.

    La documentación de Oracle (pro*C incluido) está únicamente en inglés (al menos que yo sepa).

    Saludos.

    Carlos.

  13. Oscar TT dice:

    Una pregunta Carlos, en donde realizas la conexion

    EXEC SQL BEGIN DECLARE SECTION;
    char username[] = “xxxxxxxx/yyyyyyyy@zzzzzzzz”;
    …..
    EXEC SQL END DECLARE SECTION;

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

    Se puede conectar a un base de datos que se encuentre en otro servidor???

    Es decir se puede poner la IP, user, pass, SID ???

  14. Oscar TT dice:

    ohh ya no gracias carlos, tal parece que lo que tengo que hacer es agregar un alias al archivo TNSNAMES.ORA de mi oracle local hacia la BD remota, mmmm interesante.. probare y haber si furulaaa…. buen articulo..

  15. Oscar TT dice:

    =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(Host = )(Port = ))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = )
    )
    )

    ORA11 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ORA11)
    )
    )

    $ORACLE_HOME/network/admin/tnsnames.ora

  16. CarlosAL dice:

    Óscar:

    En realidad a lo que debes apuntar es a la dirección y puerto de un listener, que bien podría estar en una máquina distinta a la instancia (o instancias) a la que te esrtás conectando. En configuraciones sencillas normalmente el listener y la instancia están en la misma máquina.

    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: