Re: Dynamic SQL and returning value from function

John,

In the code below, I do not see the bind variable :ret defined.

You need a 'var ret varchar2(100)'

Jared

On Tue, 31 Oct 2000, John Dunn wrote:

> Here's a simpler example than the question I asked earlier ...
>
> The function I'm trying to call is
>
> ...
>
> CREATE FUNCTION dummy RETURN VARCHAR2 AS
>
> var_return VARCHAR2(100);
>
> begin
>
> var_return := 'HELLO';
> RETURN var_return;
>
> end;
>
> ...
>
> The code I'm using to call it is
>
> ...
>
> declare
>
> num_rows INTEGER;
> var_sql VARCHAR2(100);
> var_return VARCHAR2(100);
> num_dynamic_cursor INTEGER;
>
> begin
>
> var_sql := 'begin :ret := dummy; end;';
>
> num_dynamic_cursor := dbms_sql.open_cursor;
>
> dbms_sql.parse(num_dynamic_cursor, var_sql, dbms_sql.native);
>
> dbms_sql.bind_variable(num_dynamic_cursor, ':ret', var_return);
>
> num_rows := dbms_sql.execute(num_dynamic_cursor);
>
> dbms_sql.close_cursor(num_dynamic_cursor);
>
> end;
>
> ...
>
> I always get a PL/SQL numeric or value error - presumably because I need to
> declare the var_return variable as an out parameter somehow.
>
> Can someone please help?
>
> (I appreciate there is no need to use dynamic SQL to call the function -
Received on Wed Nov 01 2000 - 16:43:00 CST