Uso de Rollback Segments por sentencias SELECT

En alguna ocasión algún lector me ha preguntado, con cierta sorpresa, acerca del por qué una sentencia SELECT le fallaba con el mensaje de error "No es posible ampliar el segmento de rollback" ("Unable to extend rollback segment"). La sorpresa proviene del hecho de que son muchos los desarrolladores PL/SQL los que piensan que los segmentos de rollback sólo se utilizan cuando se emplean sentencias PLSQL en las que se modifican o actualizan datos dentro de la base de datos Oracle. Bajo este tipo de pensamiento es normal que, cuando se produce el error mencionado anteriormente al ejecutar una sentencia SELECT, uno se pregunte: ¿utiliza la base de datos Oracle segmentos de rollback al ejecutar sentencias SELECT?

Bueno, en mi opinión, lo primero que hay que hacer es reformular la pregunta y cambiarla por la siguiente: ¿una sentencia SELECT necesita crear o leer segmentos de rollback?  La pregunta formulada de esta manera seguro que nos ayudará a comprender mejor este artículo, ya que el verbo "utilizar" usado en la primera pregunta no es lo suficientemente específico.

En PL/SQL, todas las sentencias SELECT tienen el potencial de utilizar los datos de rollback o undo  (o datos de vuelta atrás). La base de datos Oracle a la hora de procesar cualquier tipo de consulta utiliza los segmentos de rollback para producir conjuntos de resultados de lectura consistente (la lectura consistente es una característica de las bases de datos Oracle que asegura que todos los registros dentro de un conjunto de resultados, cuando son presentados a una aplicación PLSQL o a cualquier otro tipo de aplicación, provengan de un mismo instante de tiempo).

No obstante, este uso de los segmentos de rollback no va a causar nunca por sí solo un error como el antes mencionado (ORA-01650 Unable to extend rollback segment... u ORA-01651 Unable to extend undo segment... ). Sin embargo, sí que puede provocar un error del tipo "ORA-01555 Snapshot too old".

Para que una sentencia SELECT pueda generar los errores ORA-01650 u ORA-01651, es necesario que esté generando segmentos de rollback y los motivos pueden ser los siguientes:

• La sentencia SELECT contiene la cláusula FOR UPDATE.
• La funcionalidad de auditoría está habilitada.
• La sentencia SELECT invoca a algún tipo de transacción que escribe en la base de datos Oracle.

Lo más corriente es que la causa sea que la sentencia SELECT contenga una cláusula FOR UPDATE, cuando esto ocurre la base de datos Oracle bloquea todos los registros necesarios antes de que la sentencia SELECT empiece a devolver resultados, y bloquear un registro en la base de datos Oracle implica modificar un bloque de la base de datos para registrar dicho bloqueo. Por otro lado, cada vez que se modifica un bloque de la base de datos, se necesita generar un undo para esa operación.

Demostrar este hecho es muy sencillo. Creemos una sesión SQL y ejecutemos los siguientes comandos sin que haya ningún otro usuario utilizando la base de datos:

SQL> SELECT used_ublk FROM v$transaction

No rows selected

SQL> BEGIN
  2    FOR cursor IN (
  3      SELECT * FROM nombre_tabla
  4        FOR UPDATE)
  5    LOOP null;
  6    END LOOP;
  7  END;
  10 /

PL/SQL procedure successfully completed.

SQL> SELECT used_ublk FROM v$transaction

USED_UBLK
---------
      932

SQL> COMMIT;

Según queríamos confirmar, podemos ver que la ejecución de la sentencia SELECT FOR UPDATE ha generado 932 bloques de undo (tened en cuenta que nombre_tabla debe ser una tabla que exista en vuestra base de datos Oracle).

La cabecera de los .DBF

Me parece muy interesante:

http://www.fpress.com/revista/Num9906/Jun99.htm

Funciones PLSQL TO_CHAR, EXTRACT, TO_DATE y TO_TIMESTAMP (conversión de fechas a caracteres y viceversa)

