[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

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

0
claghorn
Asked:
claghorn
  • 3
  • 2
3 Solutions
 
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

0
 
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;
0
 
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
remove calls_still_unhandled from the spec
0
 
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.
0
 
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now