Ask a Question

execute oracle stored procedure with refcursor output parameter in testcomplete

SOLVED
hb
Occasional Contributor

execute oracle stored procedure with refcursor output parameter in testcomplete

I have to execute an oracle stored procedure from test complete that returns a refcursor, I tried different ways with parameter object and also using the record set

 

My stored procedure exm

 

create or replace
PROCEDURE TESTPROC (
retCur OUT GLOBALPKG.RCT1)
AS
BEGIN
open retCur for
SELECT mc_id, ct_id,ct_type from authors where id=20210;
END TESTPROC; 

 

In sqldeveloper, I execute it as below

var c refcursor;
execute testproc (:c);
print c;

 

 

If I use the below, it says parameter not defined.

 

var recset = Sys.OleObject("ADODB.recordset");
var conObj = Sys.OleObject("ADODB.Connection");
var cmdObj = Sys.OleObject("ADODB.Command");
var conString = "Driver={Microsoft ODBC for Oracle};Server=db1:1521/utdb1; Uid=PW11;Pwd=o";
conObj.Open(conString);

cmdObj.ActiveConnection= conObj;
cmdObj.CommandText="TESTPROC";
cmdObj.CommandType= 4 //adCmdStoredProc;
recset = cmdObj.Execute();

 

IF I set up the parameter object, it says parameter not supported

 

var conObj = Sys.OleObject("ADODB.Connection");
var cmdObj = Sys.OleObject("ADODB.Command");
var conString = "Driver={Microsoft ODBC for Oracle};Server=db1:1521/utdb1; Uid=PW11;Pwd=o";
conObj.Open(conString);

cmdObj.ActiveConnection= conObj;
cmdObj.CommandText="TESTPROC";
cmdObj.CommandType= 4 //adCmdStoredProc;

var Prm = cmdObj.CreateParameter("MyParam", adUserDefined, adParamOutput);
cmdObj.Parameters.Append(Prm);
cmdObj.Execute();

 

Can you please suggest if there is a way to get around this.

2 REPLIES 2
hb
Occasional Contributor

I found the solution for this and resolved this.

 

Had to change the provide to ORAOLEDB provider instead of MSDORA.

 

Thanks

TanyaYatskovska
SmartBear Alumni (Retired)

Hi Hchaganti,

 

Thanks for sharing your solution with us. I hope it will help somebody in the future.

 

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager



cancel
Showing results for 
Search instead for 
Did you mean: