ORA-06550 Identifier must Be Declared

Question

I'm new to Oracle/PLSQL. I'm trying to write a package. I'm using Oracle SQL Developer.

Everything looks fine in SQL Developer, but when I try to use the package via PHP/PDO, I get the following:

Warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[HY000]: General error: 6550 OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00201: identifier 'SURVEY_TESTER.ADD_MBN_RECORD' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored (/core-php-src-5.2.5/php-5.2.5/ext/pdo_oci/oci_statement.c:146) in /home/robert/www/prod/htdocs/intra/__SURVEY_RECORD.php on line 47

My package declaration:

CREATE OR REPLACE 
PACKAGE SURVEY_TESTER AS 

  PROCEDURE ADD_MBN_RECORD(
    iMAILMONTH        IN MASTERBARCODEDNAMES14.MAILMONTH%TYPE,
    iZIPGROUP           IN MASTERBARCODEDNAMES14.ZIPGROUP%TYPE,
    iFIRST            IN MASTERBARCODEDNAMES14.FIRST%TYPE,
    iLAST               IN MASTERBARCODEDNAMES14.LAST%TYPE,
    iADDRESS            IN MASTERBARCODEDNAMES14.ADDRESS%TYPE,
    iCITY               IN MASTERBARCODEDNAMES14.CITY%TYPE,
    iST               IN MASTERBARCODEDNAMES14.ST%TYPE,
    iZIP                IN MASTERBARCODEDNAMES14.ZIP%TYPE,
    iSFDU               IN MASTERBARCODEDNAMES14.SFDU%TYPE,
    iSOURCE           IN MASTERBARCODEDNAMES14.SOURCE%TYPE,
    iSOURCECODE       IN MASTERBARCODEDNAMES14.SOURCECODE%TYPE,
    iNAMEMONTHMATCH   IN MASTERBARCODEDNAMES14.NAMEMONTHMATCH%TYPE,
    iCOMPLETED_SURVEY   IN MASTERBARCODEDNAMES14.COMPLETED_SURVEY%TYPE,
    iNSCFADDR           IN MASTERBARCODEDNAMES14.NSCFADDR%TYPE,
    iZIPADDRAPT       IN MASTERBARCODEDNAMES14.ZIPADDRAPT%TYPE
  );

  PROCEDURE ADD_RLI_RECORD(
    iMAILMONTH  IN RETAIL_LINE_ITEM.MAILMONTH%TYPE,
    iSPONSORID  IN RETAIL_LINE_ITEM.SPONSORID%TYPE,
    iDEALERID   IN RETAIL_LINE_ITEM.DEALERID%TYPE,
    iZIPCODE    IN RETAIL_LINE_ITEM.ZIPCODE%TYPE,
    iNAMES  IN RETAIL_LINE_ITEM.NAMES%TYPE,
    iZIPRATE    IN RETAIL_LINE_ITEM.ZIPRATE%TYPE,
    iZIPTOTAL   IN RETAIL_LINE_ITEM.ZIPTOTAL%TYPE,
    iMAILING    IN RETAIL_LINE_ITEM.MAILING%TYPE,
    iCRRT   IN RETAIL_LINE_ITEM.CRRT%TYPE
  );

END SURVEY_TESTER;
/

My package body:

