Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to check if a column exists before adding it to an existing table in PL/SQL?

Tags:

sql

oracle

plsql

How do I add a simple check before adding a column to a table for an oracle db? I've included the SQL that I'm using to add the column.

ALTER TABLE db.tablename    ADD columnname NVARCHAR2(30); 
like image 876
Prabu Avatar asked Jun 15 '11 00:06

Prabu


People also ask

How do you check whether a column exists in a table in Oracle?

SQL> EXEC :column_name := 'EMPNO'; PL/SQL procedure successfully completed. SQL> declare 2 cnt PLS_INTEGER; 3 BEGIN 4 SELECT COUNT(*) INTO cnt FROM user_tab_columns WHERE table_name = :table_name AND column_name= :column_name; 5 IF cnt > 0 THEN 6 DBMS_OUTPUT. PUT_LINE('Column Exist'); 7 ELSE 8 DBMS_OUTPUT.

How do you check if a column exists or not?

For checking the existence we need to use the COL_LENGTH() function. COL_LENGTH() function returns the defined length of a column in bytes. This function can be used with the IF ELSE condition to check if the column exists or not.

How do I check if a column is present in SQL?

Colum view to check the existence of column Name in table SampleTable. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA. COLUMNS WHERE table_name = 'SampleTable' AND column_name = 'Name' ) SELECT 'Column exists in table' AS [Status] ; ELSE SELECT 'Column does not exist in table' AS [Status];


2 Answers

All the metadata about the columns in Oracle Database is accessible using one of the following views.

user_tab_cols; -- For all tables owned by the user

all_tab_cols ; -- For all tables accessible to the user

dba_tab_cols; -- For all tables in the Database.

So, if you are looking for a column like ADD_TMS in SCOTT.EMP Table and add the column only if it does not exist, the PL/SQL Code would be along these lines..

DECLARE   v_column_exists number := 0;   BEGIN   Select count(*) into v_column_exists     from user_tab_cols     where upper(column_name) = 'ADD_TMS'       and upper(table_name) = 'EMP';       --and owner = 'SCOTT --*might be required if you are using all/dba views    if (v_column_exists = 0) then       execute immediate 'alter table emp add (ADD_TMS date)';   end if; end; / 

If you are planning to run this as a script (not part of a procedure), the easiest way would be to include the alter command in the script and see the errors at the end of the script, assuming you have no Begin-End for the script..

If you have file1.sql

alter table t1 add col1 date; alter table t1 add col2 date; alter table t1 add col3 date; 

And col2 is present,when the script is run, the other two columns would be added to the table and the log would show the error saying "col2" already exists, so you should be ok.

like image 185
Rajesh Chamarthi Avatar answered Sep 21 '22 07:09

Rajesh Chamarthi


Or, you can ignore the error:

declare     column_exists exception;     pragma exception_init (column_exists , -01430); begin     execute immediate 'ALTER TABLE db.tablename ADD columnname NVARCHAR2(30)';     exception when column_exists then null; end; / 
like image 36
grokster Avatar answered Sep 22 '22 07:09

grokster