Welcome Guest! Log in

Topic-icon Question Capturing out parameter value from Oracle Stored Procedure

More
27 Oct 2016 13:22 #1 by sanjeevgupta
sanjeevgupta created the topic: Capturing out parameter value from Oracle Stored Procedure
Hi,

In one of my development work, I have to capture the out parameter value from a Oracle stored procedure call into one of the stambia variable. The stored procedure looks like:
sp_run_validation(p_batch_id in number, p_result out number);

I tried to call this stored procedure by passing nearly all types of variables(Metadata Vairable, Parameter Component, sqltoparameter variable etc) available in stambia designer but every time instead of binding the return value to the variable, it just puts the value of that variable in the stored procedure call.

Eg: I call the stored procedure like this,
Begin
  sp_run_validation(${~/batch_id}$, ${~/result}$);
end;
The actual call made to the database is:
Begin
  sp_run_validation(100, );
end;
Since the initial value of ${~/result}$ is null, the second parameter is replaced by null.

Is there any method through which I can bind the return value form the out parameter of Oracle stored procedure to stambia variable?

Regards.

Please Log in or Create an account to join the conversation.

More
03 Nov 2016 10:40 #2 by Thomas BLETON
Thomas BLETON replied the topic: Capturing out parameter value from Oracle Stored Procedure
Hi,
Calling stored procedure can be done from Stambia processes.
But you cannot retrieve the OUT parameters. As far as I know, OUT parameters can be used in the PL/SQL context only.

If you need to get these OUT params out of PL/SQL, you need to create a function and invoke it from a SQL statement.
This function will call your stored proc and return the p_result value.

Alternative: writing an anonymous PL/SQL block that writes the OUT params to a table, and then read them from the table with a SqlToParameters. I don't really like this solution because it requires a table.

Feel free to share any other idea or solution that you may find :)
I hope this helps.

Please Log in or Create an account to join the conversation.