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

On Feb 2, 11:08 am, sangu_..._at_yahoo.co.in wrote:
> On Jan 23, 10:23 am, sangu_..._at_yahoo.co.in wrote:
>
>
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> Hi Guys,
> Any thoughts on the above issue. Could you please respond to this?
> Thanks
> Rao- Hide quoted text -
>
> - Show quoted text -

Any thoughts on this issue, I have provided the C++ code which is creating the problem. Can anyone reply please?

Thanks
Rao Received on Tue Feb 06 2007 - 01:14:53 CST