Using Pragma in Oracle Package Body

Question

I'd like to create an Oracle Package and two functions in it: A public function ( function_public ) and a private one ( function_private ). The public function uses the private one in an sql statement.

Without pragma the code does not compile (PLS-00231: function 'FUNCTION_PRIVATE' may not be used in SQL)

CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;

CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
     return 'z';
  END;

  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
  ret VARCHAR2(100);
  BEGIN
     SELECT 'x' || function_private(x) INTO ret FROM dual;
     return ret;
  END;
END PRAGMA_TEST;

The code compiles if I add WNDS, WNPS pragma to function_private. It seems to me pragma can be used only in the package declaration, and not in package body, so I have to declare function_private in the package as well:

CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
  FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES( function_private, WNDS, WNPS);
  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;

CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
     return 'z';
  END;

  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
  ret VARCHAR2(100);
  BEGIN
     SELECT 'x' || function_private(x) INTO ret FROM dual;
     return ret;
  END;
END PRAGMA_TEST;

This solution makes my function_private public as well. Is there a solution to add pragma to a function which can be found only in the package body?

UPDATE: Replaced the pseudo-code with a working (simplified) example.

UPDATE2: Bugfixes in the code as suggested by Rob van Wijk.

Answer 1

Oracle does this check.

The following code does not compile, since function_public has the pragma RNDS, and it calls function_private which reads a table.

PLS-00452: Subprogram 'FUNCTION_PUBLIC' violates its associated pragma

Remove the SELECT from function_private and it works.

---

CREATE OR REPLACE PACKAGE pragma_test AS
  FUNCTION function_public RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES( function_public, RNDS );
END pragma_test;

CREATE OR REPLACE PACKAGE BODY pragma_test AS
  FUNCTION function_private RETURN VARCHAR2 IS
    v_return dual.dummy%TYPE;
  BEGIN
     SELECT dummy INTO v_return FROM dual;
     RETURN v_return;
  END;
  --
  FUNCTION function_public RETURN VARCHAR2 IS
    v_return dual.dummy%TYPE;
  BEGIN
     RETURN function_private;
  END;
END pragma_test;

Answer 2

You write "I'd like to add WNDS, WNPS pragma ...". Why do you like that? Since version 9 (I think) Oracle does this check for you. The only reason you might want to add the pragma yourself, is when:

• you know where in a SQL statement you want to use the function AND

• you know which purity levels are required for this use AND

• you want to find violations at compile time instead of run time

The easiest option is to just skip all the pragma declarations altogether.

Having said that, you can omit the restrict_references pragma to function_private if you add the TRUST keyword to the restrict_references pragma of function_public.

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#21958

Regards, Rob.

Answer 3

Your function_private is only declared in the package body, so its scope is restricted only to other procedures in your package. Consequently it will have to conform to the purity level of those calling procedures or else the compiler will hurl a exception.

Compare this safe declaration (note, I have extended the purity of the function_public) ...

SQL> CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
  2    FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
  3    PRAGMA RESTRICT_REFERENCES( function_public, WNDS, WNPS, RNDS);
  4  END PRAGMA_TEST;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  2    FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  3    BEGIN
  4       return 'no harm done';
  5    END;
  6
  7    FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
  8    BEGIN
  9       return function_private(x);
 10    END;
 11  END PRAGMA_TEST;
 12  /

Package body created.

SQL>

... with this unsafe one ...

SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  2    FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  3       rv varchar2(1);
  4    BEGIN
  5       select dummy into rv from dual;
  6       return rv;
  7    END;
  8
  9    FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
 10    BEGIN
 11       return function_private(x);
 12    END;
 13  END PRAGMA_TEST;
 14  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY PRAGMA_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3      PLS-00452: Subprogram 'FUNCTION_PUBLIC' violates its associated
         pragma

SQL>

The point of the RESTRICTS_REFERENCES pragma is that procedures declared in the package spec can be used by other packages, even SQL statements, owned or executed by other users (schemas), who might not have access to our package body's source. The pragma is a method by which we offer assurances to them about the impact of incorporating our code in theirs. That's why the pragma has to be declared in the spec, because that is the only part of the code exposed when we grant EXECUTE on the package to another user.

edit

Ah, having now seen your revised code example I understand what you're trying to do. It doesn't, won't, can't work. We are only allowed to use packaged functions which have been declared in the spec = public functions - in SQL. It doesn't matter whether the SQL is written in SQL*Plus or coded in another packaged procedure. The reason why is quite clear in the error stack:

SQL> CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
  2        FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
  3        PRAGMA RESTRICT_REFERENCES( function_public, WNDS, WNPS);
  4  END PRAGMA_TEST;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  2    FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  3       rv varchar2(1);
  4    BEGIN
  5       select dummy into rv from dual;
  6       return rv;
  7    END;
  8
  9    FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
 10       rv varchar2(1);
 11    BEGIN
 12       select function_private(x) into rv from dual;
 13       return rv;
 14    END;
 15  END PRAGMA_TEST;
 16  /

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY PRAGMA_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/6     PL/SQL: SQL Statement ignored
12/13    PL/SQL: ORA-00904: : invalid identifier
12/13    PLS-00231: function 'FUNCTION_PRIVATE' may not be used in SQL
SQL>

The compiler hurls ORA-00904: invalid identifier because the function is not declared in the spec; it has nothing to do with purity levels,

a note about scope

PL/SQL is not entirely consist with regards to its scoping rules: we can use private variables in our packaged SQL statement:

SQL> CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  2
  3    gv constant varchar2(8) := 'global';
  4
  5    FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  6       rv varchar2(1);
  7    BEGIN
  8       select dummy into rv from dual;
  9       return rv;
 10    END;
 11
 12    FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
 13       rv varchar2(10);
 14    BEGIN
 15       select gv||'+'||dummy into rv from dual;
 16       return rv;
 17    END;
 18  END PRAGMA_TEST;
 19  /

Package body created.

SQL>

It's just functions and types which wm must declare in the spec if we want to use them in SQL statements.

Answer 4

Your problem has nothing to do with PRAGMAs. As Rob says, modern Oracle versions handle most of this automatically.

The problem is you can't call private functions from a SQL statement, even ones embedded in another subprogram within the same package. When PL/SQL executes SQL, it is handed off to the SQL engine for execution, and that essentially takes you outside the scope of the package, so it has no access to private members.

This compiles fine -- no pragmas, but making the "private" function public:

CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;


CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
     return 'z';
  END;

  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
     ret VARCHAR2(30);
  BEGIN
     SELECT 'x' || function_private(x) INTO ret FROM dual;
     RETURN ret;
  END;
END PRAGMA_TEST;

If you want to keep the function private, you need to see if you can rewrite the public function in such a way that the call to the private function is done outside the SQL statement:

CREATE OR REPLACE PACKAGE PRAGMA_TEST AS
  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2;
END PRAGMA_TEST;


CREATE OR REPLACE PACKAGE BODY PRAGMA_TEST AS
  FUNCTION function_private(y IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
     return 'z';
  END;

  FUNCTION function_public(x IN VARCHAR2) RETURN VARCHAR2 IS
     ret VARCHAR2(30);
  BEGIN
     ret := function_private(x);
     SELECT 'x' || ret INTO ret FROM dual;
     RETURN ret;
  END;
END PRAGMA_TEST;