Re: I have this error...

In article <1996Jan26.214143.4042_at_nosc.mil>, "Michael P. Vergara" <mvergara_at_sctcorp.com> writes
>Everyone:
>
>Attached below is a little PL/SQL script which I'd like to create
>a function. It seems to work, until I get the indecipherable message
>which you seel below.
>
>Any suggestions about what I'm doing wrong?
>
>Thanks
>Mike Vergara
>
>====================================================================
>SQL>
>SQL> set echo on
>SQL> set feedback on
>SQL> set pause off
>SQL>
>SQL> set serveroutput on
>SQL>
>SQL> create or replace function
> 2 check_table_priv (p_table_name in varchar,
> 3 p_chk_priv in varchar)
> 4 return number as
> 5 --
> 6 ck_role varchar2(30);
> 7 ctr number;
> 8 cursor c_curr_role is
> 9 select granted_role
> 10 from user_role_privs;
> 11 BEGIN
> 12 ctr := 0;
> 13 loop
> 14 fetch c_curr_role into ck_role;
> 15 dbms_output.put_line('A Current Role is '||ck_role);
> 16 ctr := ctr + 1;
> 17 end loop;
> 18 return ctr;
> 19 END check_table_priv;
> 20 /
>
>Function created.
>SQL>
>SQL> select check_table_priv('TABLE','ACCESS') from dual;
>select check_table_priv('TABLE','ACCESS') from dual
> *
>ERROR at line 1:
>ORA-06571: Function CHECK_TABLE_PRIV does not guarantee not to update database
>
>
>========================================================================

If you wish to embed a stored function in a select list you must declare a pragma to let Oracle know that your function does not update, table data, database state etc.

Example:

    PRAGMA RESTRICT_REFERENCES(My_Function,WNDS)

There are several other options other than WNDS (which escape me at the moment) but if you're still having problems drop me a mail and I'll look up the manual title at work where these are kept.

Clive Bostock

Senior Consulant
KPMG (Health Systems)

All opions expressed are those of my
own and not my employer. Received on Sat Feb 03 1996 - 00:00:00 CET