Get Specific Column Names in SYNONYM in Oracle

Question

With this query I can successfully get a list of column names for an Oracle table.

SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE table_name = 'TBL_NEWS' 
AND COLUMN_NAME LIKE ('GLOBE_%') 
ORDER BY  COLUMN_ID

I have a synonym and I need to get all column names of that synonym also. So how can I extend above query to get all column names in Synonym starting GLOBE_ ?

Erich Kitzmueller · Accepted Answer · 2017-07-05T18:38:01.903

4

A synonym does not have columns, the table it refers to has. So you might use this:

SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS atc
JOIN ALL_SYNONYMS als ON atc.table_name = als.table_name
WHERE als.SYNONYM_NAME='my_synonym'
  AND als.OWNER IN (USER, 'PUBLIC')
  AND atc.COLUMN_NAME LIKE ('GLOBE_%')
ORDER BY atc.COLUMN_ID

edited Jul 05 '17 at 18:38

answered Mar 07 '17 at 10:12

Erich Kitzmueller

36,381
5
80
102

Synonym can be for a view as well from other schema, above answer excludes the views, but just considers the tables. Isn't it ? – Srikanth Balaji Mar 07 '17 at 10:14
@SrikanthA: Actually, it works for views as well, since `USER_TAB_COLUMNS` also contains views' columns. – Erich Kitzmueller Mar 07 '17 at 10:17
Right, and the other schemas - How about using a ALL_TAB_COLUMNS – Srikanth Balaji Mar 07 '17 at 10:18
@SrikanthA: OK, Changed that to ALL_TAB_COLUMNS. – Erich Kitzmueller Mar 07 '17 at 10:19
1

remove `AND als.OWNER IN (USER, 'PUBLIC')` and `AND atc.COLUMN_NAME LIKE ('GLOBE_%')` and see if you get something. Also keep in mind that identifiers in Oracle are usually upper case (unless "quoted") so use upper case for synonym name and column name. – Erich Kitzmueller Mar 07 '17 at 10:25
Unfortunately, in Oracle you can create synonyms for other synonyms, which I don't think this answer will handle. The column name `ALL_SYNONYMS.TABLE_NAME` is misleading: if the synonym does not point to a table, it does not actually hold a table name. So, I think a "complete" solution to the OP's question needs to be a little more general. – Matthew McPeak Mar 07 '17 at 13:46
...correct the alias in the first join from utc to atc – jimo3 Jul 05 '17 at 18:34

Srikanth Balaji · Answer 2 · 2017-03-07T10:44:49.697

Depending on the extent of Schema you want data from - u can use ALL_TAB_COLUMNS and ALL_SYNONYMS OR DBA_TAB_COLUMNS and DBA_SYNONYMS

SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS ATC JOIN ALL_SYNONYMS ALS ON ATC.table_name = ALS.table_name WHERE ALS.SYNONYM_NAME='my_synonym'      AND ATC.COLUMN_NAME LIKE ('GLOBE_%') ORDER BY ATC.COLUMN_ID;

or 


SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS DTC JOIN DBA_SYNONYMS DS ON DTC.table_name = DS.table_name WHERE DS.SYNONYM_NAME='my_synonym'    AND DTC.COLUMN_NAME LIKE ('GLOBE_%') ORDER BY DTC.COLUMN_ID;

score 0 · Answer 3 · answered Mar 07 '17 at 10:37

Instead of querying data dictionary. You can create function to return list of column in table/view/synonym.

create type list_varchar2 is table of varchar2(100);
/
create or replace function  list_of_column(p_name in varchar2) return list_varchar2 PIPELINED  
    is
        v_Cnt        number :=  0;
        v_table_description       dbms_sql.desc_tab;
        c_curosor     integer default dbms_sql.open_cursor;
   begin
       dbms_sql.parse(  c_curosor, 'select * from '|| p_name||' where 1 = 2', dbms_sql.native );
       dbms_sql.describe_columns( c       => c_curosor,
                                  col_cnt => v_Cnt,
                                  desc_t  => v_table_description );

       for i in 1 .. v_Cnt
       loop

           pipe row ( v_table_description(i).col_name );

        end loop;
       dbms_sql.close_cursor(c_curosor);
   exception
       when others then dbms_sql.close_cursor( c_curosor );
           raise;
   end ;
/
select * from table(list_of_column('table_name'))
/
select * from table(list_of_column('view_name'))
/
select * from table(list_of_column('synonym'))
/

Get Specific Column Names in SYNONYM in Oracle

3 Answers3