Dynamic SQL and returning value from function

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 Tue Oct 31 2000 - 02:35:51 CST