Re: DBMS_SQL question

In article <31FD44A5.5735_at_jp.oracle.com>, Ken Mizuta <kmizuta_at_jp.oracle.com> writes:
|> 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.

This is correct. You need to have the PRAGMA RESTRICT_REFERENCES in all functions that you use in SQL statements. You also need them in all functions which those functions call - ie if A calls B in order to use A in a SQL statement than both A and B have to be pure in the sense of RESTRICT_REFERENCES.

The problem is that DBMS_SQL is not pure. It can't be, since you don't know what it could execute. So you can't use DBMS_SQL in functions that will be used inside SQL statements. Period.

|> 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.
Received on Tue Jul 30 1996 - 00:00:00 CEST