Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00231 depending on function's access modifier
PLS-00231 depending on function's access modifier [message #306416] Fri, 14 March 2008 02:33 Go to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Hello,

The following problematic behavior has been observed. I'd appreciate an explanation.

A SHORT DESCRIPTION OF THE PROBLEMATIC BEHAVIOR:
A package function, used in an SQL statement inside the same package, generates a compilation error (PLS-00231) when it is private, but not so when public.

AN ILLUSTRATIVE CODE SNIPPET:
create or replace package PKG_TEST is

  procedure PrintTwo;

--THE PACKAGE FAILS TO COMPILE (WITH A PLS-00231 ERROR)
--WHEN THEN FOLLOWING LINE IS COMMENTED OUT,
--BUT COMPILES SUCCESSFULLY WHEN IT IS UNCOMMENTED.
  function Two return Number;

end PKG_TEST;
/
create or replace package body PKG_TEST is

  function Two
  return Number
  is
  begin
    return 2;
  end Two;
  
  procedure PrintTwo
  is
    myNum Number;
  begin
    select Two
    into myNum
    from dual;

    dbms_output.put_line(myNum);
  end PrintTwo;

end PKG_TEST;
/
Re: PLS-00231 depending on function's access modifier [message #306428 is a reply to message #306416] Fri, 14 March 2008 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How SQL engine which is external to the package can access a procedure that is private to the package?

And this is NOT an expert question.
A compilation error CAN'T be an expert question.

Regards
Michel

[Updated on: Fri, 14 March 2008 02:47]

Report message to a moderator

Re: PLS-00231 depending on function's access modifier [message #306430 is a reply to message #306416] Fri, 14 March 2008 03:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Most of the functions which are available in the sql engine is also available with the pl/sql engine. I know one function nvl2 is not available inside pl/sql engine. O.k coming to the point why do you want to do a context switch (pl/sql to sql engine) when you can do it inside the pl/sql itself. Something like this
  1  create or replace package PKG_TEST is
  2    procedure PrintTwo;
  3  --THE PACKAGE FAILS TO COMPILE (WITH A PLS-00231 ERROR)
  4  --WHEN THEN FOLLOWING LINE IS COMMENTED OUT,
  5  --BUT COMPILES SUCCESSFULLY WHEN IT IS UNCOMMENTED.
  6  --  function Two return Number;
  7* end PKG_TEST;
  8  /

Package created.


  1  create or replace package body PKG_TEST is
  2    function Two
  3    return Number
  4    is
  5    begin
  6      return 2;
  7    end Two;
  8    procedure PrintTwo
  9    is
 10      myNum Number;
 11    begin
 12      select Two()
 13      into myNum
 14      from dual;
 15      dbms_output.put_line(myNum);
 16    end PrintTwo;
 17* end PKG_TEST;
 18  /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY PKG_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/5     PL/SQL: SQL Statement ignored
12/12    PL/SQL: ORA-00904: : invalid identifier
12/12    PLS-00231: function 'TWO' may not be used in SQL
SQL> ed
Wrote file afiedt.buf

  1  create or replace package body PKG_TEST is
  2    function Two
  3    return Number
  4    is
  5    begin
  6      return 2;
  7    end Two;
  8    procedure PrintTwo
  9    is
 10      myNum Number;
 11    begin
 12  --    select Two()
 13  --    into myNum
 14  --    from dual;
 15        myNum := Two();
 16      dbms_output.put_line(myNum);
 17    end PrintTwo;
 18* end PKG_TEST;
SQL> /

Package body created.

SQL>

Regards

Raj
Re: PLS-00231 depending on function's access modifier [message #306431 is a reply to message #306430] Fri, 14 March 2008 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think this is just an example and the real purpose is not to get the value from dual but to use the function in a complex query.
In this case, the function must be public and not private.

Regards
Michel
Re: PLS-00231 depending on function's access modifier [message #306436 is a reply to message #306416] Fri, 14 March 2008 03:27 Go to previous messageGo to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Thanks, Michel and Raj.

Michel, i find your answer enlightening.

You are correct, Michel: the code snippet is just a simplistic model of the actual code i'm interested in. In the actual code, the function corresponding to "Two" is intimately coupled with the SQL statement in which it is used; they can't easily be separated.

Even though now i understand why the compiler behaves as it does, i think this behavior is undesireable. A public function should be interchangeable with an equivalent private function of the same package, inside the package where they are defined. At least, that's how access modifiers behave in object-oriented languages (e.g. C++, Java and C#).
Re: PLS-00231 depending on function's access modifier [message #306437 is a reply to message #306436] Fri, 14 March 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not quite true.
SQL is not part of the PL/SQL language it is outside PL/SQL.
It is like calling a procedure that is outside your program.

In any object language. If a procedure call a procedure that is in another package/class/... then this latter procedure can't call a private procedure of the first package/class/...
See a call to SQL as a call to another package not as the execution of a language statement.

Regards
Michel
Re: PLS-00231 depending on function's access modifier [message #306447 is a reply to message #306437] Fri, 14 March 2008 04:50 Go to previous message
itayb
Messages: 10
Registered: March 2008
Junior Member
This is a helpful way of thinking about it. Thanks.
Previous Topic: Job_queue_process
Next Topic: Get name of function
Goto Forum:
  


Current Time: Thu Dec 08 04:06:12 CST 2016

Total time taken to generate the page: 0.12371 seconds