[PL/SQL] Lack understanding of PRAGMA RESTRICT

From: <jboes_at_qtm.net>
Date: Thu, 02 Jul 1998 18:25:15 GMT
Message-ID: <6ngjaa$emp$1@nnrp1.dejanews.com>

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 nbrApproved := 0; nbrRejected := 0; nbrUnprocessed := 0;

  FOR thisRow IN csrClaimUnits
  LOOP
    IF thisRow.CLAIM_APRVL_STAT_CODE = 'A' THEN       nbrApproved := nbrApproved + 1;
    ELSIF thisRow.CLAIM_APRVL_STAT_CODE = 'R' THEN       nbrRejected := nbrRejected + 1;
    ELSE
      nbrUnprocessed := nbrUnprocessed + 1;     END IF;
  END LOOP;   IF nbrUnprocessed > 0 THEN
    RETURN 'N';
  ELSIF nbrRejected > 0 and nbrApproved > 0 THEN     RETURN 'M';
  ELSIF nbrApproved > 0 THEN
    RETURN 'A';
  ELSIF nbrRejected > 0 THEN
    RETURN 'R';
  ELSE
    RETURN '0';
  END IF;
END;
END DCS_CLAIM_APRVL_STAT_SMMRY_PKG;
/
SHOW ERRORS -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 02 1998 - 13:25:15 CDT

[PL/SQL] Lack understanding of PRAGMA RESTRICT_REFERENCES