Ejecutando DML’s y Stored Procedures en Oracle con TPT.

TPT, mediante su ODBC OPERATOR, permite acceder a otras bases de datos (Oracle, SqlServer…) generalmente para extraer datos y moverlos y normalmente para cargarlos en Teradata.

ODBC OPERATOR es un operador de tipo PRODUCER. Esto es, lee datos de una fuente (ODBC) y los escribe en un “data stream” para que los utilice un CONSUMER. Los operadores así conectados deben compartir un mismo SCHEMA. Así, se puede mediante un ODBC OPERATOR leer el contenido de una tabla Oracle y cargarlo en una tabla Teradata sin necesidad de conjugar exportaciones a fichero (o pipe) desde sqlplus con cargas posteriores a Teradata (con Fastload, Multiload, TPump, bteq o incluso TPT).

El procedimiento es bastante sencillo y se pueden encontrar muchos ejemplos descriptivos de cómo hacerlo. Hay que indicar que Teradata sólo soporta el funcionamiento de los ODBC OPERATORs utilizando los “drivers” ODBC de DataDirect. Hay quien afirma haber conseguido que funcionen usando “drivers” de Oracle o MicroSoft, pero yo sólo tuve éxito mediante los citados “drivers” de DataDirect.

Por supuesto, el primer paso es tener los “drivers” instalados y el segundo es configurar un origen de datos ODBC (en nuestro caso a Oracle 11gR2).

Una vez hecho eso se pueden efectuar pruebas de extracción de datos Oracle mediante el ODBC OPERATOR sólo para verificar conectividades y asegurarse de que todo funciona correctamente.

Pero ahora viene lo bueno: ¿Podríamos ejecutar mediante TPT sentencias DML (INSERT, UPDATE, DELETE) o incluso ejecutar procedimientos almacenados (stored procedures)?

La respuesta corta es NO. Pero la respuesta larga es SÍ.

En efecto, el ODBC OPERATOR sólo permite sentencias SELECT y, como es un PRODUCER, necesita de un CONSUMER que lea datos extraídos, por lo que, en teoría, no es posible ejecutar DMLs.

Pero la respuesta larga es SÍ. ¿Cómo? Pues haciendo un poco de trampa.

Lo que viene a continuación es un ejemplo válido sólo a efectos didácticos, y únicamente pretende mostrar cómo se podría conseguir el objetivo buscado y quizá indicar un camino de inicio para procesos más complejos.

Nos vamos a valer del hecho de que Oracle permite definir funciones con código subyacente. Si conseguimos incluir una llamada a una función de este tipo tendremos la puerta abierta para ejecutar DMLs y SPs:

Así pues si en Oracle generamos una función algo así como:

CREATE OR REPLACE FUNCTION CARLOS.PRUEBA_INSERT_3(iDesde NUMBER, 
   iHasta NUMBER)
RETURN NUMBER
AS
BEGIN
   FOR i IN iDesde..iHasta LOOP
      INSERT INTO CARLOS.PRUEBA03(ID_N, C_TXT) VALUES (i, TO_CHAR(i));
   END LOOP;
   COMMIT;
   RETURN(0);
EXCEPTION
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE (SQLERRM);   
      RETURN(-1);
END;

Entonces, podremos definir un TPT job que llame a dicha función en un SELECT. Como necesitamos un CONSUMER para recoger el resultado del SELECT, utilizaremos un simple $FILE_WRITER que escriba el retorno de la función en un fichero:

