Re: [PL/SQL] Lack understanding of PRAGMA RESTRICT

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Jul 1998 19:54:08 GMT
Message-ID: <359ee4d4.24174961@192.86.155.100>

A copy of this was sent to jboes_at_qtm.net (if that email address didn't require changing) On Thu, 02 Jul 1998 18:25:15 GMT, you wrote:

>I'm developing a function, and got the dreaded "function does not guarantee
>not to update database" error. I had not run across this before; of course,
>I've not developed a lot of functions before.
>
>The function contains a single cursor, and runs through that cursor counting
>up various conditions so it can report a single flag indicating something
>like "some", "none", "all", or "invalid" depending on how the count went.
>
>I packaged the function so that I could insert the PRAGMA RESTRICT_REFERENCES
>line (d****d restricted documentation set I have to work with, you'd think a
>company that spent this much on Oracle software would have something close to
>a full doc set and put it in a place where I could see it!--but that's
>another story). Now the PL/SQL compiler is complaining that the function
>violates its own pragma. I just can't see what I'm doing that violates
>WNDS/WNPS.
>
>CREATE OR REPLACE PACKAGE DCS_CLAIM_APRVL_STAT_SMMRY_PKG
>IS
> FUNCTION CLAIM_APRVL_STAT_SMMRY(
> ClaimType IN DCS_TRNSP_CLM.CLAIM_SRCE_SYS_TYPE_CODE%TYPE,
> ClaimNbr IN DCS_TRNSP_CLM.TRNSPN_CLAIM_NBR%TYPE,
> ClaimDate IN DCS_TRNSP_CLM.CLAIM_CREATE_DATE%TYPE,
> OrgCode IN DCS_TRNSP_CLM_APRVL_AUTH.CLAIM_APRVL_ORG_CODE%TYPE)
> RETURN VARCHAR2;
>
>PRAGMA RESTRICT_REFERENCES(CLAIM_APRVL_STAT_SMMRY, WNDS, WNPS);
>
>END DCS_CLAIM_APRVL_STAT_SMMRY_PKG;
>/
>SHOW ERRORS
>
>CREATE OR REPLACE PACKAGE BODY DCS_CLAIM_APRVL_STAT_SMMRY_PKG
>IS
>
>FUNCTION CLAIM_APRVL_STAT_SMMRY(
> ClaimType IN DCS_TRNSP_CLM.CLAIM_SRCE_SYS_TYPE_CODE%TYPE,
> ClaimNbr IN DCS_TRNSP_CLM.TRNSPN_CLAIM_NBR%TYPE,
> ClaimDate IN DCS_TRNSP_CLM.CLAIM_CREATE_DATE%TYPE,
> OrgCode IN DCS_TRNSP_CLM_APRVL_AUTH.CLAIM_APRVL_ORG_CODE%TYPE)
>RETURN VARCHAR2
>IS
>
> -- This function returns a summary of the status of a claim,
> -- based on the most recent approvals for each of its units
> -- for a given claim approval organization code.
> -- The return code interpreted:
> --
> -- A: All claim units are approved by the specified organization
> -- R: All claim units are rejected by the specified organization
> -- M: Some claim units approved, some rejected ('mixed')
> -- N: Some claim units unprocessed (no authorizations found)
> -- 0 (zero): Shouldn't happen, but indicates no claim units found
>
> nbrApproved INTEGER; nbrRejected INTEGER; nbrUnprocessed INTEGER; CURSOR
>csrClaimUnits IS SELECT CLAIM_APRVL_STAT_CODE, unit.MODEL_NBR,
>unit.SERIAL_NBR, MAX(TX_TMSTMP) FROM DCS_TRNSP_CLM_APRVL_AUTH auth,
>DCS_TRNSP_CLM_UNIT unit WHERE unit.CLAIM_SRCE_SYS_TYPE_CODE = ClaimType
>AND unit.TRNSPN_CLAIM_NBR = ClaimNbr AND TO_CHAR(unit.CLAIM_CREATE_DATE,
>DCS_CONSTANTS_PKG.C_DATE_FORMAT) = TO_CHAR(ClaimDate,
>DCS_CONSTANTS_PKG.C_DATE_FORMAT) AND auth.CLAIM_SRCE_SYS_TYPE_CODE (+) =
>unit.CLAIM_SRCE_SYS_TYPE_CODE AND auth.TRNSPN_CLAIM_NBR (+) =
>unit.TRNSPN_CLAIM_NBR AND auth.CLAIM_SRCE_SYS_TYPE_CODE (+) =
>unit.CLAIM_SRCE_SYS_TYPE_CODE AND CLAIM_APRVL_ORG_CODE (+) = OrgCode GROUP
>BY CLAIM_APRVL_STAT_CODE, unit.MODEL_NBR, unit.SERIAL_NBR ; BEGIN

Your package is referring to another package, DCS_CONSTANTS_PKG. The purity level of this other package is unknown and the worst is assumed...

Consider the following example which demonstrates the error and shows how to fix it:

SQL> create or replace package const
  2 as
  3 some_mask varchar2(15) default 'dd-month-yyyy';   4 end;
  5 /
Package created.

SQL> create or replace package demo
2 as

  3          function foo return varchar2;
  4          pragma restrict_references(foo,wnds,wnps);

5 end;
6 /
Package created.

SQL> create or replace package body demo   2 as
  3
  3 function foo return varchar2
  4 is
  5 begin
  6 return to_char( sysdate, const.some_mask );   7 end;
  8
  8 end;
  9 /
Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY DEMO:

LINE/COL ERROR

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

So, that shows the error happening -- foo obviously doesn't write to the data or the package but it errs out.... To fix it, we specify the purity level of the package const as follows:

SQL> create or replace package const
  2 as
  3 pragma restrict_references(const,wnds,rnds,wnps,rnps);   4
  4 some_mask varchar2(15) default 'dd-month-yyyy';   5 end;
  6 /
Package created.

SQL> alter package demo compile body;

Package body altered.

and now it works.....

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 Thu Jul 02 1998 - 14:54:08 CDT

Re: [PL/SQL] Lack understanding of PRAGMA RESTRICT_REFERENCES