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;
_variant_t vt;
vt.SetString("2");
m_pConn.CreateInstance (__uuidof (Connection));
pCommand.CreateInstance (__uuidof (Command));
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));
pCommand->CommandText = "{CALL GetEmpRS1(?)}";
pRecordset = pCommand->Execute(NULL, NULL,
adCmdStoredProc | adCmdUnspecified );
while( pRecordset !=NULL ) )
{
while( !pRecordset->GetadoEOF() )
{
}
long lngRec = 0;
pRecordset = pRecordset->NextRecordset((VARIANT *)lngRec);
}
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.