Re: DBMS_SQL and RESTRICT REFERENCES problem

From: Scott Urman <surman_at_dlsun338.us.oracle.com>
Date: 1996/08/02
Message-ID: <4tu0e9$736_at_inet-nntp-gw-1.us.oracle.com>#1/1


In article <1996Aug2.144319.30752_at_ludens>, seci_at_ludens.elte.hu writes:
|> hello!
|>
|> i am writing a (packaged) function that collects some information from a table
|> - only READING the table BUT with dynamic SQL (the first parameter is
|> the tablename)
|>
|> the problem occurs when i am trying to use this function in an INSERT
|> statement VALUES list. execution is terminated with "Function does not
|> guarantee not to modify database state or something like this.
|>
|> i have tried to use the pragma RESTRICT REFERENCES but is does not work
|> bacause DBMS_SQL.* does not guarantee not to modify database state -
|> but i KNOW that is this case it guarantees.
|>
|> so, is there any tricky way to tell oracle that this function is ok and
|> can be used in an insert ???

Nope. The problem is that the DBMS_SQL package does not have the RESTRICT_REFERENCES pragma set for it. Some of the DBMS packages do, and others don't. It is impossible for Oracle to set this for DBMS_SQL, since in general functions using DBMS_SQL can update the database. Yours in particular may not, but Oracle has no way of guarenteeing this since the SQL statement is constructed at runtime. Sorry.

|>
|> TIA
|> Peter



Scott Urman Oracle Corporation surman_at_us.oracle.com

Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm

"The opinions expressed here are my own, and are not necessarily that of  Oracle Corporation"
Received on Fri Aug 02 1996 - 00:00:00 CEST

Original text of this message