Calling procedure inside a package returns an error-CodePudding

I'm trying to create a package with procedures to insert rows in my tables.

The package header:

CREATE OR REPLACE PACKAGE pkg_developers
AS
    PROCEDURE empty_tables;
    PROCEDURE add_city(country IN CITIES.COUNTRY%type, zipcode IN CITIES.ZIPCODE%type, city IN CITIES.CITY%type);
END pkg_developers;

package body:

CREATE OR REPLACE PACKAGE BODY pkg_developers AS

    PROCEDURE empty_tables
    IS
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE cities CASCADE';
        /* More truncate statements*/
        DBMS_OUTPUT.put_line('Tables truncated successfully.');
    END empty_tables;

    PROCEDURE add_city(p_country IN cities.COUNTRY%type, p_zipcode IN cities.ZIPCODE%type, p_city IN cities.CITY%type)
    IS
    BEGIN
        INSERT INTO CITIES(COUNTRY, ZIPCODE, city) VALUES(p_country, p_zipcode, p_city);
        COMMIT;
    END add_city;

END pkg_developers;

How I Execute the procedures:

BEGIN
    pkg_developers.empty_tables();
    pkg_developers.add_city('Country', '1111', 'City');
END;

The error I get:

[2022-04-16 11:41:48] [72000][4063]
[2022-04-16 11:41:48]   ORA-04063: package body "PROJECT.PKG_DEVELOPERS" has errors
[2022-04-16 11:41:48]   ORA-06508: PL/SQL: could not find program unit being called: "PROJECT.PKG_DEVELOPERS"
[2022-04-16 11:41:48]   ORA-06512: at line 2
[2022-04-16 11:41:48] Position: 0
[2022-04-16 11:41:48] Summary: 1 of 1 statements executed, 1 failed in 15 ms (106 symbols in file)

I did find out that if I remove the parameters for the procedure add_city and hardcode the values it does work, but I can't for the life of me figure out what would be wrong with the parameters, I've looked at examples online and they seem identical to me.

I've also tried to call the exact same procedure but not inside the package and then it works fine as well.

CodePudding user response：

Read what it says:

package body "PROJECT.PKG_DEVELOPERS" has errors

You can't call an invalid package (well, you can - as you already did - but you know the outcome).

So, what's wrong? At first sight, this:

Package specification says:

PROCEDURE add_city (country IN CITIES.COUNTRY%type, 
                    zipcode IN CITIES.ZIPCODE%type, 
                    city    IN CITIES.CITY%type);

Package body says:

PROCEDURE add_city (p_country IN cities.COUNTRY%type, 
                    p_zipcode IN cities.ZIPCODE%type, 
                    p_city    IN cities.CITY%type)

Find the difference. If you can't, then: in package body, all parameters have prefix p_ (which is correct, i.e. better than declaration in package specification). Whichever option you choose (I suggest the owe with a prefix), use it in both specification and body.