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

From: <sangu_rao_at_yahoo.co.in>
Date: 22 Jan 2007 04:02:29 -0800
Message-ID: <1169467349.843103.166120@51g2000cwl.googlegroups.com>

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 Received on Mon Jan 22 2007 - 06:02:29 CST