Create a package with only specification?

Question

The code listed below creates a package with only a specification. I keep getting an error.

Error:

Error: PL/SQL: Compilation unit analysis terminated
Error(1,14): PLS-00201: identifier 'TAXRATE_PKG' must be declared                        
Error(1,14): PLS-00304: cannot compile body of 'TAXRATE_PKG' without its specification

Code:

CREATE OR REPLACE PACKAGE BODY TAXRATE_PKG IS 

    PROCEDURE state_tax_pf(
                    p_state IN VARCHAR2,
                    pv_tax_nc OUT NUMBER, 
                    pv_tax_tx OUT NUMBER, 
                    pv_tax_tn OUT NUMBER) 
    IS 
        lv_state NUMBER; 
    BEGIN 
       IF p_state = 'NC' THEN 
           pv_tax_nc := 0.35; 
       ELSIF p_state = 'TX' THEN 
           Pv_tax_tx := 0.05; 
       ELSIF p_state = 'TN' THEN 
           pv_tax_tn := 0.02; 
       END IF; 

       RETURN lv_state; 

    END; 
END;

Answer 1

The code listed below creates a package with only a specification

but your code shows that you are creating a package BODY

CREATE OR REPLACE PACKAGE 
    BODY TAXRATE_PKG IS  -- your code doesnt follow what you are trying to do

    PROCEDURE state_tax_pf(
                    p_state IN VARCHAR2,
                    pv_tax_nc OUT NUMBER, 
                    pv_tax_tx OUT NUMBER, 
                    pv_tax_tn OUT NUMBER) 
    IS 
        lv_state NUMBER; 
    BEGIN 
       IF p_state = 'NC' THEN 
           pv_tax_nc := 0.35; 
       ELSIF p_state = 'TX' THEN 
           Pv_tax_tx := 0.05; 
       ELSIF p_state = 'TN' THEN 
           pv_tax_tn := 0.02; 
       END IF; 

       RETURN lv_state; 

    END; 
END;

A PACKAGE SPECIFICATION

• contains only the first line of your PROCEDURES/FUNCTIONS,

• It starts with CREATE OR REPLACE PACKAGE and doesnt include the word "BODY" since its the specification and not the BODY of the package.

While a PACKAGE BODY

• contains the BODY of the procedures/functions starting from the word "PROCEDURE" up to the END statement of the procedure.

If you want to create a package with only specification. You should write it like this:

CREATE OR REPLACE PACKAGE 
     TAXRATE_PKG IS

PROCEDURE state_tax_pf(
                    p_state IN VARCHAR2,
                    pv_tax_nc OUT NUMBER, 
                    pv_tax_tx OUT NUMBER, 
                    pv_tax_tn OUT NUMBER) ;

END TAXRATE_PKG;

Just to warn you, you have a procedure inside but you are returning a value.This will return an error. You should make sure that the codes you are inserting in a package works to avoid having too many errors.

Answer 2

There are some problems with your code. First your error is because you are creating a PACKAGE and in Oracle to do so you have to create the specification first. It is like an Interface in Java or a Header file in C. So, for your code get rid of that error you have to do:

-- This is the specification
CREATE OR REPLACE PACKAGE TAXRATE_PKG IS
    PROCEDURE state_tax_pf( p_state IN VARCHAR2,
                            pv_tax_nc OUT NUMBER, 
                            pv_tax_tx OUT NUMBER, 
                            pv_tax_tn OUT NUMBER);
END TAXRATE_PKG;

--This is the definition
CREATE OR REPLACE PACKAGE BODY TAXRATE_PKG IS 
    PROCEDURE state_tax_pf(
                    p_state IN VARCHAR2,
                    pv_tax_nc OUT NUMBER, 
                    pv_tax_tx OUT NUMBER, 
                    pv_tax_tn OUT NUMBER) 
    IS 
        lv_state NUMBER; 
    BEGIN 
       IF p_state = 'NC' THEN 
           pv_tax_nc := 0.35; 
       ELSIF p_state = 'TX' THEN 
           Pv_tax_tx := 0.05; 
       ELSIF p_state = 'TN' THEN 
           pv_tax_tn := 0.02; 
       END IF; 

       RETURN lv_state; 

    END; 
END TAXRATE_PKG;

The other problems you have in your code is more like logic problems.

You defined a procedure but you are returning a value RETURN lv_state; and you didn't even assign any value to it that makes no sense. No need for that return statement or that variable since you are not using it at all.

Another problem is you created three OUT parameters (unless the code that use this is testing any of that other values for null) that isn't assigned (because of your if) you just need one of then lets say pv_tax OUT NUMBER.

The other problem that I see is that if you need that RETURN lv_state; for something you have to change your procedure into a function and assign that variable to something.

Hope it Helps you to understand.