7

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

 3 years ago
source link: https://mikesmithers.wordpress.com/2017/11/30/pls-00231-the-best-laid-cunning-plans-and-private-package-functions/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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

Posted on November 30, 2017

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…

baldrick_fix.png?w=584

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK