Using Pragma in Oracle Package

Question

I would like to create an Oracle package and its two functions: a public function ( function_public ) and a private ( function_private ). The public function uses closed in 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 you add WNDS, WNPS pragma to function_private . It seems to me that pragma can only be used in the package declaration, and not in the package body, so I should also declare function_private in the package:

 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. Is there a solution to add pragma to a function that can only be found in the package body?

UPDATE: Replaced the pseudocode with a working (simplified) example.

UPDATE2 : Fixed bugs in the code proposed by Rob van Wijk.

Answer 1

Your problem has nothing to do with PRAGMA. According to Rob, modern versions of Oracle handle most of this automatically.

The problem is that you cannot call private functions from an SQL statement, even those that are embedded in another routine in the same package. When PL / SQL executes SQL, it is passed the SQL engine for execution, and this essentially takes you beyond the scope of the package, so it does not have access to private members.

This compiles fine - no pragmas, but makes the "private" function publicly available:

 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 the function to be private, you need to check if you can rewrite the public function so that the private function is called 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 only declared in the package body, so its scope is limited only by other procedures in your package. Therefore, it will have to correspond to the purity level of these calling procedures, otherwise the compiler will throw an exception.

Compare this safe declaration (note I expanded the purity of 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 ...

 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 the procedures declared in the package specification can be used by other packages, even SQL statements, owned or executed by other users (schemas), which may not have access to our source code of the package body. Pragma is the method by which we offer them assurances regarding the impact of including our code in them. This is why a pragma must be declared in the specification because it is the only piece of code that opens when we provide EXECUTE in the package to another user.

change

And now, after seeing your revised code example, I understand what you are trying to do. It is not, it will not, it cannot work. We are allowed to use only batch functions that were declared in spec = public functions - in SQL. It doesn't matter if SQL is written in SQL * Plus or encoded in another packed procedure. The reason why it is clear enough 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 assigns ORA-00904: invalid identifier because the function is not declared in spec; it has nothing to do with purity levels,

area note

PL / SQL does not consist entirely of the rules for defining it : we can use private variables in our packed 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> 

These are simply functions and types that wm must declare in the specification if we want to use them in SQL statements.

Answer 3

You write "I would like to add WNDS, WNPS pragma ...". Why do you like it? Starting with version 9 (I think) Oracle does this for you. The only reason you can add the pragma yourself is when:

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

• you know what purity levels are needed for this use.

• you want to find compile time violations instead of runtime

The easiest option is to simply skip all the pragma declarations in general.

Having said that, you can omit the pragma limit_references in function_private by adding the keyword TRUST to the pragma limit_references of function_public.

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

Regards, Rob.

Answer 4

Oracle performs this check.

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

PLS-00452: Subroutine "FUNCTION_PUBLIC" violates the associated pragma

Remove 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;