Question

Results from Oracle stored procedure

  • 2 April 2014
  • 3 replies
  • 19 views
C
Badge +1
Hi,

 

Is there a way that I can get hold of and use the results from calling a stored procedure in a SQL Executor?

 

 

My procedure seems to run fine, but I haven't been able to get anything out of the Result output port of the SQLExecutor.  The stored proc queries a mapping table and returns either an existing id or a newly-created one (or an error).

 

 

My SQL looks something like this:

 

 

declare

 

  ID number;

 

  STATUS number;

 

  ERRMSG varchar2(128);

 

begin

 

  schema.get_new_id(@Value(obj_id),'@Value(table_name)',ID,STATUS,ERRMSG);

 

end;

 

 

Again, it seems to run fine, but I would like to use the output variables ID and STATUS on the feature farther along in the workflow (e.g. to set an attribute value). 

 

Any assistance is appreciated.

 

Thanks!

 

3 replies

david_r
Rank
Userlevel 4
Hi,

 

 

does schema.get_new_id() have any return values? This is necessary. You cannot have return values in the anonymous block in your SQLExecutor (not supported by Oracle), so the function get_new_id() must RETURN the values ID and STATUS to you. Declaring the results as OUT isn't enough here, they must be explicitely returned, i.e. get_new_id() must be a function, not a procedure.

 

 

If you can modify the declaration of get_new_id() to RETURN the results, or eventually create a PL/SQL wrapper function around it to do it, you can then do something like this in your SQLExecutor:

 

 

select ID, STATUS, ERRMSG

 

from table(schema.get_new_id(@Value(obj_id),'@Value(table_name)'))

 

 

You can then expose the resulting ID, STATUS and ERRMSG as feature attributes in the SQLExecutor, as usual.

 

 

See also the example here (http://stackoverflow.com/a/9484311/370301).

 

 

David

 

 

david_r
Rank
Userlevel 4

Hi,

 

 

does schema.get_new_id() have any return values? This is necessary. You cannot have return values in the anonymous block in your SQLExecutor (not supported by Oracle), so the function get_new_id() must RETURN the values ID and STATUS to you. Declaring the results as OUT isn't enough here, they must be explicitely returned, i.e. get_new_id() must be a function, not a procedure.

 

 

If you can modify the declaration of get_new_id() to RETURN the results, or eventually create a PL/SQL wrapper function around it to do it, you can then do something like this in your SQLExecutor:

 

 

select ID, STATUS, ERRMSG
from table(schema.get_new_id(@Value(obj_id),'@Value(table_name)'))

 

You can then expose the resulting ID, STATUS and ERRMSG as feature attributes in the SQLExecutor, as usual.

 

 

See also the example here http://stackoverflow.com/a/9484311/370301

 

 

David

 

C
Badge +1
David - thanks for your response!  I don't have it solved yet, but this is helpful.

 

Reply


Community Stats

28,707
Posts
109,479
Replies
36,552
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.