Re: dynamic sql

From: Chris Halioris <halioris_chris_nonlilly_at_lilly.com>
Date: 1997/02/07
Message-ID: <32FBA309.3BA_at_lilly.com>#1/1

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);

• return_value now holds the result of the call to fred() END;

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