Re: DBMS_SQL question(B

David Bratton wrote:
>
> I'm having trouble with DBMS_SQL. No matter what I do I get
> the same error message at run time.
>
> ex. SELECT TEST('RETENTION_CATEGORY','CATEGORY') FROM DUAL
> *
> ERROR at line 1:
> ORA-06571: Function TEST does not guarantee not to update database
>
> That looked like priviledge problem. So I granted SELECT ANY TABLE and
> EXECUTE ANY PROCEDURE to the account I'm using (and the account owns the
> tables I'm testing against). Didn't work.
>
> I'm stumped.
>
> I'm using version 7.1 on NT with PL/SQL version 2.1.
>
> Here is the PL/SQL source...
>
> create or replace function test(tabnam IN varchar2,colnam IN varchar2)
> return INTEGER AS
> stmt varchar2(255);
> v_cursor number;
> v_nbrrow INTEGER := 0;
> BEGIN
> stmt := 'SELECT DISTINCT '||colnam||' FROM '||tabnam;
> v_cursor := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(v_cursor,stmt,DBMS_SQL.V7);
> v_nbrrow := DBMS_SQL.EXECUTE(v_cursor);
> DBMS_SQL.CLOSE_CURSOR(v_cursor);
> return(v_nbrrow);
> EXCEPTION
> WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(v_cursor);
> RAISE;
> END;
> /
>
> TIA
> --
> David Bratton
> University of Houston System
> DBratton_at_uh.edu

The problem you are having is not with grants. If you want to call a function from an SQL statement, you have to guarantee Oracle that you are not going to update the database. Because of the DBMS_SQL statements that you call, Oracle cannot guarantee that it is "pure", meaning it will
not update the database.

Oracle gets around this by using pragmas. PRAGMA RESTRICT_REFERENCES statements. This is used for packages. I've tried
some of this and I can't seem to get around the problem for this particular case.
I'm not quite certain about the syntax of the PRAGMA statements and since I can't
find any references of this, I'm not even sure if it is something that Oracle
is allowing users to use. Anyway, you may be stuck breaking up the SQL statements
into several PL/SQL statements.

-- 
                  ____________________________________________ 
                 /    Kenichi Mizuta 
      ////      /     Oracle Corporation (Redwood Shores, CA) 
     |0 0|     /      Applications Division 
_ooO_ \U/_Ooo_/       email: kmizuta_at_us.oracle.com 
The comments and opinions expressed herein are mine and
do not necessarily represent those of Oracle Corporation.