Re: pragma RESTRICT

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 08 Jun 1998 18:33:40 GMT
Message-ID: <357d2be1.22275059@192.86.155.100>

Yes, you need to take into account that every procedure in that package now READS the database state and WRITES the package state.

To avoid confusion (eg: if you asserted the purity of A in the example below, the compiler would tell you A violates its associated pragma when in fact A doesn't but the package does) I like to assert the purity of not only the procedures in the package but the package itself.

For example, the following example shows what I am trying to describe:

SQL> create or replace package mine
2 as

  3          procedure a;
  4          pragma restrict_references( a, wnds, rnds, wnps, rnps );

5 end;
6 /
Package created.

SQL> create or replace package body mine   2 as
  3 n number;
  4 procedure a
  5 is
  6 begin
  7 null;
  8 end;
  9 begin
10 select count(*) into N from dual; 11 end;
12 /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY MINE:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
4/1      PLS-00452: Subprogram 'A' violates its associated pragma

So, its telling us that A is causing a problem but obviously, A is not causing the problem (it does nothing). So, I suggest rewriting the spec as follows:

SQL> create or replace package mine
2 as
3 pragma restrict_references( mine, wnds, rnds, wnps, rnps ); 4

  4          procedure a;
  5          pragma restrict_references( a, wnds, rnds, wnps, rnps );

6 end;
7 /
Package created.

SQL>
SQL> alter package mine compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors package body mine
Errors for PACKAGE BODY MINE:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/2      PLS-00452: Subprogram 'MINE' violates its associated pragma

Ahh, now its telling us the PACKAGE 'MINE' is doing something wrong (and this time its right). By asserting the overall purity of the package, the compiler will tell us the package is in violation. Before, we only asserted the procedure and the procedure was listed...

This little trick can and will save you hours of head scratching some day in the future.

So, what is the purity level we can assert:

SQL> create or replace package mine
2 as
3 pragma restrict_references( mine, wnds, wnps ); 4

  4          procedure a;
  5          pragma restrict_references( a, wnds, wnps );

6 end;
7 /

Package created.

SQL>
SQL> alter package mine compile body;

Package body altered.

SQL> show errors package body mine
No errors.

That is telling us the maximum purity level ANY procedure in this package can have is wnds, wnps (what is specified at the package level).

A copy of this was sent to Connor McDonald <mcdonald.connor.cs_at_bhp.com.au> (if that email address didn't require changing) On Mon, 08 Jun 1998 18:30:07 +0800, you wrote:

>If you have anonymous block in your package body,
>do you need to take that into account when using
>pragma restrict_references
>
>eg
>
>package MINE is
> proc A;
>end;
>
>package body MINE is
> proc A is
> begin
> blah, blah but no sql
> end;
>begin
> select ...
> into some package variables
> from ...;
>end;
>
>Now when "MINE.A" is first called, some SQL will be run in the
>anonymous part of the package body...Although there is no explicit
>SQL in proc A, will it need the appropriate pragma restrict_references
>clause ???
>
>Cheers
>Connor

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jun 08 1998 - 13:33:40 CDT

Re: pragma RESTRICT_REFERENCES