For SQL Server, the "Execute SQL Server SQL Script" activity can be used to return data from SQL Server procedure.
For Oracle database, the "Execute Oracle SQL Script" activity can be used to return data from Oracle stored procedure. You will need a cursor to return data. Here is an example. Please make sure that you put "testCursor" to the “Specify a cursor returned by a stored procedure in the command” field.
Could you please suggest me the right approach of executing the stored procedure in CPO for the above requirement, should I provide the outparams in the query as well?
1) Begin vpn_queue_pkg.dequeue_msg; (p_UserID_out OUT VARCHAR2, p_HostName_out OUT VARCHAR2, p_Port_out OUT VARCHAR2, p_Admin_Group_out OUT VARCHAR2, p_Role_out OUT VARCHAR2, p_Serial_Number_out OUT varchar2, p_Activation_Code_out OUT varchar2, p_Policy_String_out OUT varchar2, p_Channel_ID_out OUT VARCHAR2, p_action_out OUT VARCHAR2, p_Priority_out OUT VARCHAR2)(:v_rc); End;