I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:
(p_from_dttm OUT varchar2,
p_error_code OUT number,
p_error_text OUT varchar2,
p_proc_name OUT varchar2);
Could you please be more specific? I tried the following:
1) The stored proc spec is as follows:
Procedure testing(myDate OUT varchar2);
2) Created a Data Flow Task.
3) Created 2 variables called myStoredProc & myDate at the package level.
4) In the value for myDate variable i set it to myDate.
5) In the properties for myStoredProc variable i changed the EvaluateAsExpression property to True.
6) In the expression for myStoredProc variable i have the following:
"declare myDate varchar2(50); begin sa.testing(" + @[User::myDate] + "); end;"
7) Now inside my Data Flow Task i have a Ole Db DataSource connection set to Native OLE DB\Microsoft Ole Db
Provider for Oracle.
8) Data Access Mode set to SQL Command from Variable.
9) The value of the Variable Name is User::myStoredProc.
10) Now when i hit preview i get the following error Message.
No disconnected record set is available for the specified SQL statement.
I am not sure what's wrong here. Could someone help me??????????
Thanks guys. Now i am able to execute it successfully. But i am getting some junk characters in the Output parameters. Here is the stored proc definition:
createor replace procedure testing(myDate OUT varchar2)
myDate := 'hey';
This should return 'hey' but i am getting this:
I am experiencing the same problem as Sun Moon.
My Oracle procedure appears to run successfully in SSIS as an "Execute SQL Task" but when I check the values of the variables set to the procedure's outputs, I see junk characters.
e.g. I expected to see User::ProductType set to:
but instead it has been set to :
Does anyone have a solution?
Thanks in advance.
Hi Sun Moon,
How do you get the value OUT parameter from Oracle?
In Execute SQL Task, I tried to set:
Resultset = Single row, Result Name = 1, Variable Name = User::output (String datatype)
But it returns the following error:
Error: No result rowset is associated with execution of this query.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "output"
I found the fix,
In SSIS I used Execute SQL TASK.
1. Ensure that the RESULTSET is set to NONE
2. In the SQL STATEMENT call your Oracle stored proc like the belowDECLARE
CALL_MY_SProc_Oracle ( Param_INPUT, Param_output);
? := Param_output ;
3) in the Parameter Mapping section, Map the SSIS input variable of your choice to the Parameter "0" with Direction "input" so that you can pass the value of your SSIS variable to the Param_input
Then Map the SSIS output variable of your choice to the Parameter "1" with Direction "Output" so that you can pass the value of the Oracle output parameter (Param_output) into the SSIS variable