How to call an Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C++

Introduction

This article is exactly what the title suggests - How to call an Oracle stored procedure that returns one or more REF CURSORS, using ADO from C++. We needed that for one of our projects, and not knowing Oracle as 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 an 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 was not what we needed. We needed plain and simple ADO - using the smart pointers _ConnectionPtr, _CommandPtr, etc. My colleague Rabindra Mohapatra did all the hard work, so thanks a lot to his persistent research, and hope this article will 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

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 an input parameter for lookup, and has two OUT REF CURSORS. For simplicity of this example, both the REF CURSORS return a column (a single column). That is not a requirement in real life of course, you can as well associate a REF CURSOR with a SELECT statement like: SELECT * ....

What is a REF CURSOR

Cursors, as you know, help return recordsets/ resultsets. There may be another more technically correct definition of a cursor, but with my limited knowledge of databases, that statement 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 that? 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, and you can return a full recordset in each REF CURSOR parameter back to the caller. So you can include as many REF CURSOR parameters as you want - your stored procedure will have the ability to return that many recordsets. Cool, huh?

C++ Code

Error handling is not included for brevity, in this example.

_ConnectionPtr m_pConn;
_RecordsetPtr pRecordset;
_CommandPtr pCommand; 
_ParameterPtr pParam1;

//We will 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 that are not REF CURSORS, you need
//to create and append them too. 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));

//NOTE the "PLSQLRSet=1" part in 
//the connection string. You can either
//do that or can set the property separately using 
//pCommand->Properties->GetItem("PLSQLRSet")->Value = true;
//But beware if you are not working with ORACLE, trying to GetItem()
//a property that does not exist 
//will throw the adErrItemNotFound 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 ) vt);
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 will not work in this case.
//NOTE that in the escape sequence, the number '?'-s correspond to the
//number of parameters that are NOT REF CURSORS.

pCommand->CommandText = "{CALL GetEmpRS1(?)}";

//NOTE the options set for Execute. It did not work with most other
//combinations. Note that we are using a _RecordsetPtr object
//to trap the return value of Execute call. That single _RecordsetPtr
//object will contain ALL the REF CURSOR outputs as adjacent recordsets.

pRecordset = pCommand->Execute(NULL, NULL, 
             adCmdStoredProc | adCmdUnspecified );

//After this, traverse the pRecordset object to retrieve all
//the adjacent recordsets. They will be in the order of the
//REF CURSOR parameters of the stored procedure. In this example,
//there will be 2 recordsets, as there were 2 REF CURSOR OUT params.

while( pRecordset !=NULL ) )
{
    while( !pRecordset->GetadoEOF() )
    {
        //traverse through all the records of current recordset...
    }
    long lngRec = 0;
    pRecordset = pRecordset->NextRecordset((VARIANT *)lngRec);
}

//Error handling and cleanup code (like closing recordset/ connection)
//etc are not 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 is not intended to discuss all of those. May be, in some other article some day! This article is specifically targeted to fill up the gaping void that exists in the digital world of interconnected computers out there - internet without an easy article on how to do Oracle REF CURSORS using ADO and C++. So long.