Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: dynamic sql
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 youWhat exactly are you doing?
DBMS_SQL is intended for issuing dynamic SQL not PL/SQL.
A simple function call is not SQL.
You could however do the following:
SELECT function(parms) FROM dual;
You could then parse this and use DBMS_SQL.DEFINE_COLUMN and DBMS_SQL.COLUMN_VALUE to get the result into a local variable.
I'll give an example. Assume you have a function named fred that takes no parameters but returns an integer. You could call it and get the result as follows:
DECLARE
cursor_handle INTEGER;
return_value INTEGER;
BEGIN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_handle,
'SELECT fred() FROM dual', DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(cursor_handle, 1, return_value); DBMS_SQL.EXECUTE_AND_FETCH(cursor_handle);DBMS_SQL.COLUMN_VALUE(cursor_handle, 1, return_value); DBMS_SQL.CLOSE_CURSOR(cursor_handle);