12.2: Enhanced ACCESSIBLE BY Options

View All Scripts Login to Run Script

Script Name 12.2: Enhanced ACCESSIBLE BY Options
Description The ACCESSIBLE BY clause restricts access to units and subprograms by other units. The accessor list, also known as the white list, explicitly lists those units which may have access. In 12.2, this feature was enhanced to be applied to subprograms in packages. You can also specify the type or "unit kind" to which the whitelist applies. This is useful when you have triggers and PL/SQL program units of the same name. Doc: http://docs.oracle.com/database/122/LNPLS/ACCESSIBLE-BY-clause.htm
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Wednesday February 15, 2017

Statement 1

CREATE TABLE my_data (n NUMBER)

Table created.

Statement 2

In 12.2, you can now apply the ACCESSIBLE BY clause to a subprogram of a package. In addition, you can qualify the name of the unit with its type, as in "PROCEDURE ". This can come in handy if you happen to have program units with the same name as triggers (which, unfortunately, do not share the same namespace). Note, however, that within the ACCESSIBLE BY list, you can only have schema-level program unit names, not subprograms.

Apply ACCESSIBLE BY To Subprograms and Specify "Unit Kind"

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   PROCEDURE do_this; 
 
   PROCEDURE this_for_proc_only 
      ACCESSIBLE BY (PROCEDURE generic_name); 
 
   PROCEDURE this_for_trigger_only 
      ACCESSIBLE BY (TRIGGER generic_name); 
 
   PROCEDURE this_for_any_generic_name 
      ACCESSIBLE BY (generic_name); 
 
   /* This will not work - only program units can be named, not subprograms */ 
   PROCEDURE this_for_pkgd_proc1_only 
      ACCESSIBLE BY (PROCEDURE pkg1.myproc1); 
END;

Package created.

Statement 3

Remember: the ACCESSIBLE BY clauses in the package specification must be repeated in the body.

CREATE OR REPLACE PACKAGE BODY pkg 
IS 
   PROCEDURE do_this 
   IS 
   BEGIN 
      NULL; 
   END; 
 
   PROCEDURE this_for_proc_only 
      ACCESSIBLE BY (PROCEDURE generic_name) 
   IS 
   BEGIN 
      NULL; 
   END; 
 
   PROCEDURE this_for_trigger_only 
      ACCESSIBLE BY (TRIGGER generic_name) 
   IS 
   BEGIN 
      NULL; 
   END; 
 
   PROCEDURE this_for_any_generic_name 
      ACCESSIBLE BY (generic_name) 
   IS 
   BEGIN 
      NULL; 
   END; 
 
   PROCEDURE this_for_pkgd_proc1_only 
      ACCESSIBLE BY (PROCEDURE pkg1.myproc1) 
   IS 
   BEGIN 
      NULL; 
   END; 
END;

Package Body created.

Statement 4

Works just fine.

Trigger Invokes Trigger-Only Procedure

CREATE OR REPLACE TRIGGER generic_name 
   BEFORE INSERT 
   ON my_data 
   FOR EACH ROW 
DECLARE 
BEGIN 
   pkg.this_for_trigger_only; 
END;

Trigger created.

Statement 5
Sorry, no go. You should see a PLS-00904 error here. I am not sure why LiveSQL is not currently (Feb 2017) showing you the error information.
Trigger Invokes Procedure-Only Procedure
```
CREATE OR REPLACE TRIGGER generic_name 
   BEFORE INSERT 
   ON my_data 
   FOR EACH ROW 
DECLARE 
BEGIN 
   pkg.this_for_proc_only; 
END;
```
```
Error at line: 26
```
More Details: https://docs.oracle.com/error-help/db/ora-24344
Statement 6
That's just too many "Procedures" in a title, but at least I get a clean compile. :-)
Procedure Invokes Procedure-only Procedure
```
CREATE OR REPLACE PROCEDURE generic_name 
   AUTHID DEFINER 
IS 
BEGIN 
   pkg.this_for_proc_only; 
END;
```
```
Procedure created.
```

Statement 7

And PL/SQL says "No!"

Procedure Invokes Trigger-only Procedure

CREATE OR REPLACE PROCEDURE generic_name 
   AUTHID DEFINER 
IS 
BEGIN 
   pkg.this_for_trigger_only; 
END;

Errors: PROCEDURE GENERIC_NAME
Line: 5 PLS-00904: insufficient privilege to access object THIS_FOR_TRIGGER_ONLY

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 8
The ACCESSIBLE BY clause did not restrict to a "unit kind" (type). So both the trigger and procedure of the name "GENERIC_NAME" can invoke the procedure.
Trigger Can Invoke "Any Kind" Procedure
```
CREATE OR REPLACE TRIGGER generic_name 
   BEFORE INSERT 
   ON my_data 
   FOR EACH ROW 
DECLARE 
BEGIN 
   pkg.this_for_any_generic_name; 
END;
```
```
Trigger created.
```
Statement 9
The ACCESSIBLE BY clause did not restrict to a "unit kind" (type). So both the trigger and procedure of the name "GENERIC_NAME" can invoke the procedure.
Procedure Can Invoke "Any Kind" Procedure
```
CREATE OR REPLACE PROCEDURE generic_name 
   AUTHID DEFINER 
IS 
BEGIN 
   pkg.this_for_any_generic_name; 
END;
```
```
Procedure created.
```

Statement 10

CREATE OR REPLACE PACKAGE pkg1
   AUTHID DEFINER
IS
   PROCEDURE myproc1;

   PROCEDURE myproc2;
END;

Package created.

Statement 11
This header in the pkg package "PROCEDURE this_for_pkgd_proc1_only ACCESSIBLE BY (PROCEDURE pkg1.myproc1);" does not stop successful complilation, but that format (PROCEDURE .) is not supported and so the pkg1 body does not compile.
ACCESSIBLE BY Cannot Restrict to Subprogram.
```
CREATE OR REPLACE PACKAGE BODY pkg1 
IS 
   PROCEDURE myproc1 
   IS 
   BEGIN 
      pkg.this_for_pkgd_proc1_only; 
   END; 
 
   PROCEDURE myproc2 
   IS 
   BEGIN 
      NULL; 
   END; 
END;
```
```
Errors: PACKAGE BODY PKG1
Line: 6 PLS-00904: insufficient privilege to access object THIS_FOR_PKGD_PROC1_ONLY
```
More Details: https://docs.oracle.com/error-help/db/ora-24344

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database