CREATE OR REPLACE PACKAGE BODY SURVEY_TESTER AS

  PROCEDURE ADD_MBN_RECORD(
    iMAILMONTH        IN MASTERBARCODEDNAMES14.MAILMONTH%TYPE,
    iZIPGROUP           IN MASTERBARCODEDNAMES14.ZIPGROUP%TYPE,
    iFIRST            IN MASTERBARCODEDNAMES14.FIRST%TYPE,
    iLAST               IN MASTERBARCODEDNAMES14.LAST%TYPE,
    iADDRESS            IN MASTERBARCODEDNAMES14.ADDRESS%TYPE,
    iCITY               IN MASTERBARCODEDNAMES14.CITY%TYPE,
    iST               IN MASTERBARCODEDNAMES14.ST%TYPE,
    iZIP                IN MASTERBARCODEDNAMES14.ZIP%TYPE,
    iSFDU               IN MASTERBARCODEDNAMES14.SFDU%TYPE,
    iSOURCE           IN MASTERBARCODEDNAMES14.SOURCE%TYPE,
    iSOURCECODE       IN MASTERBARCODEDNAMES14.SOURCECODE%TYPE,
    iNAMEMONTHMATCH   IN MASTERBARCODEDNAMES14.NAMEMONTHMATCH%TYPE,
    iCOMPLETED_SURVEY   IN MASTERBARCODEDNAMES14.COMPLETED_SURVEY%TYPE,
    iNSCFADDR           IN MASTERBARCODEDNAMES14.NSCFADDR%TYPE,
    iZIPADDRAPT       IN MASTERBARCODEDNAMES14.ZIPADDRAPT%TYPE
  ) IS
  BEGIN
    INSERT INTO MASTERBARCODEDNAMES14 (
        MAILMONTH,
        ZIPGROUP,
        FIRST,
        LAST,
        ADDRESS,
        CITY,
        ST,
        ZIP,
        SFDU,
        SOURCE,
        SOURCECODE,
        NAMEMONTHMATCH,
        COMPLETED_SURVEY,
        NSCFADDR,
        ZIPADDRAPT
    ) VALUES (
        iMAILMONTH, 
        iZIPGROUP, 
        iFIRST, 
        iLAST, 
        iADDRESS, 
        iCITY, 
        iST, 
        iZIP, 
        iSFDU, 
        iSOURCE, 
        iSOURCECODE, 
        iNAMEMONTHMATCH, 
        iCOMPLETED_SURVEY, 
        iNSCFADDR, 
        iZIPADDRAPT
    );
  END ADD_MBN_RECORD;


  PROCEDURE ADD_RLI_RECORD(
    iMAILMONTH  IN RETAIL_LINE_ITEM.MAILMONTH%TYPE,
    iSPONSORID  IN RETAIL_LINE_ITEM.SPONSORID%TYPE,
    iDEALERID   IN RETAIL_LINE_ITEM.DEALERID%TYPE,
    iZIPCODE    IN RETAIL_LINE_ITEM.ZIPCODE%TYPE,
    iNAMES  IN RETAIL_LINE_ITEM.NAMES%TYPE,
    iZIPRATE    IN RETAIL_LINE_ITEM.ZIPRATE%TYPE,
    iZIPTOTAL   IN RETAIL_LINE_ITEM.ZIPTOTAL%TYPE,
    iMAILING    IN RETAIL_LINE_ITEM.MAILING%TYPE,
    iCRRT   IN RETAIL_LINE_ITEM.CRRT%TYPE
  ) IS
  BEGIN
    INSERT INTO RETAIL_LINE_ITEM (
        MAILMONTH, 
        SPONSORID, 
        DEALERID, 
        ZIPCODE, 
        NAMES, 
        ZIPRATE, 
        ZIPTOTAL, 
        MAILING, 
        CRRT
    ) values (
        iMAILMONTH, 
        iSPONSORID, 
        iDEALERID, 
        iZIPCODE, 
        iNAMES, 
        iZIPRATE, 
        iZIPTOTAL, 
        iMAILING, 
        iCRRT
    );
  END ADD_RLI_RECORD;

END SURVEY_TESTER;
/

And, here's the gist of my PHP. Assume the generated SQL is fine.

$sql = "begin SURVEY_TESTER.ADD_".$table."_RECORD(:".implode(", :", array_keys($data))."); end;";

Here's a var_dump. In case it helps.

string 'begin SURVEY_TESTER.ADD_RLI_RECORD(:mailmonth, :sponsorid, :dealerid, :zipcode, :names, :ziprate, :ziptotal, :mailing, :crrt); end;' (length=131)

array
  ':mailmonth' => string '0715' (length=4)
  ':sponsorid' => string '121266' (length=6)
  ':dealerid' => string 'COFL' (length=4)
  ':zipcode' => string '34683' (length=5)
  ':names' => string '100' (length=3)
  ':ziprate' => string '0.56' (length=4)
  ':ziptotal' => string '24.75' (length=5)
  ':mailing' => string '201507' (length=6)
  ':crrt' => string 'All' (length=3)

What am I doing wrong?

Answer 1

The answer came from @Wolf's comment

You either need to connect as the user that owns this package, or grant execute on SURVEY_TESTER to your_connection_user;. Because the default is to compile as authid current_user you also need to grant on the underlying objects (tables, views, etc) to the other connection user.

Answer 2

The code itself is good I think.
But the setup of your Package could be the problem.

CREATE OR REPLACE PACKAGE BODY NameIt AS 
    PROCEDURE AddOne()
    AS
    BEGIN
        BEGIN
            # Insert into table. 
        EXCEPTION
            # What if the value is already inserted? 
        END;
    END;

    PROCEDURE AddTwo()
    AS
    BEGIN
        BEGIN
            # Insert into table. 
        EXCEPTION
            # What if the value is already inserted? 
        END;
    END;
END;
/