Re: Function does not guarantee... using DBMS_SQL package.

Somewhere along the line, purity is set for (one of the elements of) packages.
You'll have to add pragma's, i.e:
pragma restrict_references(YourFunction, WNDS, WNPS); in the specification of the package.
There are 4 pragma's possible:

WNDS: Write No Database State
WNPS: Write No Package State
RNDS: Read No Database State
RNPS: Read No Package State

An update is seens as a possible cause for changes in the database state (error conditions,
and such). I.e. any package, procedure that does a raise cannot have WNDS for purity.

If one package has purity defined, all dependants must have at least the same level of purity.

--
Kind Regards,
Frank
"Jim Ley" <jim_at_jibbering.com> wrote in message
news:397c2ac0.10892144_at_news-east.usenetserver.com...


> On writing a function using the DBMS_SQL package for dynamic SQL, the

> function is created (although the input is truncated to 1

> character(?)), When I use the function with

>

> SELECT f('a','b') FROM dual

>

> I get an error;

> ORA-06571: Function F does not guarantee not to update database

>

> Although my function does not include any DDL statements.

> Any ideas why?

>

>

> below is the code for the function:

>

> create or replace function f(x in varchar2,

>    y in varchar2)

>    return varchar2

> is

> cursor_handle integer := DBMS_SQL.OPEN_CURSOR;

> dbms_sql_feedback integer;

> return_value varchar2(100) :=NULL;

> begin

> DBMS_SQL.PARSE (cursor_handle,

> 'select distinct ' || x ||

> ' from ' || y ||

> ' where type = ''CRE_DEL''',DBMS_SQL.NATIVE);

> DBMS_SQL.DEFINE_COLUMN (cursor_handle,1,x,100);

> dbms_sql_feedback := DBMS_SQL.EXECUTE (cursor_handle);

> dbms_sql_feedback := DBMS_SQL.FETCH_ROWS (cursor_handle);

> if dbms_sql_feedback > 0

> then

> DBMS_SQL.COLUMN_VALUE (cursor_handle,1,return_value);

> end if;

> DBMS_SQL.CLOSE_CURSOR (cursor_handle);

> return_value := 7;

> return return_value;

> end;

>

>

> Chees,

>

> Jim.