Re: dynamic sql

From: <Solomon.Yakobson_at_entex.com>
Date: 1997/02/07
Message-ID: <855334991.23926_at_dejanews.com>#1/1

You need to use DBMS_SQL.VARIABLE_VALUE:

CREATE OR REPLACE FUNCTION Double(N IN NUMBER) RETURN NUMBER AS BEGIN RETURN N*2; END; / SET SERVEROUTPUT ON DECLARE nCursor_Number NUMBER; nIgnore NUMBER; nResult NUMBER; nDummy NUMBER; BEGIN nCursor_Number := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(nCursor_Number,'BEGIN :nX := Double(:nY); END;',DBMS_SQL.V7);

DBMS_SQL.BIND_VARIABLE(nCursor_Number,'nX',nDummy); 
DBMS_SQL.BIND_VARIABLE(nCursor_Number,'nY',100);  nIgnore :=
DBMS_SQL.EXECUTE(nCursor_Number); 
DBMS_SQL.VARIABLE_VALUE(nCursor_Number,'nX',nResult); 
DBMS_SQL.CLOSE_CURSOR(nCursor_Number);	DBMS_OUTPUT.PUT_LINE('100 * 2 =

'||nResult); END; / "dsql.sql" 23 lines, 622 characters

SQL> _at_dsql

Function created.

100 * 2 = 200

PL/SQL procedure successfully completed.

Note: You need to issue DBMS_SQL.BIND_VARIABLE for function return value even though it is OUT variable. DBMS_SQL package does not distinguish between IN and OUT bind variables

Solomon.Yakobson_at_entex.com

In article <5dc8ci$ju2$1_at_proxy01.iafrica.com>,   Denver.Schouw_at_Baps.com (Denver Schouw) wrote:
>
> hi
>
> if the 'parsed statement' in dbms_sql.parse is a function call that returns a
> value,how do i retreive this value?
>
> thank you

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Fri Feb 07 1997 - 00:00:00 CET