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

Hi Jim,

if you are using Oracle 8 and your function within SQL, some BUILT -in Packages like DBMS_SQL are not allowed!!

By
Frank

Jim Ley schrieb:

> 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