Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00302: component must be declared (Ora 11.2.0)
PLS-00302: component must be declared [message #607841] Wed, 12 February 2014 09:47 Go to next message
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
Member
I have a problem that I cannot explain. I have a table called ACCT_FOLIO_CHANGE on HAF user. This table also has a PUBLIC synonym.
Logging into HAF, I can see the table and synonym objects....

$ sqlplus haf/*********@*******

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 12 15:35:44 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM dba_objects WHERE object_name = 'ACCT_FOLIO_CHANGE';

OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED           LAST_DDL_TIME        TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- -------------------- -------------------- ------------------- ------- - - - ---------- ------------------------------
PUBLIC                         ACCT_FOLIO_CHANGE                                                 61094                SYNONYM             13-Jun-2013 15:31:46 13-Jun-2013 15:31:46 2013-06-13:15:31:46 VALID        N N N          1
HAF                            ACCT_FOLIO_CHANGE                                                 78775          78775 TABLE               13-Jun-2013 14:38:39 01-Jul-2013 10:32:40 2013-06-13:14:38:42 VALID        N N N          1

Elapsed: 00:00:00.07

SQL> SELECT * FROM dba_synonyms WHERE synonym_name = 'ACCT_FOLIO_CHANGE';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------
PUBLIC                         ACCT_FOLIO_CHANGE             HAF                            ACCT_FOLIO_CHANGE

Elapsed: 00:00:00.07


Also, I can select from the table (same session as above)...

SQL> SELECT * FROM HAF.ACCT_FOLIO_CHANGE;

ACCOUNT_NO ACTION     DATE_CREATED
---------- ---------- ---------------------------------------------------------------------------
1004602888 UPDATE     13-AUG-13 22.43.08.000000

Elapsed: 00:00:00.05

SQL> SELECT * FROM ACCT_FOLIO_CHANGE;

ACCOUNT_NO ACTION     DATE_CREATED
---------- ---------- ---------------------------------------------------------------------------
1004602888 UPDATE     13-AUG-13 22.43.08.000000

Elapsed: 00:00:00.05


However, I cannot reference the Table from within a PL/SQL block using the "HAF." prefix... but I can if I use the PUBLIC synonym....

SQL> DECLARE
 TYPE tt IS TABLE OF HAF.ACCT_FOLIO_CHANGE%ROWTYPE;
    BEGIN
      NULL;
    END;
  /
 TYPE tt IS TABLE OF HAF.ACCT_FOLIO_CHANGE%ROWTYPE;
                         *
ERROR at line 2:
ORA-06550: line 2, column 26:
PLS-00302: component 'ACCT_FOLIO_CHANGE' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Item ignored


Elapsed: 00:00:00.21
SQL> DECLARE
 TYPE tt IS TABLE OF ACCT_FOLIO_CHANGE%ROWTYPE;
    BEGIN
      NULL;
    END;
    /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL>


I don't understand why this is. I am only getting this on one of 6 databases. The other work fine and all are built with an identical process. It is a problem for me because I have many stored packages that reference objects using the USER prefix and so changing them all to use the PUBLIC SYNONYM is not an option.

I don't think this is a PRIV or Role problem as all objects are owned by the same user.
Could something have changed in the Oracle Configuration to cause this?
Could there be something corrupt in a datafile to cause this?

Thanks in advance,

Mike

Re: PLS-00302: component must be declared [message #607843 is a reply to message #607841] Wed, 12 February 2014 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What does this give:
SELECT * FROM dba_objects WHERE object_name = 'HAF';

Re: PLS-00302: component must be declared [message #607859 is a reply to message #607843] Wed, 12 February 2014 16:24 Go to previous message
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
Member
Hi, Above SELECT returns:

SQL> SELECT * FROM dba_objects WHERE object_name = 'HAF';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED              LAST_DDL_TIME        TIMESTAMP      STATUS  T G S
------------------------------ ---------- -------------- ------------------- -------------------- -------------------- ------------------- ------- - - -
 NAMESPACE EDITION_NAME
---------- ------------------------------
PUBLIC
HAF
                                   101647                SYNONYM             12-Feb-2014 10:35:59 12-Feb-2014 10:35:59 2014-02-12:10:35:59 VALID   N N N
         1


Elapsed: 00:00:00.10
SQL>


This appears to be the problem.. There has been a SYNONYM created for HAF that should not be there. I have dropped the synonym and everything appears fine now. Thanks CookieMonster... Good Call!!

Mike
Previous Topic: cursor lock for individual transactions
Next Topic: Prevent Table Owner from Deleting Records of a Table
Goto Forum:
  


Current Time: Thu Mar 28 04:17:09 CDT 2024