Re: Issues in OUTPUTing the value from Oracle Procedure to C++ application

From: <sangu_rao_at_yahoo.co.in>
Date: 22 Jan 2007 21:23:22 -0800
Message-ID: <1169529802.828189.171320@a75g2000cwd.googlegroups.com>

Hi,
Thanks for your quick reply. Actually i am PL/SQL programmer. Even when i have executed the sp i am getting the correct value as a output. But when it is getting called from the C++ code it is always returning NULL value. After using the temporary table the C++ application is getting the correct value. So i thought there might be some issue in returning the value from the sp itself. Here is the C++ code which is intended to call the sp.

strSPName = "{CALL TestSPName(?)}";
vector<_variant_t> inputParms;
inputParms.push_back("Parameter1Value");

_RecordsetPtr pRstTemp;
_CommandPtr pCommand;

//Create the C++ ADO Objects
pCommand.CreateInstance(__uuidof(Command)); pCommand->ActiveConnection = pConn;

pCommand->put_CommandTimeout(60);

for(unsigned int i=0; i < inputParams.size(); ++i) {

	long paramSize = ((_bstr_t)inputParams[i]).length();
	if(paramSize == 0)
		paramSize = 1;

pCommand->Parameters->Append(pCommand->CreateParameter(_bstr_t (""),adLongVarWChar,adParamInput,paramSize,inputParams[i])); }

pRstTemp.CreateInstance(__uuidof(pRstTemp)); pCommand->put_CommandText(_bstr_t(strSPName.c_str()));

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

if (!pRstTemp->EndOfFile)
{

        _variant_t vt_Id =
pRstTemp->Fields->GetItem("GenSystemID")->GetValue();

	if (VT_NULL != vt_Id.vt)
	{
		_bstr_t GenSystemId =vt_Id.bstrVal;
		strGenSystemId = lexical_cast<string>(GenSystemId);
	}

}

Could you please tell me what is wrong with the C++ code that is stopping from returning the value?
Also is it always necessary to use a SYS_REFCURSOR to output the values to the other applications like C++?
Please provide your suggestions.

Thanks
Rao

sybrandb wrote:

> On Jan 22, 1:02 pm, sangu_..._at_yahoo.co.in wrote:
> > Hi,
> > I have Oracle sp which will accept 2 IN parameters and one OUT
> > parameter. This sp will get called from C++ application. Since Oracle
> > will only OUTPUT the value from Procedure to C++ application using the
> > SYS_REFCURSOR parameter i have created the OUTPUT parameter with the
> > type SYS_REFCURSOR.
> > But the issue is Oracle sp always OUTPUT the NULL values to the C++
> > application eventhough there is a value in the OUTPUT parameter. The
> > sample code for this is:
> >
> > CREATE OR REPLACE TEST (A IN INT,
> >                    B IN INT,
> >                    C OUT SYS_REFCURSOR)
> > AS
> > Var1 VARCHAR2(100) := 'this is the output';
> > BEGIN
> > -- Procedure body
> > OPEN C FOR SELECT var1 FROM DUAL;
> > END TEST;
> > /
> >
> > In the above code Var1 variable clearly has one value. But still the sp
> > is not returning the actual value to the C++ application. It is only
> > returning NULL. Why?
> >
> > We have solved this issue by creating a temporary table. The sample
> > code for this is:
> >
> > CREATE TABLE TEMP(var VARCHAR2(100));
> >
> > CREATE OR REPLACE TEST (A IN INT,
> >                    B IN INT,
> >                    C OUT SYS_REFCURSOR)
> > AS
> > Var1 VARCHAR2(100) := 'this is the output';
> > BEGIN
> > -- Procedure body
> >
> > -- This will delete the previous data if any
> > DELETE FROM TEMP;
> > COMMIT;
> >
> > -- Insert the Var1 value to the TEMP table
> > INSERT INTO TEMP VALUES (var1);
> > -- Create the SYS_CURSOR with the value from TEMP table.
> > OPEN C FOR SELECT var FROM TEMP;
> > END TEST;
> > /
> >
> > The above sp is returning the correct value to the Calling program
> > always. Why do we require a temporary table always in constructing a
> > SYS_REFCURSOR? It is difficult use a Temporary table for each sp which
> > is having a OUTPUT parameter and gets called from other applications
> > like C++.
> > Is there any alternative solution for this. One more interesting point
> > is if we create the TEMP table as a GLOBAL TEMPORARY TABLE and with ON
> > COMMIT DELETE option even then the sp will return NULL always.
> >
> > Can anyone please answer this.
> >
> > Thanks
> > Rao
>
>
> I conducted a small test on 9.2.0.8, in sql*plus, so not using OCI.
> The procedure is fine.
> SQL> variable c refcursor
> SQL> begin test(1,1,:c); end;
>   2  /
>
> PL/SQL-procedure is geslaagd.
>
> SQL> print c
>
> :B1
> --------------------------------
> this is the output
>
> SQL>
>
> You are calling it incorrectly.
> As you don't disclose how you are calling it, no one can help you.
> 
> -- 
> Sybrand Bakker
> Senior Oracle DBA

Received on Mon Jan 22 2007 - 23:23:22 CST