Forum Discussion

hb's avatar
hb
Occasional Contributor
9 years ago
Solved

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.

  • I found the solution for this and resolved this.

     

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

     

    Thanks

2 Replies

  • hb's avatar
    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's avatar
      TanyaYatskovska
      SmartBear Alumni (Retired)

      Hi Hchaganti,

       

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