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

From: sybrandb <sybrandb_at_gmail.com>
Date: 22 Jan 2007 04:54:04 -0800
Message-ID: <1169470444.654853.132610@l53g2000cwa.googlegroups.com>

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 - 06:54:04 CST