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

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;

Answer 2

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.