Pero antes de hablar de estas funciones, resulta conveniente conocer como se puede obtener desde PLSQL el valor de la fecha y el tiempo actual. Seguro que la gran mayoría de vosotros, si sois programadores PL/SQL, ya conocéis la clásica función SYSDATE, función que, sin duda, es la más empleada en este sentido. No obstante, la base de datos Oracle ofrece la posibilidad de utilizar otras funciones que proporcionan diferentes variantes del valor de la fecha y el tiempo actual y que veremos a continuación.

Las funciones PLSQL de las que hablo son:

FUNCION SQL        ZONA HORARIA      TIPO DE DATO DEVUELTO
CURRENT_DATE       Sesión            DATE
CURRENT_TIMESTAMP  Sesión            TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP     Sesión            TIMESTAMP
SYSDATE            Servidor de BBDD  DATE
SYSTIMESTAMP       Servidor de BBDD  TIMESTAMP WITH TIME ZONE

En el siguiente ejemplo podéis ver los valores devueltos por las funciones SYSDATE y SYSTIMESTAMP.

BEGIN
DBMS_OUTPUT.put_line (SYSDATE);
DBMS_OUTPUT.put_line (SYSTIMESTAMP);
DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00
-000000000 00:00:00.437000000

En el ejemplo podemos observar que al haber utilizado la función PLSQL DBMS_OUTPUT.PUT_LINE para mostrar los valores entregados por las funciones SYSDATE y SYSTIMESTAMP, la base de datos Oracle implícitamente convierte los datos devueltos en una cadena de caracteres, para ello utiliza el formato fecha que por defecto tiene asignado la base de datos o la sesión (esto se especifica al definir el valor del parámetro NLS de base de datos denominado NLS_DATE_FORMAT, otros parámetros relacionados son NLS_TIMESTAMP_FORMAT y NLS_TIME_FORMAT). La instalación por defecto de cualquier base de datos Oracle configura el formato de fecha con el valor DD-MON-YYYY.

También debéis daros cuenta de que al restar al valor devuelto por la función SYSTIMESTAMP el valor entregado por SYSDATE, el resultado es un intervalo que, aun siendo muy cercano a cero, no es exactamente cero.

Conversión de fechas a carácter

De igual manera que con la función TO_CHAR es posible convertir números a caracteres, otra versión de la misma función nos permite hacer lo mismo con los tipos de dato asociados con fechas y tiempo. De igual manera que con los números, TO_CHAR ofrecen un gran número de posibilidades para formatear fechas y que estas aparezcan tal y como queramos.

Veamos algunos ejemplos:

Si utilizamos TO_CHAR sin ningún tipo de mascara de formato, entonces la cadena de caracteres devuelta por la función será la misma que devuelve la base de datos Oracle cuando realiza una conversión implícita (según vimos que ocurría al emplear la función DBMS_OUTPUT.PUT_LINE).

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE));
  DBMS_OUTPUT.put_line (TO_CHAR (SYSTIMESTAMP));
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00

Si queremos que la función PL/SQL TO_CHAR devuelva el día de la semana y el nombre del mes, entonces utilizaríamos la siguiente máscara:

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'Day, DD "de" Month "de" YYYY'));
END;
/

Martes   , 21 de Febrero    de 2012

En este sentido debemos saber que el idioma utilizado para mostrar los datos de la fecha viene determinado por el valor del parámetro de la base de datos Oracle NLS_DATE_LANGUAGE, un valor que también puede pasarse como tercer argumento de la función TO_CHAR como podemos ver en el siguiente ejemplo:

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'Day, DDth Month YYYY',
             'NLS_DATE_LANGUAGE=English'));
END;
/

Tuesday  , 21ST February  2012

En los anteriores ejemplos observaréis que la función TO_CHAR devuelve algunos caracteres extra en blanco, esto ocurre porque por defecto la base de datos Oracle añade dichos caracteres hasta completar la máxima longitud posible del día de la semana o del mes. Esta claro que en la mayoría de los casos querremos evitar que estos caracteres extra aparezcan, para ello disponemos del elemento formateador FM, que si lo añadimos al principio de nuestra máscara eliminará los mencionados espacios.

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'FMDay, DD "de" Month "de" YYYY'));
END;
/

Martes, 21 de Febrero de 2012

