Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> PL/SQL Purity Levels
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 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.
@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;
PRAGMA RESTRICT_REFERENCES(NumHistoryMajors, WNDS, WNPS, RNPS);
END StudentOps;
/
CREATE OR REPLACE PACKAGE BODY StudentOps AS
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;
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;
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
Thanks,
John Hough Received on Tue Apr 22 1997 - 00:00:00 CDT