Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

Output parameter from Stored Procedure

                   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
Cisco Employee

Output parameter from Stored Procedure

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

New Member

Output parameter from Stored Procedure

This is for Oracle DB,

Thank you Chuandong, Will try this out.

Cisco Employee

Output parameter from Stored Procedure

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

New Member

Hi David, I have tried the

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

New Member

Output parameter from Stored Procedure

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;

70
Views
0
Helpful
5
Replies
CreatePlease login to create content