Private Functions and ACCESSIBLE BY Packages in 12c

My recent post about PLS-00231 prompted an entirely reasonable question from Andrew :

“OK so the obvious question why [can’t you reference a private function in SQL] and doesn’t that defeat the objective of having it as a private function, and if so what about other ways of achieving the same goal ?”

I’ll be honest – that particular post was really just a note to self. I tend to write package members as public initially so that I can test them by calling them directly.
Once I’ve finished coding the package, I’ll then go through and make all of the helper package members private. My note was simply to remind myself that the PLS-00231 error when compiling a package usually means that I’ve referenced a function in a SQL statement and then made it private.

So, we know that a PL/SQL function can only be called in a SQL statement if it’s a schema level object or it’s definied in the package header because that’s the definition of a Public function in PL/SQL. Or at least it was…

In formulating an answer to Andrew’s question, it became apparent that the nature of Private functions have evolved a bit in 12c.

So, what I’m going to look at here is :

  • What are Private and Public package members in PL/SQL and why you might want to keep a package member private
  • How 12c language features change our definition of private and public in terms of PL/SQL objects
  • Hopefully provide some up-to-date answers for Andrew

Private and Public in the olden days

As most real-world PL/SQL functions are written within the context of a package, this is where we’ll focus our attention.

From the time that PL/SQL stored program units were introduced into Oracle, right up to and including 11g, the definition was simple.

A PL/SQL package member ( function or procedure) was public if it’s specification was declared in the package header.
Otherwise, it was private.
A private package member can only be referenced from inside it’s package.

A private package member might be used to encapsulate some functionality that is used in multiple places inside your package but not outside of it.
These “helper” functions tend to be quite common.
Another reason for using a private function would be to reduce clutter in the package signature. If your package is serving as an API to some business functionality, having few public members as entry points helps to ensure that the API is used as intended.

Of course, a private package member cannot be referenced in a SQL query, even from inside the package…

Changes in 12c and (probably) beyond

The ability to use PL/SQL constructs in SQL with clauses provided by 12c manages to take some of the certainty out of our definition of public and private. For example…

with function catchphrase return varchar2 is
    begin
        return 'I have a cunning plan which cannot fail';
    end;
select catchphrase 
from dual
/

…in 12c rewards you with :

CATCHPHRASE                                       
--------------------------------------------------
I have a cunning plan which cannot fail

Possibly more significant is the ability to create packages that are useable only by certain other stored program units using the ACCESSIBLE BY clause.

Using this new feature, we can split out our helper package members from the main package :

create or replace package baldrick_helper 
    accessible by (package baldrick)
as
    function catchphrase return varchar2;
end baldrick_helper;
/

create or replace package body baldrick_helper 
as    
    function catchphrase return varchar2
    is
    begin
        return 'I have a cunning plan which cannot fail';
    end catchphrase;
end baldrick_helper;
/

As well as reducing the size of individual packages, it should also mean that we can now reference the catchphrase function directly in a SQL statement right ? After all, it’s declared in the package header.

create or replace package baldrick 
as
    procedure cunning_plan;
end baldrick;
/

create or replace package body baldrick as
    procedure cunning_plan is
        optimism varchar2(100);
    begin
        select baldrick_helper.catchphrase
        into optimism
        from dual;
        
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

This compiles without error. However, when we try to run it we get :

set serveroutput on size unlimited
exec baldrick.cunning_plan;

ORA-06553: PLS-904: insufficient privilege to access object BALDRICK_HELPER
ORA-06512: at "MIKE.BALDRICK", line 5
ORA-06512: at line 1

Although the function is declared in the package header, it appears to remain private due to the use of the ACCESSIBLE BY whitelist. Therefore, if you want to reference it, you need to do it in straight PL/SQL :

create or replace package body baldrick as
    procedure cunning_plan is
        optimism varchar2(100);
    begin
    optimism := baldrick_helper.catchphrase;
        
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

This works as expected :

set serveroutput on size unlimited
exec baldrick.cunning_plan;

I have a cunning plan which cannot fail


PL/SQL procedure successfully completed.

Answers for Andrew

If your goal is to reference a PL/SQL package member in a SQL statement then it must be public.
In 12c this means it must be declared in the header of a package which is not defined using an ACCESSIBLE BY clause.

On the other hand, if your goal is to keep your package member private then you cannot reference it in a SQL statement.
In 12c, you do have the option of re-defining it in a with clause as mentioned earlier. However, this only works in straight SQL.
As far as code in a package is concerned, you can’t use an in-line with clause as a wrapper for the call to the private function like this…

create or replace package body baldrick as
    procedure cunning_plan is
        optimism varchar2(100);
    begin
        with function cheating return varchar2 is
        begin 
            return baldrick_helper.catchphrase;
        end;     
        begin
        select catchphrase
        into optimism
        from dual;
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

…because it’s not currently supported in PL/SQL.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.