如何从Azure Data Factory v2调用Oracle存储过程 [英] How to call Oracle stored procedure from azure data factory v2

问题描述

我的要求是将数据从Oracle复制到SQL Server.从Oracle数据库复制之前，我需要使用具有一定逻辑性的过程来更新Oracle表.

My requirement is copy data from Oracle to SQL Server. Before copying from Oracle database, I need to update the Oracle table using procedure which has some logic.

如何从Azure数据工厂执行Oracle存储过程?

How do I execute Oracle stored procedure from Azure datafactory?

我将此线程引用

如果我在 preCopy 脚本中使用 EXECUTE PROC_NAME(PARAM); ，则失败，并出现以下错误

if I use EXECUTE PROC_NAME (PARAM); in preCopy script it's failing with following error

Failure happened on 'Source' side. 
ErrorCode=UserErrorOdbcOperationFailed,
Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException
Message=ERROR [42000] [Microsoft][ODBC Oracle Wire Protocol driver]
[Oracle]ORA-00900: invalid SQL statement
Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,
Type=System.Data.Odbc.OdbcException
Message=ERROR [42000] [Microsoft][ODBC Oracle Wire Protocol driver]
[Oracle]ORA-00900: invalid SQL statement,Source=msora28.dll

有人可以帮忙吗?

注意:我使用的是数据工厂的自托管运行时环境谢谢！

Note: I am using self-hosted runtime environment for data factory thanks!!

推荐答案

我使用了Lookup Activity和DUAL TABLE的SELECT语句.由于存储过程无法从语句SELECT调用.我创建了一个oracle函数，该函数调用了存储过程.该函数返回一个值，并且查找活动将接收到该值.定义函数时，必须添加语句PRAGMA AUTONOMOUS_TRANSACTION.这是因为默认情况下Oracle不允许使用SELECT语句执行DML指令.然后，您需要定义存储过程中的DML指令将是自主事务.

I used a Lookup Activity and a SELECT statement of DUAL TABLE. Due to the stored procedures can not be call from a statement SELECT. I created an oracle function and the function calls the stored procedure. The function returns a value and this value is received by the lookup activity. When you define the function, you have to add the statement PRAGMA AUTONOMOUS_TRANSACTION. This is because Oracle does not allow to execute DML instructions with a SELECT statement by default. Then, you need to define that DML instructions in the Stored Procedure will be an autonomous transaction.

--Tabla
CREATE TABLE empleados(
   emp_id NUMBER(9),
   nombre VARCHAR2(100),
   CONSTRAINT empleados_pk PRIMARY KEY(emp_id),
);

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
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert_empleado (numero, nombre);
  return 'done';
end;
--statement in query of lookup
SELECT  funcinsert_empleado ('1', 'Roger Federer') 
FROM DUAL;

查找示例

这是西班牙语的示例. https://dev.to/maritzag/ejecutar-un-procedure-de-oracle-desde-data-factory-2jcp

This is example in Spanish. https://dev.to/maritzag/ejecutar-un-stored-procedure-de-oracle-desde-data-factory-2jcp

这篇关于如何从Azure Data Factory v2调用Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！