Newly Added Column is not Appearing in Synonym

user_6859002 0
Viewed 850

I have a table named YY_ZZ_VAT_TRX_DETAILS under the XX schema. It has an existing SYNONYM to AA schema.

I am currently logged in as AA and I wanted to add a column to YY_ZZ_VAT_TRX_DETAILS and executed the below command just fine

alter table XX.YY_ZZ_VAT_TRX_DETAILS
add (USER_DEFINED_FISC_CLASS VARCHAR2(30));

Table XX.YY_ZZ_VAT_TRX_DETAILS altered.

I tried to select the Column from the Table using the simple query below

select  USER_DEFINED_FISC_CLASS
from    YY_ZZ_VAT_TRX_DETAILS;

But I surprisingly got this error:

ORA-00904: "USER_DEFINED_FISC_CLASS": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 1 Column: 9

I thought I just missed altering the table, so I re-ran the ALTER script again, but got this error instead:

Error starting at line : 4 in command - alter table XX.YY_ZZ_VAT_TRX_DETAILS add (USER_DEFINED_FISC_CLASS VARCHAR2(30)) Error report - ORA-01430: column being added already exists in table 01430. 00000 - "column being added already exists in table" *Cause:
*Action:

I checked the Object Definition and sure enough, I found the new column there:

enter image description here

But when I query all the columns from the Table, I still can't see it:

select  *
from    YY_ZZ_VAT_TRX_DETAILS;

enter image description here

However, when I query using the XX schema prefix, I'm able to see the column:

select  *
from    XX.YY_ZZ_VAT_TRX_DETAILS;

enter image description here

Why isn't the synonym picking up the newly-added column?

oracle sql
2 Answers

It seems this was caused by this "Edition-Based Redefinition" thing that Oracle recently introduced for 12c.

Upon further investigation, what happened was:

The Table XX.XX_ZZ_VAT_TRX_DETAILS has a Editionable view named XX_ZZ_VAT_TRX_DETAILS# that only selects a certain number of columns.

Now, the View XX_ZZ_VAT_TRX_DETAILS# has a SYNONYM named XX_ZZ_VAT_TRX_DETAILS under the schema AA, hence the confusion on the Table having a SYNONYM of the same name.

To resolve this, i modified the view XX_ZZ_VAT_TRX_DETAILS# and added the new column and successfully recompiled it.

Once this was done, the synonym now shows the newly-added column and I was able to compile the stored procedure successfully.

user_68590020

I believe you could be having a table or a view called YY_ZZ_VAT_TRX_DETAILS or a private synonym that points to another table?.

In oracle the preference while querying is

1)objects_in same schema 2)private synonym 3)public synonym

user_71370900
Related