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;