Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: RESTRICT_REFERENCES and DBMS_SQL Catch 22?

Re: RESTRICT_REFERENCES and DBMS_SQL Catch 22?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 11 Jun 1999 19:46:34 GMT
Message-ID: <376c6742.16224109@newshost.us.oracle.com>


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
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 11 1999 - 14:46:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US