Project and Portfolio Management Practitioners Forum
cancel

ksc_run_plsql_procedure: getting output parameters

Highlighted
Zuria
Regular 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
Volker Schoettn
Honored Contributor.

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
Regular 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
Volker Schoettn
Honored Contributor.

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
Acclaimed Contributor.

Re: ksc_run_plsql_procedure: getting output parameters

The correct syntax is:

ksc_store your_token=[SQL_OUTPUT]
Zuria
Regular 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
Acclaimed 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
Regular 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