Search code examples
stored-proceduresplsqloracle-sqldeveloper

What does the pale / light icon of the procedure mean?

I compiled a package in Oracle SQL Developer and some of my procedures have a rather pale / light colored icon (I am not allowed to add a picture). I am not really familiar with Oracle SQL Developer and googled around but couldn't find any list or legend of all symbols. What does it mean when the procedure has the light icon instead of the clearly colored icon?

Checked help center and hovered over the icon, but didn't give any helpful information. Other stack overflow questions only explain the green dot / bug icon meaning.

Solution

  • If you're looking at the object viewer, private procedures (and functions) are shown with a paler icon than public ones.

    Public procedures and functions are those that are declared in the package specification and then defined in the package body. You can call those from outside the package.

    Private procedures and functions are only declared and defined in the package body, and do not appear in the specification. Those cal only be called from within the package, not by anything outside it.

    Read more in the documentation.

    As a simple example, if I create a dummy package with:

    create or replace package test_package as

procedure public_procedure;

end test_package;
/

create or replace package body test_package as

-- forward declaration of private procedure
procedure private_procedure;

-- public (declared in specification) so visible externally
procedure public_procedure is
begin
  private_procedure;
end public_procedure;

-- private so not visible externally
procedure private_procedure is
begin
  null;
end private_procedure;

end test_package;
/

    then the object viewer shows the public procedure with a 'normal' icon in both the specification and body, and the private function declaration and definition as paler icons, and only in the body:

    enter image description here

    (You don't usually need to declare private procedures at all, but you do if the definition appears after the first call in the package body, so I've set my code up like that to demonstrate that both are shown with pale icons.)

    I can call the public procedure from an anonymous block:

    exec test_package.public_procedure;

PL/SQL procedure successfully completed.

    and attempting to call the private one fails as expected:

    exec test_package.private_procedure;

BEGIN test_package.private_procedure; END;
Error report -
ORA-06550: line 1, column 20:
PLS-00302: component 'PRIVATE_PROCEDURE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored