Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: RESTRICT_REFERENCES and DBMS_SQL Catch 22?
A copy of this was sent to marckell_at_best.com
(if that email address didn't require changing)
On Fri, 11 Jun 1999 18:00:19 GMT, you wrote:
>Could someone tell me which one I'm mistaken about, or if there is a
>workaround:
>
>1. When creating a PLSQL function, you need to add a RESTRICT_REFERENCES
>pragma to the package to enforce read-only rights to the function.
>Otherwise the block won't run (error of "No guarantee it won't write" or
>something along those lines).
>
the block will run, but not when called from SQL. You cannot SELECT <that function> from dual but you can:
SQL> declare
x varchar2(25); begin x := that_function; end;
or
SQL> exec dbms_output.put_line( that_function );
or something similar.
In Oracle8i, release 8.1, using the enhanced dynamic sql new to that release, you can use dynamic sql in functions called from sql.
>2. When using RESTRICT_REFERENCES, you can't use DBMS_SQL for
>inserting variables into SQL statements, as this violates the purity of
>RESTRICT_REFERENCES. The SQL statement is not known at compile-time,
>so it won't allow the block to be used.
>
>3. The only way to create a SQL statement dynamically, which
>is necessary in my case because the table name is unknown until
>it's run, is to use DBMS_SQL.
>
>Catch 22.
>
>So my initially simplistic function in which I would pass
>in the name of a table and return a single value from that
>table is turning into a ridiculously confusing project, unless
>someone here can tell me where I'm going wrong.
>
>Oracle 7.33/DEC OSF1 V4
>
>mk
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--