PL/SQL Purity Levels

From: John Hough <q6y_at_ornl.gov>
Date: 1997/04/22
Message-ID: <335D3146.34F2_at_ornl.gov>#1/1

[Quoted] One of our programmers is attempting to write a PL/SQL Package of functions that are callable from SQL. He knows he must be concerned about the purity level of his functions. In order to use his functions in the where clause of the sql he believes the must be a purity [Quoted] level of WNDS and WNPS. All of the resources we have checked (Oracle Application Developers Guide, Oracle Press PL/SQL book and the O'Reilly PL/SQL book state the WNPS and RNPS mean that you cannot WRITE or READ package variables  "OUTSIDE" the package in which your function resides. It is permissible however to WRITE/READ package variables contained in the same package as your function. The Oracle Press book by Scott Urman has an example using this on page 270-271. When I tried to compile the example I get the following error.

_at_05-prgma.sql

Function created.

Package created.

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY STUDENTOPS:

LINE/COL ERROR

---

---

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


SQL> describe students
 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       VARCHAR2(10)
 FIRST_NAME                               VARCHAR2(20)
 LAST_NAME                                VARCHAR2(20)
 MAJOR                                    VARCHAR2(20)

The package text is shown below:

REM 05-PRGMA.SQL
REM This file contains the PRAGMA RESTRICT_REFERENCES examples REM used in Chapter 5 of "Oracle PL/SQL Programming".

REM This is version 1.0 of this file, updated 2/18/96. REM Comments and questions should go to Scott Urman at REM surman_at_us.oracle.com.

REM *** Chapter 5: The FullName Function *** CREATE OR REPLACE FUNCTION FullName (
  p_StudentID students.ID%TYPE)
  RETURN VARCHAR2 IS   v_Result VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name
    INTO v_Result
    FROM students
    WHERE ID = p_StudentID;

  RETURN v_Result;
END FullName;
/

REM *** Chapter 5: Using RESTRICT_REFERENCES in a Package *** CREATE OR REPLACE PACKAGE StudentOps AS
  FUNCTION FullName(p_StudentID IN students.ID%TYPE)     RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(FullName, WNDS, WNPS, RNPS);

  /* Returns the number of History majors. */   FUNCTION NumHistoryMajors
    RETURN NUMBER;
[Quoted]   PRAGMA RESTRICT_REFERENCES(NumHistoryMajors, WNDS, WNPS, RNPS); END StudentOps;
/

[Quoted] CREATE OR REPLACE PACKAGE BODY StudentOps AS

• Packaged variable to hold the number of history majors. v_NumHist NUMBER;

  FUNCTION FullName(p_StudentID IN students.ID%TYPE)     RETURN VARCHAR2 IS
    v_Result VARCHAR2(100);
  BEGIN
    SELECT first_name || ' ' || last_name

      INTO v_Result
      FROM students
      WHERE ID = p_StudentID;

    RETURN v_Result;
  END FullName;

[Quoted]   FUNCTION NumHistoryMajors RETURN NUMBER IS     v_Result NUMBER;
  BEGIN
    IF v_NumHist IS NULL THEN

      /* Determine the answer. */
      SELECT COUNT(*)
        INTO v_Result
        FROM students
        WHERE major = 'History';
      /* And save it for future use. */
      v_NumHist := v_Result;
    ELSE
      v_Result := v_NumHist;

    END IF;     RETURN v_Result;
  END NumHistoryMajors;
END StudentOps;
/

Any ideas of why this package will not compile?

Environment:

Operating System: OpenVMS (TM) Alpha Operating System, Version V7.1 Oracle Version: Oracle7 Server Release 7.3.2.3.0

• Production Release PL/SQL Release 2.3.2.3.0 - Production

Thanks,

John Hough Received on Tue Apr 22 1997 - 00:00:00 CEST