Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: dynamic sql
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> @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 UsenetReceived on Fri Feb 07 1997 - 00:00:00 CST