Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

ksc_run_plsql_procedure: getting output parameters

Highlighted
Zuria
Occasional Contributor

ksc_run_plsql_procedure: getting output parameters

Hi,

Does anybody know how to get the ouput parameters when using the special command ksc_run_plsql_procedure?

I have defined this procedure in a database package:
PROCEDURE entregaCierreTecnico(p_request_id IN NUMBER, o_message OUT VARCHAR2)

From a workflow step, I execute the following command:
ksc_run_plsql_procedure TDE_UTILITIES.EntregaCierreTecnico
p_request_id.INTEGER.IN=[REQ.REQUEST_ID]
o_message.VARCHAR.OUT
ksc_end_plsql_parameters

When I execute it, the request log displays the following message:

Executing PL/SQL procedure TDE_UTILITIES.EntregaCierreTecnico
Done executing PL/SQL procedure TDE_UTILITIES.EntregaCierreTecnico
Out parameter o_message: value: Esto es una prueba sqlType: VARCHAR

But then how can I access the value of o_message? where is it stored?
I have tried using an IN OUT parameter on the procedure, but the token I use keeps the value it had before executing the procedure.

Thanks,

Zuria
7 REPLIES

Re: ksc_run_plsql_procedure: getting output parameters

Hi,

a execution step needs always a result matching a "code" entry of the configured step validation.

i.e.
You use the standard validation of execution steps "WF - Standard Execution Result" than
the possible values are SUCCESS or FAILURE
This means the output of your procedure must be SUCCESS or FAILURE

You can try the [SQL_OUTPUT] token to get the result of your procedure.

However in this case I use a function instead of a procedure and use a execution step of type "PL/SQL Function".
The function returns the code value of the validation and the workflow goes on using the transition configured with the validation result.

Regards
Volker
Zuria
Occasional Contributor

Re: ksc_run_plsql_procedure: getting output parameters

Hi,

Thanks for your response.

I have used one output parameter just to do the testing, but really the procedure would return two parameters, that's why I need a procedure.

My idea is to use it in a step of type "commands", store the output results in temporary variables with ksc_set and use them in another command. The finally result of the step (SUCCESS of FAILURE) would be calculated in the last command of the step.

Regards,

Zuria

Re: ksc_run_plsql_procedure: getting output parameters

Hi,

in this case try the [SQL_OUTPUT] token.

ksc_store [SQL_OUTPUT] =

Regards
Volker
Jim Esler
Honored Contributor

Re: ksc_run_plsql_procedure: getting output parameters

The correct syntax is:

ksc_store your_token=[SQL_OUTPUT]
Zuria
Occasional Contributor

Re: ksc_run_plsql_procedure: getting output parameters

Hi,

The [SQL_TOKEN] stores the result of a SQL query or function, but in this case I would like to run a procedure that returns more than one result...

Thanks

Regards,

Zuria

Erik Cole
Honored Contributor

Re: ksc_run_plsql_procedure: getting output parameters

If you're looking to call a procedure and get two distinct return values to store maybe you can structure it as a function and call it twice, each time specifying which output value you want it to return...?

Command steps like

ksc_oa_run_sql QUERY_STRING="select myFunc([INPUT_TOKEN],'A') from dual"

ksc_store your_token=[SQL_OUTPUT]

ksc_oa_run_sql QUERY_STRING="select myFunc([INPUT_TOKEN],'B') from dual"

ksc_store your_token=[SQL_OUTPUT]
Zuria
Occasional Contributor

Re: ksc_run_plsql_procedure: getting output parameters

Yes, finally I'm going to do it using two functions.

Thank you all

Regards,

Zuria
//Add this to "OnDomLoad" event