cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
687
Views
0
Helpful
5
Replies

Output parameter from Stored Procedure

jkanchar
Level 1
Level 1

                   Hi everyone,

I have a requirement to retrieve output parameters from storedprocedure thru CPO, I was being told that CPO 3.0 version has a facility to perform this. Let me know if anyone has tried this

5 Replies 5

David Xu
Cisco Employee
Cisco Employee

Hi, Jahnavi,

Is this SQL Server or Oracle database?

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.

ScreenHunter_312 Feb. 27 14.32.jpg

This is for Oracle DB,

Thank you Chuandong, Will try this out.

Hi, Jahnavi,

The example that I gave eariler is to return data via a Sys_RefCursor return type. Execute Oracle Script activity currently does not support returning multiple output parameters directly.

One work around is to wrap the stored procedure with another stored procedure to return a Cursor instead of output parameter. I am not sure if that is an option for your use case though.

Regards,

David

Hi David,

 

I have tried the below query from toad and it was working fine, but when I tried this from CPO, I have an error. Is this a tool limitation or some format related error?

DECLARE

v_rc SYS_REFCURSOR;

BEGIN

vpn_queue_pkg.dequeue_msg(p_wait_in => '5',  p_recordset=>v_rc);

END;

 

Error in CPO : Unable to cast object of type 'System.DBNull' to type 'Devart.Data.Oracle.OracleCursor'.

 

Regards,

Jahnavi

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;

or

2)Begin
vpn_queue_pkg.dequeue_msg(:v_rc);
End;

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: