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