Welcome Guest! Log in


The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

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

27 Oct 2016 13:22 #1 by sanjeevgupta

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,
  sp_run_validation(${~/batch_id}$, ${~/result}$);
The actual call made to the database is:
  sp_run_validation(100, );
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?

03 Nov 2016 10:40 #2 by Thomas BLETON
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.