the_martian the_martian - 17 days ago 9
SQL Question

SQL Specs and Packages

So I know that if your define something in the spec, that it must be implemented in the body. Is it possible to define a method in the body, but not the spec.

I'm new to SQL--I hope this question makes sense.

Answer

Yes, a procedure or function can be defined in a package body but not in a package specification. Objects in the specification are public, objects only in the body are private to that package.

Creating the package specification and body

create or replace package test_package is
    procedure public_procedure;
end;
/

create or replace package body test_package is
    procedure private_procedure is
    begin
        null;
    end;

    procedure public_procedure is
    begin
        private_procedure;
    end;
end;
/

How to call the package

--WORKS:
begin
    test_package.public_procedure;
end;
/

--FAILS WITH THIS ERROR:
--  ORA-06550: line 2, column 15:
--  PLS-00302: component 'PRIVATE_PROCEDURE' must be declared
--  ORA-06550: line 2, column 2:
begin
    test_package.private_procedure;
end;
/

Why would you need to do this?

In a language like Java this question would probably be closed for being too obvious. But this is an understandable question in PL/SQL, since most PL/SQL programmers make everything public. Looking at an average PL/SQL program it's reasonable to say to yourself: "there must be a good reason for this."

There isn't.

Private should be the default. Only make something public if you need to share it with other objects and programmers. Or if you need to have something persist during a session.

Use private procedures and functions to hide as many details as possible. This makes packages simpler. It also gives other programs and programmers fewer ways to abuse your package.