DEFINE JOB ODBC_DML
DESCRIPTION 'ODBC DML'
(
   DEFINE SCHEMA Oracle_Return_Schema (
      id_n  VARCHAR(11)                
   );

   DEFINE OPERATOR ODBC01
   DESCRIPTION 'Teradata Parallel Transporter ODBC Operator'
   TYPE ODBC
   SCHEMA Oracle_Return_Schema
   ATTRIBUTES (
      VARCHAR DSNName = 'ORA11G_CENTOS6DD',  /* DataDirect ODBC Driver */
      VARCHAR UserName = @UserName,
      VARCHAR UserPassword = @UserPassword,
      VARCHAR TraceLevel = 'All',
      VARCHAR TruncateData = 'Yes',
      VARCHAR SelectStmt = 'SELECT CARLOS.PRUEBA_INSERT_3(1,3) FROM DUAL;'
   );

   DEFINE OPERATOR FILE_WRITER_1
   TYPE DATACONNECTOR CONSUMER
   SCHEMA * 
   ATTRIBUTES (
      VARCHAR FileName = 'Prueba_Insert_Oracle.csv',
      VARCHAR Format = 'DELIMITED',
      VARCHAR TextDelimiter=';',
      VARCHAR IndicatorMode = 'N',
      VARCHAR OpenMode = 'Write'
   );

   STEP EXPORTAR (   
      APPLY TO OPERATOR (FILE_WRITER_1)
         SELECT * FROM OPERATOR (ODBC01);
   );
   
);

Esto debería valer. Pero primero, vamos a verificar el funcionamiento de la función con sqlplus:

CARLOS@ORA11GR2> SET SERVEROUTPUT ON;
CARLOS@ORA11GR2> SELECT CARLOS.PRUEBA_INSERT_3 FROM DUAL;

PRUEBA_INSERT_3
---------------
             -1

ORA-14551: cannot perform a DML operation inside a query
CARLOS@ORA11GR2>     

¡Es verdad! ¡No podemos hacer DML en un SELECT! Pero lo podemos soslayar utilizando una transacción autónoma (PRAGMA AUTONOMOUS_TRANSACTION). Esto, por supuesto, tiene importantes efectos en el proceso, pero nos permite hacer:

CARLOS@ORA11GR2> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION CARLOS.PRUEBA_INSERT_3(iDesde NUMBER, 
  2     iHasta NUMBER)
  3  RETURN NUMBER
  4  AS
  5  PRAGMA AUTONOMOUS_TRANSACTION;
  6  BEGIN
  7   FOR i IN iDesde..iHasta LOOP
  8      INSERT INTO CARLOS.PRUEBA03(ID_N, C_TXT) VALUES (i, TO_CHAR(i));
  9   END LOOP;
 10   COMMIT;
 11   RETURN(0);
 12  EXCEPTION
 13   WHEN OTHERS THEN
 14      RETURN(-1);
 15* END;
CARLOS@ORA11GR2> /

Function created.

CARLOS@ORA11GR2> SELECT PRUEBA_INSERT_3(1, 3) FROM DUAL;

PRUEBA_INSERT_3(1,3)
--------------------
                   0

CARLOS@ORA11GR2> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 1
         2 2
         3 3

CARLOS@ORA11GR2> 

Y ahora podemos ejecutar el TPT (sustituyendo ‘SELECT CARLOS.PRUEBA_INSERT_3(1,3) FROM DUAL;’ por ‘SELECT CARLOS.PRUEBA_INSERT_3(4,6) FROM DUAL;’ ):

C:\Carlos\TPT\TPT_Oracle_To_Teradata>tbuild -f Prueba_Insert_Oracle.tpt
Teradata Parallel Transporter Version 14.10.00.11 64-Bit
Job log: C:\Program Files\Teradata\client\14.10\Teradata Parallel Transporter/logs/carlos-57.out
Job id is carlos-57, running on CARLOS03
Teradata Parallel Transporter DataConnector Operator Version 14.10.00.11
FILE_WRITER_1: Instance 1 directing private log report to 'dtacop-carlos-6672-1'.
FILE_WRITER_1: DataConnector Consumer operator Instances: 1
Teradata Parallel Transporter ODBC Operator Version 14.10.00.11
ODBC01: private log not specified
FILE_WRITER_1: ECI operator ID: 'FILE_WRITER_1-6672'
FILE_WRITER_1: Operator instance 1 processing file 'C:\Trazas\Prueba_Insert_Oracle.csv'.
ODBC01: connecting sessions
ODBC01: sending SELECT request
ODBC01: data retrieval complete
ODBC01: Total Rows Exported:  1
FILE_WRITER_1: Total files processed: 1.
ODBC01: disconnecting sessions
ODBC01: Total processor time used = '0.0312002 Second(s)'
ODBC01: Start : Wed Apr 06 19:26:48 2016
ODBC01: End   : Wed Apr 06 19:26:53 2016
Job step EXPORTAR completed successfully
Job carlos completed successfully
Job start: Wed Apr 06 19:26:45 2016
Job end:   Wed Apr 06 19:26:53 2016