También es posible utilizar determinadas formatos que nos permitirán mostrar informaciones variadas relacionadas con una fecha.

Cuatrimestre:
TO_CHAR (SYSDATE, 'Q') -> 1

Día del año:
TO_CHAR (SYSDATE, 'DDD') -> 052

Diferentes formatos de fecha y hora:
TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') -> 21-02-2012 14:06:30
TO_CHAR (SYSDATE, 'DD-MON-YY HH:MI:SS AM') -> 21-FEB-12 02:06:30 PM

Otra de las funciones PL/SQL que permite manejar fechas es la función EXTRACT, una función que permite extraer los valores de diferentes elementos de un dato de tipo fecha. A continuación os dejo diferentes empleos de la misma.

Año:
EXTRACT (YEAR FROM SYSDATE) -> 2012

Día del mes:
EXTRACT (DAY FROM SYSDATE) -> 21

Por otro lado, para convertir una cadena de caracteres a un dato de tipo fecha se utilizan las funciones TO_DATE y TO_TIMESTAMP. Estas funciones también admiten la utilización de una máscara para indicar el formato de la fecha y para configurar el idioma de la misma.

A continuación os dejo un ejemplo con varios usos de la función TO_DATE.

DECLARE
  l_date DATE;
BEGIN
  l_date := TO_DATE ('21-FEB-2012');
  l_date := TO_DATE ('022112', 'MMDDYY');
  l_date := TO_DATE ('21-Feb-12', 'DD-Mon-YY HH:MI:SS');
  l_date := TO_DATE ('Feb/21/12', 'Mon/DD/YY HH:MI:SS');
  l_date := TO_DATE ('Febrero.21.2012', 'Month.DD.YYYY HH:MI:SS');
END ;

Obviamente si la máscara que pasamos a las funciones TO_DATE o TO_TIMESTAMP no se corresponde con el formato de la fecha que queremos convertir, la base de datos Oracle generará un error.

Por ejemplo, la siguiente consulta SQL devolverá el error "ORA-01843: mes no válido":

SELECT TO_DATE ('21-02-2012', 'DD-MON-YYYY')
FROM dual;

Desde el punto de vista de la programación PL/SQL, conviene tener mucha precaución a la hora de utilizar las funciones TO_DATE o TO_TIMESTAMP, sobre todo si se utilizan sin especificar una máscara concreta (es decir, utilizarán la máscara que emplea la base de datos Oracle por defecto), ya que cambios posteriores del parámetro de base de datos NLS_DATE_FORMAT pueden provocar errores en nuestro programa.

Tuning o puesta a punto de consultas SELECT COUNT(*) en PL/SQL

De vez en cuando recibo consultas sobre cómo sería posible mejorar el rendimiento de sentencias PL/SQL concretas. En la mayoría de los casos contestar a estas preguntas puede ser poco menos que imposible, más que nada porque realizar el tuning de una consulta PL/SQL sin conocer el contexto en que se ejecuta dicha consulta resulta muy complicado. Cada vez que esto ocurre siempre me asaltan preguntas como: ¿por qué se ejecuta dicha consulta?, ¿puede eliminarse la consulta y ser incluida en otro proceso?, ¿está la consulta dentro de un bucle LOOP y realmente debe formar parte del bucle?, ¿están creados todos los índices que podrían acelerar su ejecución? Por si esto fuera poco, una vez que tenemos la respuesta a preguntas como las antes mencionadas, sin duda, surgirán nuevas preguntas.

No obstante, el otro día un asiduo lector de este blog me envió una consulta SELECT bastante sencilla que, aún utilizando los índices de forma adecuada y ejecutándose bastante rápido, terminaba consumiendo muchos recursos de CPU en su base de datos Oracle debido a que era ejecutaba con mucha frecuencia dentro un procedimiento PLSQL. Dicho lector me pedía ayuda para realizar el tuning o puesta a punto de la mencionada consulta.

La consulta SQL era la siguiente:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id(+)
  AND  t2.otro_id = v_otro_id;

