Calling an API to Capture Output Parameter Values

You can call a public Oracle Life Sciences Data Hub API from SAS source code to capture the values of output Parameters in a SAS Program contained in a Report Set or Workflow for the purpose of passing their value during execution to another Program in the same Report Set or Workflow (see Setting Up Parameter Value Propagation).

If you set up value propagation in a Report Set or Workflow, you must call an API from each SAS Program whose output Parameter values you need to capture. You must call the API once for each Parameter value you need. You may want to add the API call to every SAS Program that contains output Parameters in case you later add the Program to a Report Set or Workflow and want to use the output Parameter value in value propagation. The API package procedure name is: Cdr_Pub_Exe_User_Utils.setOutputParams.

The example below uses a PL/SQL wrapper to call the API. In this way you can call the API multiple times and only connect to the database once from SAS, and only two arguments are required for each output Parameter that you want to send back to Oracle LSH:

• pi_vparamName. Enter the Name of the output or input/output Parameter whose value you want to capture.
• pi_vparamValue. This procedure parameter receives the value of the Program Parameter you specified as the value of pi_vparamName.

You can use the following code to call the API. Use %sysget (as shown) to get the required values rather than hardcoding the values in the code.

SAS code
--------------------------------------------------------------------------------
Proc SQL;
/*set the job context then send the output value*/
connect to oracle (user=%sysget(CDR_SCHEMA) pass=%sysget(CDR_PASSWD)
path=%sysget(CDR_DB) );
 
/* pass output parameter back to LSH */
execute(exec my_plsql_package.setOutputParams(
'MyParamName'
,'My Param Value'
)
by oracle ;
 
PL/SQL code
--------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE my_plsql_package AS
Procedure setOutputParams(
pi_vParamName IN varchar2
,pi_vParamValue IN varchar2
);
END my_plsql_package;
/
 
CREATE OR REPLACE PACKAGE BODY my_plsql_package AS
Procedure setOutputParams(
pi_vParamName IN varchar2
,pi_vParamValue IN varchar2
) IS
return_status VARCHAR2(10);
msg_count NUMBER;
msg_data VARCHAR2(2000);
BEGIN
   Cdr_Pub_Exe_User_Utils.setOutputParams(p_api_version => 1
               ,p_init_msg_list => Cdr_Pub_Def_Constants.G_FALSE
               ,p_commit => Cdr_Pub_Def_Constants.G_FALSE
               ,p_validation_level => Cdr_Pub_Def_Constants.G_VALID_LEVEL_FULL
               ,x_return_status => return_status
               ,x_msg_count => msg_count
               ,x_msg_data => msg_data
               ,pi_vparamName => pi_vParamName
               ,pi_vparamValue => pi_vParamValue) ;
   IF return_status <> 'S' THEN
      RAISE_APPLICATION_ERROR(-20200,'Failed to call Cdr_Pub_Exe_User_Utils.setOutputParams: '||msg_data);
   END IF ;
END setOutputParams;
END my_plsql_package;
/