i m using
:;
_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 );


to call

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;


Thanks
Md Nasim