Lo primero que se me vino a la mente es que había muchas posibilidades de que realmente no hiciera falta ejecutar dicha sentencia, y no existe ninguna posibilidad de ejecutar de forma más rápida una sentencia SELECT que no tener que ejecutarla. El caso es que siempre que me encuentro una cláusula COUNT(*) en una consulta SQL, tiendo a intentar eliminarla. La razón es que en la mayoría de los casos el procedimiento PL/SQL que la contiene, utiliza dicho COUNT(*) para hacer lo siguiente:

SELECT COUNT(*)
INTO   v_count
FROM   ...;

IF v_count > 0 THEN
  ejecutar_algo();
ENF IF;

En mi opinión, en estos casos lo mejor para el rendimiento de la base de datos Oracle es reescribir el código antes mencionado y reemplazarlo por un código tan simple como este:

ejecutar_algo();

Si el procedimiento PLSQL ejecutar_algo() lo que hace es analizar un conjunto de resultados dentro de un bucle, terminando cuando dicho conjunto ha sido analizado en su totalidad, entonces llamar a dicho procedimiento PLSQL cuando no hay datos para procesar, significará que el conjunto de resultados inicial estará vacío y la rutina ejecutar_algo() no hará nada. Por otro lado, si existen datos para procesar, entonces el procedimiento PL/SQL se ejecutará más rápido porque habremos eliminado la primera consulta SELECT realmente innecesaria. Y si lo pensamos detenidamente, es más que probable que incluso no habiendo datos para procesar, también el rendimiento de nuestro proceso mejore.

De cualquier forma, si asumimos que por cualquier motivo no hay forma de eliminar la consulta SELECT y que ésta tiene que ser realmente ejecutada, entonces deberemos pensar en qué podemos hacer para ponerla a punto o "tunearla". En el caso que os he planteado, algo que me resultó evidente nada más ver la consulta fue que el outer join podía eliminarse sin problemas, no en vano la primera consulta SQL es equivalente a esta otra:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id
  AND  t2.otro_id = v_otro_id;

El outer join desde la tabla t1 a la t2 lo que hace es devolver resultados de la tabla t1 cuando no existe ninguno asociado por el mismo id en la t2, pero en este caso el campo t2.otro_id será NULL, y el valor NULL nunca es igual a nada, por lo que el último límite de la consulta nunca se cumplirá cuando la tabla t2 no devuelve ningún registro asociado.

No obstante, realizar este cambio en el código PLSQL no implicará ninguna mejora en el rendimiento de nuestro proceso, y esto es debido a que el optimizador de la base de datos Oracle es lo suficientemente "inteligente" como para darse cuenta de que puede eliminar sin problemas el outer join, y así lo hará al escribir el plan de ejecución correspondiente. Lo único que realmente habremos hecho es conseguir que nuestra consulta SELECT esté escrita de forma correcta.

Por lo tanto, llegado a este punto tuve que preguntarme si verdaderamente se podía hacer algo más para mejorar el rendimiento de esa sentencia SELECT tan simple, y me encontré con que sabiendo lo que sabía no era posible hacer nada. Sin conocer nada acerca del esquema de la base de datos Oracle, no podía hacer ninguna suposición, ni llegar a ninguna conclusión. ¿Realmente se podría hacer algo más conociendo el esquema y la relación existente entre las tablas t1 y t2? La respuesta es sí, pero esto será materia de otro artículo.

Diferencias entre COUNT(1) y COUNT(*)

Miles y miles y miles de veces veo que se arman consultas SQL que contiene el COUNT(1). Este es un viejo mito. Muchos piensan que colocar COUNT(1) en vez del COUNT(*) mejora la performance de la consulta.... pero en realidad mejora la performance?

Veamos...

SQL_9iR2> CREATE TABLE test AS
 2  SELECT level id, 'texto_'||level texto
 3  FROM dual
 4  CONNECT BY level <= 100000 ;  Table created.  SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Bien, ahora ejecutemos las 2 consultas:

SQL_9iR2> ALTER SESSION SET SQL_TRACE = TRUE ;

Session altered.

SQL_9iR2> SELECT COUNT(1)
 2  FROM test ;

 COUNT(1)
----------
   100000

1 row selected.

SQL_9iR2> SELECT COUNT(*)
 2  FROM test ;

 COUNT(*)
