PLS-00231 – The best laid (cunning) plans and private package functions

There are times when I feel like Baldrick.
One moment I’m all boundless optimism and cunning plans and the next, I’m so confused I don’t know what my name is or where I live.
One such recent bout of existential uncertainty was caused by the error mentioned in the title of this post, or to give it it’s full name :

PLS-00231 : Function <function name> may not be used in SQL

So, from the beginning…

Let’s start with (appropriately enough) a simple package header :

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

No problems there, it compiles fine as you’d expect.
Now for the body…

create or replace package body baldrick as
    -- Private
    function catchphrase return varchar2 is
    begin
        return 'I have a cunning plan which cannot fail';
    end catchphrase;
    
    -- Public
    procedure cunning_plan is
        optimism varchar2(4000);
    begin
        select catchphrase
        into optimism
        from dual;
    
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

That looks fine, right?
I mean, sure, the CATCHPHRASE function is private so it can only be referenced from inside the package but that’s not unusual, is it ?
Well, it turns out that Oracle isn’t entirely happy about this and says so at compile time…

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/9     PL/SQL: SQL Statement ignored
12/16    PL/SQL: ORA-00904: "CATCHPHRASE": invalid identifier
12/16    PLS-00231: function 'CATCHPHRASE' may not be used in SQL

After some head-scratching, I was beginning to worry that I was losing it. Maybe I should apply for that job as Village Idiot of Kensington.
Fortunately, I was saved from a career of Professional Idiocy in West London by the simple expedient of making the function public…

create or replace package baldrick as
    function catchphrase return varchar2;
    procedure cunning_plan;
end baldrick;
/

Re-creating the package header using this code, we can now see that the package body magically compiles and works without further complaint…

Cunning Plans are here again !

To discover why this happens may not require a plan more cunning than a Fox who has just been made Professor of Cunning at Oxford University but it’s my turn to cook.
So, now my code is working, I’m off to prepare the Turnip Surprise.

Advertisements

3 thoughts on “PLS-00231 – The best laid (cunning) plans and private package functions

  1. OK so the obvious question why 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

    • Andrew,
      Here’s the newly appointed Professor of Cunning’s answer :

      https://platform.twitter.com/widgets.js

      As to your second question – how can we keep the function private but still use it, the short answer is not to reference it an a SQL query.
      This is something that I’ll explore a bit more fully in a follow-up post – mainly because it got a bit more involved than I thought it would when I tried to come up with a short answer.

      Mike

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.