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);
Add a comment
|
4 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.
3 Comments
Rajesh Chamarthi
@Mesa very true
HelpfulProgrammer
The comparing string in
column_name and table_name always need to be in uppercase (that’s why @mastaBlasta wrote his comment) even if you created them with different casing. create table foo (bar number); will result in table_name=FOO and column_name='BAR'
Jamie
@bugybunny Unless the column and table names are actually created in lowercase.
create table "foo" ("bar" number);. Note that this means you can have tables named FOO, Foo and FOo. So groksters solution below solves the problem a little better imo.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;
/
4 Comments
Rich Bianco
Sweet! Can this be used for table_exists too?
grokster
Unfortunately, there is no "table_exists".
eztam
@grokster To check if the table exists, simply use the code -955 instead of -01430
Yair
worked perfect, great to avoid pesky maven flyway errors
Normally, I'd suggest trying the ANSI-92 standard meta tables for something like this but I see now that Oracle doesn't support it.
-- this works against most any other database
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS C
INNER JOIN
INFORMATION_SCHEMA.TABLES T
ON T.TABLE_NAME = C.TABLE_NAME
WHERE
C.COLUMN_NAME = 'columnname'
AND T.TABLE_NAME = 'tablename'
Instead, it looks like you need to do something like
-- Oracle specific table/column query
SELECT
*
FROM
ALL_TAB_COLUMNS
WHERE
TABLE_NAME = 'tablename'
AND COLUMN_NAME = 'columnname'
I do apologize in that I don't have an Oracle instance to verify the above. If it does not work, please let me know and I will delete this post.
4 Comments
Randy
or try it and catch the exception
Justin Cave
If you query the
ALL_TAB_COLUMNS data dictionary view, you'd want to include a predicate on the OWNER column in case the same table exists in multiple schemas. If you know that you're only interested in tables in the current user's schema, you should use the USER_TAB_COLUMNS view instead.
XDS
Errrr in the first snippet did you mean to write 'COLUMN_NAME' (instead of 'COLLATION_NAME')
billinkc
Thanks for the catch @xDisruptor I have now corrected it
To check column exists
select column_name as found
from user_tab_cols
where table_name = '__TABLE_NAME__'
and column_name = '__COLUMN_NAME__'
1 Comment
APC
This answer merely reproduces advice given in other answers without adding anything.