Home » SQL & PL/SQL » SQL & PL/SQL » Alter Table (ORACLE 9.0.1.1.1 , Win XP)
Alter Table [message #304155] Tue, 04 March 2008 06:00 Go to next message
adilsami
Messages: 46
Registered: October 2007
Member
Hi ,

i want to alter a table by adding columns to it..
with the check that IF the columns already exists in
the table then don't add these columns.. else add.

is there any syntax for this ?

regards,
Adil

Re: Alter Table [message #304157 is a reply to message #304155] Tue, 04 March 2008 06:07 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
you can check USER_TAB_COLUMNS.why do you need to do this?what is your business requirement?

regards,
Re: Alter Table [message #304158 is a reply to message #304155] Tue, 04 March 2008 06:14 Go to previous messageGo to next message
adilsami
Messages: 46
Registered: October 2007
Member
Ive made a procedure to add 2 columns ( updated by , updated on )
to all the tables in the current schema then i had to add more tables .. and wanet to add same 2 columns in new added tables ..
so didn't know how do that using the .
Re: Alter Table [message #304159 is a reply to message #304155] Tue, 04 March 2008 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try to add them, if they are already there you will get an error that you ignore.

Regards
Michel
Re: Alter Table [message #304160 is a reply to message #304155] Tue, 04 March 2008 06:35 Go to previous messageGo to next message
adilsami
Messages: 46
Registered: October 2007
Member
well ive tried to run the precdure again to add
the columns,but gives me an error that columns
already exist , so it doesn't adds to the remaining tables .

how i can tackle this.. ?
Re: Alter Table [message #304163 is a reply to message #304159] Tue, 04 March 2008 06:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
For creating re-runnable installation scripts, especially for 3rd party customers, it is not enough to ignore these errors. If you do, customers will complain that the installation script contains errors.
I found this one of the hardest things in the development-cycle. How to keep a clean installation (especially when branches etc get involved).
The way I handle the problem in the original question is to create a package containing several procedures like add_column (p_table_name, p_column_name, p_statement) that does
begin
  select 1
  into   l_exists
  from   user_tab_columns utc
  where  utc.table_name = p_table_name
  and    utc.column_name = p_column_name;
  dbms_output.put_line('Column '||p_column_name||' already present in table '||p_table_name||'.');
exception
  when no_data_found
  then
    dbms_output.put_line('Adding column '||p_column_name||' to table '||p_table_name||'.');
    execute immediate p_statement;
end;


It's a quick (and may I say dirty-ish) way, but it gets the job done.
Re: Alter Table [message #304177 is a reply to message #304163] Tue, 04 March 2008 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Between "select" and "alter table" the column may be added by another one and yuo're back to the first question.

The only safe way is to (try to) add and if there is an error "ORA-01430: column being added already exists in table" then do what you want in the exception block, log, display a message or simply ignore it and go to the next one.

Regards
Michel
Re: Alter Table [message #304194 is a reply to message #304177] Tue, 04 March 2008 08:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Tue, 04 March 2008 14:43
Between "select" and "alter table" the column may be added by another one and yuo're back to the first question.


That's nonsense during an installation.
Re: Alter Table [message #304195 is a reply to message #304155] Tue, 04 March 2008 08:30 Go to previous messageGo to next message
adilsami
Messages: 46
Registered: October 2007
Member
hey Michel ,

can u please tell me more in detail how to do that ?
Re: Alter Table [message #304197 is a reply to message #304194] Tue, 04 March 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Tue, 04 March 2008 15:28
Michel Cadot wrote on Tue, 04 March 2008 14:43
Between "select" and "alter table" the column may be added by another one and yuo're back to the first question.


That's nonsense during an installation.

Right but how many applications do this in the fly? Too much. They really exist.

Regards
Michel

Re: Alter Table [message #304200 is a reply to message #304195] Tue, 04 March 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
declare 
  already_exist exception;
  pragma exception_init (already_exists, -1430);
begin
  for c in (select table_name from user_tables) loop
    begin
      execute immediate 'alter table '||c.table_name||' add (col integer)';
    exception when already_exists then null;
    end;
  end loop;
end;
/

Regards
Michel
Re: Alter Table [message #304226 is a reply to message #304155] Tue, 04 March 2008 10:25 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:698008000346356376

Regards
Michel
Previous Topic: Date Format - Y2k
Next Topic: External Tables -- Identifying
Goto Forum:
  


Current Time: Wed Dec 07 10:27:22 CST 2016

Total time taken to generate the page: 0.14743 seconds