SqlNonQuery stored procedure - how to retrieve value from out parameter

Permanently deleted user

October 10, 2013 21:39

I have a SqlNonQuery activity that calls a stored procedure. The stored procedure takes some I n parameters and generate some out parameters. If it runs successfully, it inserts a new record into a table. I want to display one of the out parameters WORK_REQUEST_NO_OUT to show the newly created work request number if the stored procedure is created successfully.  It seems the stored procedure does runs successfully as i run the work flow, i see new record inserted into the destination table .   However, the variable work_request_no which is the value for out parameter WORK_REQUEST_NO_OUT  shows empty on the screen in alert activity.   Here is what I have in the SqlNon query command:

 

DECLARE 

 

  PLANT_IN VARCHAR2(3);

 

  DESCRIPTION_IN VARCHAR2(2000);

 

  ASSET_LIST_IN VARCHAR2(32767);

 

  REQUESTOR_IN VARCHAR2(30);

 

  WORK_REQUEST_NO_OUT VARCHAR2(7);

 

  ERROR_NO_OUT VARCHAR2(32767);

 

  ERROR_MESSAGE_OUT VARCHAR2(32767);

 

BEGIN 

 

  PLANT_IN := '01';

 

  DESCRIPTION_IN := :DESCRIPTION_IN;

 

  ASSET_LIST_IN := :ASSET_LIST_IN;

 

  REQUESTOR_IN := :REQUESTOR_IN;

 

  WORK_REQUEST_NO_OUT := :WORK_REQUEST_NO_OUT;

 

  ERROR_NO_OUT :=:ERROR_NO_OUT;

 

  ERROR_MESSAGE_OUT := :ERROR_MESSAGE_OUT;

 

  SYNERGEN.SDBP_GIS_CREATE_WR (PLANT_IN, DESCRIPTION_IN, ASSET_LIST_IN, REQUESTOR_IN, WORK_REQUEST_NO_OUT, ERROR_NO_OUT, ERROR_MESSAGE_OUT);

 

  COMMIT; 

 

END;

 

 

I have a variable numRowInserted assigned in SqlNonQuery's Rows Affected column:

In SqlNonQuery's parameters session I have:

/customer/servlet/servlet.FileDownload?file=00P6000000e88XTEAY

 

if numRowInserted = 1, the alert will display work request no, if not, an alert will show the error message:

/customer/servlet/servlet.FileDownload?file=00P6000000eltr3EAA

 

 Although the alert shows "work request created, work request number" message, the text is empty.  Any help is appreciated!

 

Regards,

 

Jin

 

0

• 

  Ryan Cooney

  October 10, 2013 22:37

  Hi Jin,

  Unfortunately the current version of the activity does not populate out paramters. We have recently added support for this during development so it will be available in a future release.

  --Ryan

  0
• 

  Permanently deleted user

  October 10, 2013 22:49

  Hi, Jin,

  If you can't wait until the next lease of GE/GVS, you can achieve this by two step SQL Activities call using 'Oracle Sequence'.

  (1) Define your Oracle Sequence which generates auto-incremented numbers which you can use as the primary key of your real db record to insert.

  (2) Run SQL Activity to get the next Sequence value like 'SELECT [Your Sequence name].NEXTVAL FROM dual'

  (3) then run  the second SQL Activity to insert with next Sequence value to your table.

  That is what I am doing for the project I am working on.

  As Ryan says, you can do this with one step with next release of GE though.

  0
• 

  Permanently deleted user

  October 10, 2013 23:18

  Munwhan,

  Thanks for the suggestion, that will be a good work around before the next release of coming out. Appreciate it!

   

  Regards,

   

  Jin

  0

Please sign in to leave a comment.