C:\Carlos\TPT\TPT_Oracle_To_Teradata>

Y en Oracle:

CARLOS@ORA11GR2> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 1
         2 2
         3 3
         4 4
         5 5
         6 6

6 rows selected.

CARLOS@ORA11GR2> 

Con lo que hemos conseguido ejecutar un INSERT en Oracle… haciendo trampas, claro.

Pero lo bueno es que podríamos ejecutar un procedimiento almacenado también. Basta con crear una función envoltorio que llame a un procedimiento almacenado:

CREATE OR REPLACE PROCEDURE CARLOS.PRUEBA_INSERT_4_SP( iDesde NUMBER, 
   iHasta NUMBER)
AS
BEGIN
   FOR i IN iDesde..iHasta LOOP
      INSERT INTO CARLOS.PRUEBA03(ID_N, C_TXT) VALUES (i, TO_CHAR(i));
   END LOOP;
END;


CREATE OR REPLACE FUNCTION CARLOS.PRUEBA_INSERT_4_FN( iDesde NUMBER, 
   iHasta NUMBER)
RETURN NUMBER
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   CARLOS.PRUEBA_INSERT_4_SP( iDesde, iHasta);
   COMMIT;
   RETURN(0);
EXCEPTION
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
      RETURN(-1);
END;

Si modificamos el “SelectStmt” del script de TPT:

VARCHAR SelectStmt = 'SELECT CARLOS.PRUEBA_INSERT_4_FN (7,20) FROM DUAL;'

Y ejecutamos:

C:\Carlos\TPT\TPT_Oracle_To_Teradata>tbuild -f Prueba_Insert_Oracle.tpt
Teradata Parallel Transporter Version 14.10.00.11 64-Bit
Job log: C:\Program Files\Teradata\client\14.10\Teradata Parallel Transporter/logs/carlos-58.out
Job id is carlos-58, running on CARLOS03
Teradata Parallel Transporter DataConnector Operator Version 14.10.00.11
FILE_WRITER_1: Instance 1 directing private log report to 'dtacop-carlos-6892-1'.
FILE_WRITER_1: DataConnector Consumer operator Instances: 1
Teradata Parallel Transporter ODBC Operator Version 14.10.00.11
ODBC01: private log not specified
FILE_WRITER_1: ECI operator ID: 'FILE_WRITER_1-6892'
FILE_WRITER_1: Operator instance 1 processing file 'C:\Trazas\Prueba_Insert_Oracle.csv'.
ODBC01: connecting sessions
ODBC01: sending SELECT request
ODBC01: data retrieval complete
ODBC01: Total Rows Exported:  1
FILE_WRITER_1: Total files processed: 1.
ODBC01: disconnecting sessions
ODBC01: Total processor time used = '0.0156001 Second(s)'
ODBC01: Start : Wed Apr 06 19:45:35 2016
ODBC01: End   : Wed Apr 06 19:45:41 2016
Job step EXPORTAR completed successfully
Job carlos completed successfully
Job start: Wed Apr 06 19:45:31 2016
Job end:   Wed Apr 06 19:45:41 2016

C:\Carlos\TPT\TPT_Oracle_To_Teradata>

Vemos que:

CARLOS@ORA11GR2> SELECT * FROM PRUEBA03;

      ID_N C_TXT
---------- ----------
         1 1
         2 2
         3 3
         4 4
         5 5
         6 6
         7 7
         8 8
         9 9
        10 10
        11 11
        12 12
        13 13
        14 14
        15 15
        16 16
        17 17
        18 18
        19 19
        20 20

20 rows selected.

CARLOS@ORA11GR2> 

Y así podríamos ejecutar DMLs y stored procedures usando TPT.

The rest is up to you…

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: