Ejecutar un Stored Procedure de Oracle desde Data Factory

En el momento de la publicación de este tutorial, Data Factory no tiene un conector que permita ejecutar un stored procedure en una base de datos oracle. En este tutorial mostraremos cómo a través de una actividad lookup de un pipeline de Data Factory lograremos ejecutar un Stored Procedure o instrucciones DML en Oracle.

Prerrequisitos

• Crear un recurso de Data Factory en el Portal de Azure

Pasos

1. Crear una actividad Lookup.
De acuerdo con la documentación oficial una actividad Lookup nos permite recuperar un dataset de cualquiera de los orígenes de datos compatibles con Azure Data Factory.
Como podemos observar la actividad está diseñada para consultar una tabla o la ejecución de una sentencia SELECT de oracle.

En Query podemos especificar cualquier instrucción SQL, sin embargo, si la sentencia no es un SELECT válido, Data Factory intentará ejecutar las instrucciones en la base de datos Oracle pero al no recibir el resultado de una consulta, generará un error así haya ejecutado correctamente las instrucciones del query en el servidor de la base de datos.
2. Crear una sentencia SELECT sobre la tabla DUAL
Debido a que los Stored Procedures no se pueden ejecutar dentro de una sentencia SELECT. Debemos crear una función en Oracle que se encargue de ejecutar el procedimiento.
2.1 Ejecución de una función Oracle desde el query de una actividad de Lookup.
Antes de ejecutar el procedimiento almacenado, revisemos el proceso de ejecutar una función en oracle desde Data Factory empleando la tabla DUAL.
Para esto, hemos creado una función en Oracle que cuenta el número de registros de la tabla EMPLEADOS.

--Tabla
CREATE TABLE empleados(
   emp_id NUMBER(9),
   nombre VARCHAR2(100),
   CONSTRAINT empleados_pk PRIMARY KEY(emp_id),
);
--Función
create or replace function func_contar_empleados
return integer 
is
   n integer:= 0;
begin
  for r in (select emp_id from empleados) loop
        n := n + 1;
  end loop;
  return n;
end;

En Data Factory configuramos el query agregando la siguiente consulta:

SELECT  func_contar_empleados() FROM DUAL;

Al ejecutar el pipeline podemos verificar que la consulta se ejecutó correctamente.

2.2 Ejecución de Stored Procedure a través de una Función Oracle.
Un Stored Procedure de Oracle es un bloque de código PL/SQL que comunmente va a estar asociado a instrucciones DML.
Para ejecutar el Sotred Procedure hemos creado una función en Oracle que recibe los parámetros y en su implementación hace el llamado al Stored Procedure.

create or replace procedure insert_empleado (numero in NUMBER, nombre in VARCHAR2) is
 begin
    INSERT INTO empleados (emp_id, nombre)
    Values(numero, nombre);
COMMIT;
end;

create or replace function funcinsert_empleado (numero in NUMBER, nombre in VARCHAR2)
return VARCHAR2 
is
begin
  insert_empleado (numero, nombre);
  return 'done';
end;

Siguiendo los pasos de ejecución de una función en Oracle usando la tabla DUAL, tendríamos la siguiente consulta:

SELECT  funcinsert_empleado ('1', 'Roger Federer') 
FROM DUAL;

Sin embargo, cuando intentamos ejecutar la instrucción obtenemos un error de Oracle que nos notifica que no podemos ejecutar operaciones DML dentro de una instrucción SELECT.

2.3 Ejecutar una función Oracle como una transacción Autónoma.
Debido a que Oracle no permite por defecto la ejecución de instrucciones DML, debemos especificar que la ejecución de la función se realizará como una TRANSACCIÓN AUTONOMA.
Las Transacciones autónomas van acompañadas de la cláusula AUTONOMOUS_TRANSACTION Pragma. Esta directiva cambia la manera como se ejecutan los subprogramas en una transacción.

create or replace function funcinsert_empleado (numero in NUMBER, nombre in VARCHAR2)
return VARCHAR2 
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert_empleado (numero, nombre);
  return 'done';
end;

La solución aquí planteada permite ejecutar la función que ejecuta el procedimiento almacenado garantizando una transacción autónoma en la ejecución de las instrucciones DML.

SELECT  funcinsert_empleado ('1', 'Roger Federer') 
FROM DUAL;

Con estos ajustes, Data Factory ya puede ejecutar la sentencia SELECT.

Tenga en cuenta que, al ejecutar la consulta desde Data Factory, el id del empleado que está pasando como parámetro no se encuentre registrado en la tabla EMPLEADOS, ya que esto generaría un error de primary key.

Conclusiones

1. Este tutorial muestra cómo usar la actividad de Lookup de Data Factory para ejecutar un Stored Procedure o instrucciones DML en una base de datos de Oracle.
2. Si tiene varios Stored Procedures podría definir condicionales dentro de la función que se llama desde la instrucción SELECT y dependiendo de un parámetro, determinar qué Stored Procedure ejecutar.

¡Gracias por leer!

Si tienes una duda, no dudes en escribir.
maritzag.