Function does not guarantee... using DBMS_SQL package.

From: Jim Ley <jim_at_jibbering.com>
Date: 2000/07/24
Message-ID: <397c2ac0.10892144@news-east.usenetserver.com>#1/1

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. Received on Mon Jul 24 2000 - 00:00:00 CDT