How do I call a Oracle Stored Procedure that returns one or more REF CURSORS, using the ADO from C + + how

How to call a Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C + +Koushik Biswas, May 2006 Cpol

4.78 (Votes)

Rate: Vote 1 Vote 2 vote 3 vote 4 Vote 5

How do I call a Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C + +? Do you find it should is easy to find on the Internet or make it work with a little luck? Not before this article is posted! is your email address OK?Your are signed up for our newsletters but your email address is either unconfirmed, or has not been reconfirmed in a long Time. Please Click here to have a confirmation email sentSo we can confirm your email address and start sending for you newsletters again. Alternatively, you can update your subscriptions. Introduction

This article are exactly what the title Suggests-how to call a Oracle stored procedure that returns one or more REF C Ursors, using ADO from C + +. We needed this for one of our projects, and not knowing Oracle as so much as we knew SQL Server, searched for online Help for Days. No article gave us the technique we needed to know. The closest we got was a article that describes how we can do it from VB. There are a lot of libraries out There for the oracle/c++ combination-but none of them uses ADO objects. They Use the OCI library, which is not what we needed. We needed plain and simple ado-using the smart pointers _connectionptr, _commandptr, etc. My colleague Rabindra Mohapatra did all hard work, so a lot to his persistent, and Ho PE This article'll finally help others in our shoes in the future.

This article is a "technique" article. Hence No downloads, no images. Short, simple. Example Stored Procedure Hide Copy Code

CREATE OR REPLACE
PROCEDURE GetEmpRS1 (p_recordset1 OUT SYS_REFCURSOR, 
              p_recordset2 OUT SYS_REFCURSOR,
              PARAM IN STRING) AS
BEGIN
  OPEN p_recordset1 FOR
  SELECT RET1 
    FROM MYTABLE
    WHERE LOOKUPVALUE > PARAM;

  OPEN p_recordset2 FOR
  SELECT RET2
   FROM MYTABLE
   WHERE LOOKUPVALUE >= PARAM;
END GetEmpRS1;

This stored procedure takes a input parameter for lookup and has out REF two. For simplicity of this example, both the REF CURSORS return a column (a single column). ' Is ' not ' a ' requirement in ' course ', you can as-very associate a REF CURSOR with a SELECT statement Like:sel ECT * .... What is a REF CURSOR

Cursors, as you know, help return recordsets/resultsets. There May is another more technically correct definition of a cursor, but with I limited knowledge of databases, that's STA Tement sounds correct. A SQL Server stored procedure can return "a resultset" with a simple SELECT statement. It can even return multiple recordsets using multiple SELECT statements. Can Oracle do? Single Recordset, of course. Multiple recordsets-you need what is called a REF CURSOR. Treated just like a data type, your stored procedure takes REF CURSORS as out parameters Set in each REF CURSOR parameter the caller. So can include as many REF CURSOR parameters as you want-your stored procedure'll have the ability to return that Many recordsets. Cool, huh? C + + Code

The Error handling is isn't included for brevity, in this example. Hide Shrink Copy Code

_connectionptr M_pconn;
_RecordsetPtr precordset; 
_commandptr Pcommand;

_parameterptr pParam1;
We'll use PPARAM1 for the sole input parameter. Note:we must not append (hence need not create)//the REF CURSOR parameters. If your stored proc has//normal out parameters this are not REF CURSORS, you need//to create and append them.

But not the REF CURSOR ones!
Hardcoding the value of i/p paramter in this example ... _variant_t vt; Vt.

SetString ("2");
M_pconn.createinstance (__uuidof (Connection));

Pcommand.createinstance (__uuidof (Command)); The "Plsqlrset=1" part in//the connection string. can either//do that or can set the property separately using//pcommand->properties->getitem ("Plsqlrset")->
Value = true; But Beware if you are are not working with ORACLE, trying to getitem ()//a property This does not exist//will the ad

Erritemnotfound exception. M_pconn->open (_bstr_t ("provider=oraoledb.oracle; Plsqlrset=1;data source=xxx "), _bstr_t ("CP"), _bstr_t ("CP"), adModeUnknown);

Pcommand->activeconnection = M_pconn; pParam1 = Pcommand->createparameter (_bstr_t ("pParam1"), Adsmallint,adparaminput, sizeof (int), (VARIANT) v
T);
Pcommand->parameters->append (PPARAM1);

Precordset.createinstance (__uuidof (Recordset));
Note:we need to specify the stored procedure name as CommandText//with proper ODBC escape sequence.
If We assign CommandType to adCmdStoredProc and CommandText//to stored procedure name, it won't work in the case. Note this in the escape sequence, the number '? '

-S correspond to the//number of parameters, are not REF CURSORS.

Pcommand->commandtext = "{call GetEmpRS1 (?)}"; The options set for Execute. It did not work with most other//combinations. Note ' We are using a _RecordsetPtr object//to trap the ' return value of ' Execute call.

This single _recordsetptr//object would contain all the REF CURSOR outputs as adjacent. Precordset = PcommAnd->execute (null, NULL, ADCMDSTOREDPROC | adcmdunspecified); After this, traverse the Precordset object to retrieve all//the adjacent recordsets. They would be in the order of the//ref CURSOR parameters of the stored procedure.

In this example,//there is 2 recordsets, as there were 2 REF CURSOR out params. while (Precordset!=null)) {while (!precordset->getadoeof ()) {//traverse through All records O
    F Current Recordset ...}
    Long Lngrec = 0;
Precordset = Precordset->nextrecordset (VARIANT *) lngrec); //error Handling and cleanup code (like closing recordset/connection)//etc are don't shown here.

The catches and tricks are all discussed in the code snippet above, as running comments. So, I am not repeating them in the article text. That ' s it! Calling stored procedures from C + + using ADO has its own pitfalls, but this article are not intended to discuss all of thos E. May being, in some other article some day! This article was specifically targeted to fill up the gaping void, exists in the digital world of interconnected ERs out there-internet without a easy article in how to do Oracle REF CURSORS using ADO and C + +. So long. License