Describe across database link in Oracle 12 fails

Toad for Oracle
1

I found why I am getting the ORA-00904 “COMS”.“ORIGIN_CON_ID”: invalid identifier error in TOAD. This is caused by a mismatch in the database versions between our current database and the db linked database. We switched to Oracle 12c over the weekend and the DB linked database is an 11g database. We can see in the generated SQL when I try to do a describe in TOAD the software builds a query in the 12c database and tries to apply the query against the linked 11g database. The issue is the query contains a column name that does not exist in the 11g database (sys.user_col_comments.origin_con_id aliased as coms).

2

I should be able to fix that. We do check Oracle version through DB Link to avoid this kind of thing, but maybe I missed something.

Can you give me the whole query that is failing?

3

Also, which version of Toad are you using? This appears to be fixed in 12.12 and newer.

4

This is in TOAD 12.11.0.95.

– Session: TKELLER@SIS_DEV
– Timestamp: 10:36:41.783
SELECT version
FROM SYS.PRODUCT_COMPONENT_VERSION@“PAYROLL”
WHERE UPPER(PRODUCT) LIKE ‘%ORACLE%’;

– Session: TKELLER@SIS_DEV
– Timestamp: 10:36:47.096
Select owner, object_name, object_type
from sys.all_objects@“PAYROLL”
where object_name = ‘EMPLOYEE_D_V’
and object_type not like ‘%PARTITION%’
and object_type <> ‘UNDEFINED’
and owner = ‘BANNER_DM’
union all
Select owner, constraint_name as object_name, ‘CONSTRAINT’ as object_type
from sys.all_constraints@“PAYROLL”
where constraint_name = ‘EMPLOYEE_D_V’
and owner = ‘BANNER_DM’
order by 3;

– Session: TKELLER@SIS_DEV
– Timestamp: 10:36:47.768
Select o.object_name, substr(o.status, 1, 1) status, o.last_ddl_time, o.object_id, o.created, o.owner
from sys.ALL_OBJECTS@“PAYROLL” o
where o.object_type = ‘VIEW’
and o.owner = ‘BANNER_DM’
and o.object_name = ‘EMPLOYEE_D_V’;

– Session: TKELLER@SIS_DEV
– Timestamp: 10:36:47.846
Select cols.column_id, cols.column_name as Name, cols.nullable,
cols.data_type as Type, cols.data_type_mod,
decode(cols.data_type, ‘CHAR’, cols.char_length,
‘VARCHAR’, cols.char_length,
‘VARCHAR2’, cols.char_length,
‘NCHAR’, cols.char_length,
‘NVARCHAR’, cols.char_length,
‘NVARCHAR2’, cols.char_length,
null) nchar_length,
decode(cols.data_type, ‘NUMBER’, cols.data_precision + cols.data_scale, cols.data_length) length,
cols.data_precision Precision, cols.data_scale Scale, cols.data_length dlength, cols.data_default
,cols.char_used
,cols.hidden_column, cols.internal_column_id
,cols.qualified_col_name
,InitCap(cols.histogram) histogram
,cols.virtual_column
,cols.num_distinct, cols.num_nulls, round(cols.density, 5) density
,coms.comments
FROM
sys.ALL_COL_COMMENTS@“PAYROLL” coms,
sys.ALL_TAB_COLS@“PAYROLL” cols
where 1=1
and coms.table_name (+) = cols.Table_Name
and coms.column_name (+) = cols.column_Name
and coms.owner (+) = cols.owner
and coms.origin_con_id (+) = TO_NUMBER(sys_context(‘USERENV’,‘CON_ID’))
and cols.table_name = ‘EMPLOYEE_D_V’
and cols.owner = ‘BANNER_DM’
order by column_id, internal_column_id;
Error: ORA-00904: “COMS”.“ORIGIN_CON_ID”: invalid identifier

– Session: TKELLER@SIS_DEV
– Timestamp: 10:36:55.251
select UPDATABLE, INSERTABLE, DELETABLE
from All_Updatable_columns@“PAYROLL”
where owner =:own
and table_name = :tn
and ((updatable=‘YES’) or (insertable=‘YES’) or (Deletable=‘YES’));
:own(VARCHAR[9],IN/OUT)=‘BANNER_DM’
:tn(VARCHAR[12],IN/OUT)=‘EMPLOYEE_D_V’

– Session: TKELLER@SIS_DEV
– Timestamp: 10:36:55.329
SELECT
ROWID, *
FROM BANNER_DM.EMPLOYEE_D_V@“PAYROLL”
Error: ORA-00936: missing expression

Thanks,
Tony

5

Ah, yes, I see it was broken for 12.11 and fixed for 12.12 and newer.