ORA-06550 Identifier must Be Declared

1
votes

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?

2
phpsqloraclepdoplsql
Is your PDO connection string connecting to the same user schema that you used to compile this package? - Wolf
@Wolf Different user, same schema. Would that make a difference? - I wrestled a bear once.
What do you mean by "different user, same schema"? Are you saying that you are doing an alter session set current_schema after opening the connection from your PHP code? Are you certain that the user that your PHP application is using to connect has been given execute access on the package? - Justin Cave
Yes. 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. - Wolf
@Wolf, thanks, that's what I needed. Feel free to post the answer if you want, I'll check it later. - I wrestled a bear once.

2 Answers

1
votes

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;
/
0
votes

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.