How check if column already exist oracle

Question

I need to check if colum already exists. I think the query must be something like this:

select 

..
case
          when exists ( select * from Students s where colum_name='pec')
         then nvl(SUBSTR(s.student_name , 0, 100),'') 
         else null
       end as STUDENT NAME,

But this query doesn't work. Anyone can help me?

This seems like a very strange thing to do. You can use `User_Tab_Columns` table [like here](https://stackoverflow.com/questions/452464/how-can-i-get-column-names-from-a-table-in-oracle) to do this... but why are you doing this in the first place? It feels very wrong. — JNevill, Jan 25 '19 at 16:26
I mean a solution that it let me to verify if a colum exists or not — Doflamingo19, Jan 25 '19 at 16:33
You would need dynamic SQL for this. That said, it is highly suspicious if you don't know the columns in the tables in your own database. — Gordon Linoff, Jan 25 '19 at 20:46

score 0 · Answer 1 · answered Jan 25 '19 at 16:58

0

you can use USER_TAB_COLUMNS

select *  from user_tab_columns where table_name = 'MYTABLE' and COLUMN_NAME = 'COL1'

answered Jan 25 '19 at 16:58

Derviş Kayımbaşıoğlu

28,492
4
50
72

Barbaros Özhan · Answer 2 · 2019-01-25T17:46:16.387

Yes, you can verify. Consider the following sample case :

create table tab1( pec int, student_name varchar2(50));
insert into tab1(student_name) values('Doflamingo19');

create table tab2( pex int, student_name varchar2(50));
insert into tab2(student_name) values('Doflamingo19');

select substr(student_name ,0, 10) as student_name, 'Exists in TAB1' as Existance
  from tab1
 where exists ( select 1 
                  from user_tab_columns
                 where table_name = 'TAB1'
                   and column_name = 'PEC' )
 union all                  
 select substr(student_name ,0, 10) as student_name, 'NOT Exists in TAB2'
   from tab2
  where not 
        exists ( select 1 
                   from user_tab_columns
                 where table_name = 'TAB2'
                   and column_name = 'PEC' );

STUDENT_NAME    EXISTANCE
------------    ------------------
Doflamingo      Exists in TAB1
Doflamingo      NOT Exists in TAB2

How check if column already exist oracle

2 Answers2