使用输出参数调用Oracle存储的proc [英] Call Oracle stored proc with output parameter

问题描述

我正在使用SSIS 2008，并且在调用具有输出参数的Oracle存储过程时遇到问题.

I'm working with SSIS 2008 and am having a problem calling an Oracle stored procedure that has an output parameter.

我这样调用SqlPlus中的存储过程:

I call the stored procedure in SqlPlus like this:

var vresult number;
exec my_stored_procedure(:vresult);
print vresult;

该语句正常工作，我得到了所需的输出.我正在尝试在SSIS中执行类似的操作，但是我需要重复执行此操作，也许是在ForEach或脚本中，以通过调用存储过程的结果来更新临时结果集(存储过程生成一个数字，我需要将该数字添加到只包含一些状态信息的结果集中的每一行).

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.

我还尝试了以下方法:

1. 如何在使用Oracle OLE DB提供程序时解决SQL查询参数映射问题?

使用OLEDB命令在Oracle中更新一行(SSIS )

Oracle变量

问题的症结似乎在于存储过程的输出参数.

The crux of the problem seems to be the stored procedure's output parameter.

我尝试过使用用于OLE DB的Oracle提供程序.有什么想法吗?

I have tried using the the Oracle Provider for OLE DB. Any ideas?

推荐答案

我想出了一个可行的解决方案:

I came up with a solution that works:

• 使用声明"和结束"结构
• 结合立即执行"
• 在exec立即执行的末尾添加"using"语句以注入变量

因此实现此目的的脚本可能看起来像这样:

So a script that implements this might look something like this:

declare
myVar number;
myStatement varchar2(50);
begin
    myStatement:='exec myProc(:1)';
    execute immediate myStatement using output myVar;
end;

将此脚本粘贴到Execute SQL任务中，设置任务的属性即可使用！

Paste this script into an Execute SQL task, set the task's properties and it works!

我是Oracle的新手，但看起来:1表示法是该变量的占位符.您也可以使用sqlplus进行测试-只需将代码保存在文件中，然后在命令行上使用@选项启动sqlplus.

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.

唯一的问题:我无法获取SSIS中使用的变量的值，但这是另一个问题.

The only problem: I can't get value of the variable for use in SSIS, but that's another problem.

这篇关于使用输出参数调用Oracle存储的proc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！