Pls-00201 - Identifier Must Be Declared

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 > 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'; 
14

3 Answers

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.

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.

4

you should give permission on your db

grant execute on (packageName or tableName) to user;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

David Miller

David Miller

Executive Financial & Market Analyst

David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.

Share this article
Twitter Facebook Pinterest