----------
   100000

1 row selected.

SQL_9iR2> ALTER SESSION SET SQL_TRACE = FALSE ;

Session altered.

Veamos lo que nos muestra el TKPROF:

select count(1)
from
test

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

select count(*)
from
test

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

Observamos que en las 2 consultas tenemos la misma cantidad de lecturas de bloques (disk, query, current) y el mismo tiempo de elapsed y cpu.
Las 2 consultas son idénticas y no hay un incremento en la performance por utilizar el COUNT(1) en vez del COUNT(*).

Incluso podría poner cualquier cosa en el COUNT... obtendríamos los mismos resultados:

SELECT COUNT(2222222)
FROM
TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

SELECT COUNT('EJEMPLO')
FROM
TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

SELECT COUNT(12345)
FROM
TEST

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        306          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0        306          0           1

Bien, por último veamos los siguiente. Qué sucede si realizo un COUNT de alguna de las columnas de la tabla? Veamos...

SQL_9iR2> SELECT COUNT(texto)
 2  FROM test ;

COUNT(TEXTO)
------------
     100000

1 row selected.

La consulta nos devolvió la cantidad de registros totales de la tabla. Ahora veamos qué me devuelve la consulta si la columna TEXTO tiene valores nulos.

SQL_9iR2> UPDATE test
 2  SET texto = NULL
 3  WHERE id > 50000 ;

50000 rows updated.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT COUNT(texto)
 2  FROM test ;

COUNT(TEXTO)
------------
      50000

1 row selected.

Como podemos observar, el COUNT sobre una columna sólo cuenta la cantidad de valores que no son nulos. Por lo tanto, tenemos que tener cuidado a la hora de realizar un COUNT y tener en cuenta estos pequeños detalles.

# Introducción a PL/SQL

SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los lenguajes de programación. Para abordar el presente tutorial con mínimo de garantias es necesario conocer previamente SQL. PL/SQL amplia SQL con los elementos caracteristicos de los lenguajes de programación, variables, sentencias de control de flujo, bucles ...        Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. PL/SQL es el lenguaje de programación que proporciona Oracle para extender el SQL estándar con otro tipo de instrucciones.        SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los lenguajes de programación. No permite el uso de variables, estructuras de control de flujo, bucles ... y demás elementos caracteristicos de la programación. No es de extrañar, SQL es un lenguaje de consulta, no un lenguaje de programación.        Sin embargo, SQL es la herramienta ideal para trabajar con bases de datos. Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. PL/SQL es el lenguaje de programación que proporciona Oracle para extender el SQL estándar con otro tipo de instrucciones y elementos propios de los lenguajes de programación .

SQL LOADER

Sql Loader es una utilidad que proporciona Oracle para cargar datos a una base de datos desde un fichero externo, normalmente un fichero de texto aunque tambien pueden ser ficheros binarios.

Al SQLLoader ( sqlldr) se le pasan como parametros (los más importanes) el fichero que contiene los datos que se van a cargar y la ruta del fichero de control que contiene las acciones a realizar. El formato de los datos, donde se cargaran y cualquier otro tipo de control.

La competencia de Oracle Business Suite: Open Apps de Velneo V7

Hace unos días me propusieron hacer una revisión y opinar sobre Velneo V7, una plataforma completa con base de datos integrada de desarrollo de aplicaciones empresariales que además incorpora plantillas de código abierto y editable FLOSS para desarrollar aplicaciones del tipo ERP (Enterprise Resource Planning) o CRM (Customer Relationship Management). Por lo tanto Velneo es, de alguna manera, competencia de Oracle Business Suite, aunque ni por las dimensiones de una y otra empresa, ni por lo que ofrecen una aplicación y otra, esa competencia sea real.

Las aplicaciones de gestion desarrolladas con Velneo V7 pueden ejecutarse en múltiples plataformas (Windows, Linux, Mac o MeeGo), soportando arquitectura Cliente/Servidor  con escritorios monopuesto y multipuesto, SaaS, HTML Web, Web RIA y terminales móviles.

