PLS-00201 - identifier must be declared

Question

I executed a PL SQL script that created the following table  TABLE NAME VARCHAR2 30      B2BOWNER SSC Page Map     I made an insert function for this table using arguments   CREATE OR REPLACE FUNCTION F SSC Page Map Insert           p page id   IN B2BOWNER SSC Page Map Page ID NBR TYPE            p page type IN B2BOWNER SSC Page Map Page Type TYPE            p page dcpn IN B2BOWNER SSC Page Map Page Dcpn TYPE    I was notified I had to declare B2BOWNER SSC Page Map prior to it appearing as an argument to my function   Why am I getting this error   EDIT  Actual error  Warning  compiled but with compilation errors Errors for FUNCTION F SSC PAGE MAP INSERT  LINE COL ERROR                                                             -------- ----------------------------------------------------------------- 2 48     PLS-00201  identifier  SSC PAGE MAP PAGE ID NBR  must be declared 0 0      PL SQL  Compilation unit analysis terminated    EDIT  Complete PL SQL Function  RETURN INTEGER IS    TABLE DOES NOT EXIST exception       PRAGMA EXCEPTION INIT TABLE DOES NOT EXIST  -942   -- ORA-00942  BEGIN     INSERT INTO         B2BOWNER SSC Page Map VALUES             p page id              p page type              p page dcpn       RETURN 0      EXCEPTION        WHEN TABLE DOES NOT EXIST THEN            RETURN -1         WHEN DUP VAL ON INDEX THEN            RETURN -2         WHEN INVALID NUMBER THEN            RETURN -3         WHEN OTHERS THEN            RETURN -4  END   SHOW ERRORS PROCEDURE F SSC Page Map Insert   GRANT EXECUTE ON F SSC Page Map Insert TO B2B USER DBROLE   RETURN INTEGER   EDIT  I change the arguments and received a new error related to the insert command  CREATE OR REPLACE FUNCTION F SSC Page Map Insert                              p page id   IN INTEGER                               p page type IN VARCHAR2                               p page dcpn IN VARCHAR2   RETURN INTEGER IS  TABLE DOES NOT EXIST exception    PRAGMA EXCEPTION INIT TABLE DOES NOT EXIST  -942   -- ORA-00942  BEGIN  INSERT INTO      B2BOWNER SSC Page Map VALUES          p page id           p page type           p page dcpn     The error  Errors for FUNCTION F SSC PAGE MAP INSERT  LINE COL ERROR                                                             -------- ----------------------------------------------------------------- 17 18    PL SQL  ORA-00942  table or view does not exist                   16 5     PL SQL  SQL Statement ignored                                       The tables has been verified within the correct schema and with the correct attribute names and types  EDIT  I executed the following command to check if I have access  DECLARE     count this INTEGER   BEGIN  select count    into count this  from all tables  where owner    B2BOWNER   and table name    SSC PAGE MAP    DBMS OUTPUT PUT LINE count this    END    The output I received is  1 PL SQL procedure successfully completed    I have access to the table   EDIT   So I finally conducted an insert into the table via the schema using PL SQL and it worked fine   It appears I simply do not have authority to create functions but that is an assumption   EDIT   Actual table DDL statement   v create     CREATE TABLE       TABLE NAME                        PAGE ID NBR   NUMERIC 10    NOT NULL Check Page ID NBR  gt  0                   PAGE TYPE     VARCHAR2 50   NOT NULL                   PAGE DCPN     VARCHAR2 100  NOT NULL                  PRIMARY KEY Page ID NBR  Page Type      EXECUTE IMMEDIATE v create    COMMIT WORK   COMMIT COMMENT  Create Table

User · Answer

The procedure name should be in caps while creating procedure in database. You may use small letters for your procedure name while calling from Java class like:

String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";

In database the name of procedure should be:

GETDBUSERBYUSERID    -- (all letters in caps only)

This serves as one of the solutions for this problem.

User · Answer

you should give permission on your db  grant execute on  packageName or tableName  to user

User · Answer

When creating the TABLE under B2BOWNER  be sure to prefix the PL SQL function with the Schema name  i e  B2BOWNER F SSC Page Map Insert   I did not realize this until the DBAs pointed it out   I could have created the table under my root USER SCHEMA and the PL SQL function would have worked fine

[oracle] PLS-00201 - identifier must be declared

Examples related to oracle

Examples related to function

Examples related to plsql