我正在使用 SSIS 2008,并且在调用具有输出参数的 Oracle 存储过程时遇到问题。
我在 SqlPlus 中调用存储过程,如下所示:
var vresult number;
exec my_stored_procedure(:vresult);
print vresult;
语句起作用,我得到了我需要的输出。我试图在 SSIS 中做类似的事情,但我需要重复执行此操作,也许在 ForEach 或脚本中使用调用存储过程的结果更新临时结果集(存储过程生成一个数字,我需要将该数字添加到仅保存一些状态信息的结果集中的每一行)。
我尝试了很多不同的方法,但总是以“无效声明”或类似的错误告终。
我还尝试了以下方法:
-
如何在使用 Oracle OLE DB 时解决 SQL 查询参数映射问题
-
使用 OLEDB 命令更新 oracle 中的行(SSIS)
-
Oracle变量
问题的症结似乎是存储过程的输出参数。
我尝试过使用 Oracle Provider for OLE DB。有什么想法吗?
I'm working with SSIS 2008 and am having a problem calling an Oracle stored procedure that has an output parameter.
I call the stored procedure in SqlPlus like this:
var vresult number;
exec my_stored_procedure(:vresult);
print vresult;
The statements work and I get the output I need. I am trying to do something similar in SSIS, yet I need to do this repeatedly, maybe in a ForEach or a script to update a temporary result set with the result of calling the stored procedure (the stored procedure generates a number, and I need to add that number to each row in a result set which just holds some state information).
I have tried a lot of different approaches and always end up with 'invalid statement' or similar errors.
I have also tried the following approaches:
-
How to resolve SQL query parameters mapping issues while using Oracle OLE DB provider?
-
Update a row in oracle using OLEDB command(SSIS)
-
Oracle variables
The crux of the problem seems to be the stored procedure's output parameter.
I have tried using the the Oracle Provider for OLE DB. Any ideas?
发布评论
评论(4)
如果您尝试调用 Oracle PLSQL 中的存储过程,此链接非常简短。
http://plsql-tutorial.com/plsql-passing-parameters-procedure -function.htm
如果您使用 Java 工作。语句对象
java.sql.CallableStatement ps;
ps.registerOutParameter(parameterIndex, sqlType);
同样,.Net 或任何其他平台也必须具有相同的信念。希望如此。:)
If you are trying to invoke The stored Procedure in Oracle PLSQL this Link is very brief.
http://plsql-tutorial.com/plsql-passing-parameters-procedure-function.htm
If you are Working in Java then. The Statement Object
java.sql.CallableStatement ps;
ps.registerOutParameter(parameterIndex, sqlType);
Similarly .Net or Any Other Platform must will have the same Convictions. Hope so.:)
我想出了一个可行的解决方案:
因此实现此功能的脚本可能看起来有些东西像这样:
将此脚本粘贴到执行 SQL 任务中,设置任务的属性,它就可以工作了!
我是 Oracle 新手,但看起来 :1 表示法是变量的占位符。您也可以使用 sqlplus 进行测试 - 只需将代码保存在文件中并使用命令行上的 @ 选项启动 sqlplus。
唯一的问题:我无法获取在 SSIS 中使用的变量值,但这是另一个问题。
I came up with a solution that works:
So a script that implements this might look something like this:
Paste this script into an Execute SQL task, set the task's properties and it works!
I'm new to Oracle but it looks like the :1 notation is a place-holder for the variable. You can test this using sqlplus too - just save the code in a file and start sqlplus using the @ option on the command line.
The only problem: I can't get value of the variable for use in SSIS, but that's another problem.
检查此帖子:从 SQL Server Integration Services 运行 Oracle 包
http://www.mssqltips.com/sqlservertip/2724/run-an-oracle-package-from-sql-server-integration-services/
问候
check tis post: Run an Oracle package from SQL Server Integration Services
http://www.mssqltips.com/sqlservertip/2724/run-an-oracle-package-from-sql-server-integration-services/
regards
你快到了。为了从 SSIS 中的 Oracle 存储过程检索输出参数的值,这对我有用
在“执行 SQL”任务中,将其粘贴到“SQL 语句”框中
在“参数映射”中,确保将 SSIS 变量映射到此通过将方向设置为“Output”并将参数名称设置为“0”(如果它是第一个参数)来输出存储过程的输出
PS:确保Oracle输出变量数据类型与您的SSIS变量匹配
谢谢
梅祖埃
You are almost there. In order to retrieve the value of the output parameter from the Oracle stored procedure in SSIS, here is what worked for me
In the Execute SQL task, paste this in the SQL statement box
In the Parameter Mapping, ensure to map your SSIS variable to this output of your stored procedure by setting the direction to "Output" and parameter name as "0" (if it is the first parameter)
PS: ensure the Oracle output variable datatypes match your SSIS variables
Thanks
Mezue