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
Received on Fri Feb 02 2007 - 00:08:05 CST
