Oracle Package Construction

I see a code sample from a well known PL/SQL book author.
He has a package that consists of one procedure and a few procedures / functions inside that procedure. He uses the main procedure's execution section to call the inner procedures and functions. But it does not compile. Saying in my shortened example:
Error(6,12): PLS-00323: subprogram or cursor 'CALLS_STILL_UNHANDLED' is declared in a package specification and must be defined in the package body.
Is this possible in PL/SQL. If so, how do you declare it in the spec and body?
--SPEC
CREATE OR REPLACE
PACKAGE call_support_pkg
IS

PROCEDURE distribute_calls(
    department_id_in IN NUMBER);

FUNCTION calls_still_unhandled
    RETURN BOOLEAN;
END call_support_pkg;

--BODY:
create or replace
PACKAGE BODY call_support_pkg
IS

PROCEDURE distribute_calls(
    department_id_in IN NUMBER )
    IS
    l_case_id NUMBER;

    FUNCTION calls_still_unhandled
       RETURN BOOLEAN
       IS
       BEGIN
          NULL;
       END calls_still_unhandled;

BEGIN
  NULL;
END distribute_calls;

END call_support_pkg;

Open in new window

claghornAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The way you have the BODY set up the function is inside the procedure.

I'm nost sure what the correct way is because I don't have the book but this fixes the error and it compiles.
--SPEC
CREATE OR REPLACE
PACKAGE call_support_pkg
IS

PROCEDURE distribute_calls(
    department_id_in IN NUMBER);

FUNCTION calls_still_unhandled
    RETURN BOOLEAN;
END call_support_pkg;
/

show errors

--BODY:
create or replace
PACKAGE BODY call_support_pkg
IS


    FUNCTION calls_still_unhandled
       RETURN BOOLEAN
       IS
       BEGIN
          NULL;
       END calls_still_unhandled;

PROCEDURE distribute_calls(
    department_id_in IN NUMBER )
    IS
    l_case_id NUMBER;
BEGIN
  NULL;
END distribute_calls;

END call_support_pkg;
/

show errors

Open in new window

sdstuberCommented:
you have calls_still_unhandled declared inside the distribute_calls procedure

move it external to that procedure

something like this...


CREATE OR REPLACE PACKAGE BODY call_support_pkg
IS
    FUNCTION calls_still_unhandled
        RETURN BOOLEAN
    IS
    BEGIN
        NULL;
    END calls_still_unhandled;

    PROCEDURE distribute_calls(department_id_in IN NUMBER)
    IS
        l_case_id   NUMBER;
    BEGIN
        NULL;
    END distribute_calls;
END call_support_pkg;
claghornAuthor Commented:
Here is the actual example on pg 1 and 2. How would the spec look to make this compile?
http://www.docstoc.com/docs/49985361/Automated-Refactoring-Arrives
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
remove calls_still_unhandled from the spec
slightwv (䄆 Netminder) Commented:
It looks like I have to register to download the doc and I might have scanned over the code you posted but anyway;

The spec (top create package statement) and the body need to match.

In the code you posted inside the spec the function and procedure are separate.  In the body the function is declared inside the procedure (a local function to that procedure).

They need to match.  In the code we posted look how we moved the procedure declaration below the function.
sdstuberCommented:
the package only has one procedure in it,  everything else is sub-routine of that one procedure

so your spec just exposes that one procedure too

CREATE OR REPLACE PACKAGE call_support_pkg
IS
    PROCEDURE distribute_calls(department_id_in IN employees.department_id%TYPE);
END call_support_pkg;
/


note, I changed the parameter type to match the code in the article  not the NUMBER type you have in your code above,  but the idea is the same

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.