decode(data_type, 'NUMBER', data_precision, and syn.synonym_name (+)= cat.table_name and col.table_name = nvl(syn.table_name,cat.table_name)
and col.owner = nvl(syn.table_owner, #{(scope ==
“all” ? “cat.owner” : “user”)}) }Works for us here. I had an initial fix but it failed for synonyms.
Then I tried EXACTLY what you tried and it didn’t work for various
permissions.
What permission problem do you have? Our database is using some schemas
with one
account who can access all schema objects (used in a J2EE environment
BTW). I don’t
have a problem and actually the original solution would work for me if
it wasn’t so slow.
Hm, I wonder what your statement is intended to do. Anyway, I get this
error (which
is quite natural if you look at the first subselect):
OCIError: ORA-00904: “CAT”.“OWNER”: invalid identifier:
select column_name, data_type, data_default, nullable,
decode(data_type, ‘NUMBER’, data_precision,
‘VARCHAR2’, data_length,
null) as length,
decode(data_type, ‘NUMBER’, data_scale, null) as
scale
from (select table_name from all_catalog where table_name
= ‘WHACCOUNTTYPE’) cat,
(select * from all_synonyms where synonym_name =
‘WHACCOUNTTYPE’) syn,
(select * from all_tab_columns) col
where cat.table_name = ‘WHACCOUNTTYPE’
and syn.synonym_name (+)= cat.table_name
and col.table_name = nvl(syn.table_name, cat.table_name)
and col.owner = nvl(syn.table_owner, cat.owner)
and cat.owner = ‘SYSP’
and syn.owner (+)= cat.owner
Regards,
Andreas