Velneo V7 es una plataforma completa  que no necesita componentes de terceros ni DLLs, con un multiservidor que distribuye aplicaciones, datos (base de datos), web, disco y ediciones. Todos los componentes (componentes de edición, de servicio y administración, y de ejecución) se conectan al servidor para poder realizar todas las fases del ciclo de desarrollo y despliegue.

Las aplicaciones se pueden desarrollar, administrar y ejecutar en local, en la nube y a través de dispositivos móviles, soportando todos los sistemas operativos simultáneamente.

Permite controlar el ciclo de desarrollo completo: diseño del proyecto, diseño de la base de datos, crear la lógica del negocio, construir y generar la interfaz de usuario, y crear el software de instalación.

Velneo V7 ha sido especialmente diseñada para desarrollar aplicaciones de gestión empresarial destacando por la ejecución nativa multiplataforma (sin máquinas virtuales), el código distribuido y reutilizable, y la integración total de bases de datos, lógica del negocio e interfaz de usuario.

La base de datos de Velneo V7 destaca por su rapidez y alto rendimiento en la ejecución de búsquedas y transacciones de bases de datos, su gran fiabilidad y la utilización de programación avanzada sin usar sentencias SQL.

Además, Velneo V7 dispone de un amplio catálogo de Open Apps con tutoriales y ejemplos para aprender a trabajar con la aplicación, con componentes que pueden reutilizarse en nuestro código, y con plantillas empresariales para poder desarrollar nuestras propias aplicaciones. Entre estas Open Apps destacaremos vConta (software para contabilidad), vGestion (gestión de compras, ventas y almacén), vCash (gestión de cuentas bancarias), vTodo Plus (software de planificación y productividad empresarial) y Business Center (software de gestion ERP).

Invocar Web Services desde PL/SQL en Oracle

Muchas veces nos vemos en la necesidad de invocar un Web Service directamente desde la base de datos.

La utilización de Java dentro de la base de datos no siempre es una opción disponible, así que tuve que buscar una solución que sólo utilizara código PL/SQL.

La solución

Podemos utilizar el package UTL_HTTP para hacer los request SOAP directamente al servidor destino, esto si bien es más complicado que utilizar las clases proxy, nos evita tener que cargar Java en la base de datos.

La forma de invocar el package UTL_HTTP es la siguiente:

DECLARE
 
    req   UTL_HTTP.req := NULL;
    resp  UTL_HTTP.resp := NULL;
    respVal VARCHAR2(32000);
    reqXML VARCHAR2(32760);
 
BEGIN
 
/*Generamos un Request a la URL destino, el método debe ser POST */
    req := UTL_HTTP.begin_request('http://servidor/ConsultaClientes', 'POST');
 
/*Creamos un mensaje SOAP tal cual se define en el WSDL*/
 
reqXML := '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:m0="ConsultaClientes">
      <SOAP-ENV:Body>
            <m:ConsultaClientes xmlns:m="http://servidor/ConsultaClientes">
                  <m0:mensaje>
                        <m0:idcliente>C123452</m0:idcliente>
                  </m0:mensaje>
            </m:ConsultaClientes>
      </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
 
/*El contenido que enviamos es XML: */
    UTL_HTTP.set_header(req, 'Content-Type', 'text/xml');
 
/*Establecemos el SOAPAction a invocar: */
    UTL_HTTP.set_header(req, 'SOAPAction', '"rpc/http://servidor/ConsultaClientes"');
 
/*Indicamos en el header el tamańo del mensaje enviado: */
    UTL_HTTP.set_header(req, 'Content-Length', LENGTH(xml));
 
/*Escribimos el body del request */
    UTL_HTTP.write_text(req, xml);
 
/*Obtenemos la respuesta */
    resp := UTL_HTTP.get_response(req);
 
/*Cargamos en la variable respVal la devolución del servidor */
    UTL_HTTP.read_text(resp, respVal);
 
/*Finalizamos la conexión HTTP */
    UTL_HTTP.end_response(resp);
 
EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(resp);
END;
 
/

Conclusion

De esta manera rápida pudimos invocar un Web Service remoto utilizando PL/SQL, a partir de allí es posible convertir lo recibido en la variable respVal a un XMLTYPE para un mejor manejo.

Espero que les haya servido!!