Re: PRAGMA RESTRICT_REFERENCES(funct name, ????, ????, ...)

From: Mike Hackett <sharmike_at_voicenet.com>
Date: 1997/10/15
Message-ID: <623nbo$q9h$1@news2.voicenet.com>#1/1

From the Oracle7 Server Application Developer's guide in the Procedures and packages chapter:

---

To call a packaged function from SQL expressions, you must assert its purity level by coding the pragma RESTRICT_REFERENCES in the package specification (not in the package body). The pragma must follow the function declaration but need not follow it immediately. Only one pragma can reference a given function declaration.
To code the pragma RESTRICT_REFERENCES, you use the syntax

PRAGMA RESTRICT_REFERENCES     function_name, WNDS [, WNPS] [, RNDS] [, RNPS]); where:

WNDS
means "writes no database state" (does not modify database tables)

WNPS
means "writes no package state" (does not change the values of packaged variables)

RNDS
means "reads no database state" (does not query database tables)

RNPS
means "reads no package state" (does not reference the values of packaged variables)

You can pass the arguments in any order, but you must pass the argument WNDS. No argument implies another. For instance, RNPS does not imply WNPS. In the example below, the function compound neither reads nor writes database or package state, so you can assert the maximum purity level. Always assert the highest purity level a function allows. That way, the PL/SQL compiler will never reject the function unnecessarily.

CREATE PACKAGE finance AS -- package specification

   ...
   FUNCTION compound

         (years  IN NUMBER,
          amount IN NUMBER,
          rate   IN NUMBER) RETURN NUMBER;

   PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS); END finance;

CREATE PACKAGE BODY finance AS --package body

   ...
   FUNCTION compound

         (years  IN NUMBER,
          amount IN NUMBER,
          rate   IN NUMBER) RETURN NUMBER IS
   BEGIN
      RETURN amount * POWER((rate / 100) + 1, years);
   END compound;
                   -- no pragma in package body

END finance;
Later, you might call compound from a PL/SQL block, as follows:

BEGIN
   ...

    SELECT finance.compound(yrs,amt,rte)  -- function call       INTO
interest       FROM accounts       WHERE acctno = acct_id;

Fred Hirschfeld wrote in message <622fej$rf5$1_at_news.bc>...
>Does anyone know where I can get information on the parameters for this
>pragma:
> PRAGMA RESTRICT_REFERENCES(funct name, ????, ????, ...)
>A manual name would do...
>
> Cheers Fred
>---------------------------------------------------------------------------
 -
>Fred Hirschfeld Sierra Systems Consultants
 Inc
> Vancouver, BC (604)688-1371
>fhirsch@rogers.wave.ca http://www.sierrasys.com/
>
>
>
Received on Wed Oct 15 1997 - 00:00:00 CDT