topic Re: adding columns to many tables - ORACLE in Operating System - HP-UX

adding columns to many tables - ORACLE

Stephen Badgett — Thu, 03 Mar 2005 14:26:22 GMT

ORACLE - adding columns to many tables..

We have a schema "srb" that has several tables in it that we need to add columns in all the tables in a schema.

example
schema SRB

TABLE A
TABLE B
TABLE C
...
TABLE ZZZ

COLUMNS TO ADD IN EACH

NAME VARCHAR2(100)
ADDRESS VARCHAR2(100)
PHONE NUMBER

I know I can add one at a time but, We have a lot of tables and need to do this without doing it manually.

Any suggestions?

Re: adding columns to many tables - ORACLE

Alex Lavrov. — Thu, 03 Mar 2005 14:40:26 GMT

yep, 2 ways:

1. write select that generates "alter table" statements, something like:
select "alter table " || table_name ...
from dict
wher ....

spool it into some file and then execute it.

2. write pl/sql procedure that executes alter table.

But I prefer the first way.

Re: adding columns to many tables - ORACLE

Stephen Badgett — Thu, 03 Mar 2005 16:57:53 GMT

I am not sure how to do that.

Re: adding columns to many tables - ORACLE

Julio Yamawaki — Thu, 03 Mar 2005 17:49:36 GMT

Hi,

I've been an Oracle DBA for many years and believe, the first way that Alex told you is the fast and best way to do this.
Also, you can make some modifications in the script generated by select..., puting some promptÂ´s or dbms_output.put_line... to say what your are doing, like, "alter table xxx add column xxx number;" and spooling all output of this script to verify that everything was done.

Regards

Re: adding columns to many tables - ORACLE

Stephen Badgett — Thu, 03 Mar 2005 19:55:18 GMT

I do not doubt it in any way I just do not know how to do the syntax of the statement

Re: adding columns to many tables - ORACLE

Stephen Badgett — Thu, 03 Mar 2005 20:03:23 GMT

Here it is currently -- Are we just to add the add part later?
---------------------------------

select 'alter table ' || table_name from srb_flash
where table_name is not null;
2
'ALTERTABLE'||TABLE_NAME
--------------------------------------------------------------------------------
alter table CUSTOMER
alter table DEPT
alter table EMPLOYEE
alter table NEWTBL
alter table SRB_FLASH

Re: adding columns to many tables - ORACLE

Indira Aramandla — Thu, 03 Mar 2005 20:07:02 GMT

Hi Steve,

Yes creating a script with the alter table statements will be an easy solution. Here is how you do it.

SQL> set heading off
SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';

This statement will prepare alter table statements for all the tables in the schema SRB and the output will look like this :-

Alter table TABLE_A add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);

Befor you execute the above SQL statement spool to a alter_tab.sql file. Like this

SQL> set heading off

SQL>spool alter_tab.sql

SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';

SQL> spool off

SQL>set heading on

Now you will find the sql file alter_tab.sql created in your directory. Now to execute the created alter_tab.sql do this.

SQL>spool alter_tab_output.log
SQL>@alter_tab.sql
SQL>spool off

Now check the alter_tab_output.log file for any errors.

Indira A

Re: adding columns to many tables - ORACLE

Stephen Badgett — Thu, 03 Mar 2005 20:18:07 GMT

Perfect -- thank you lots

Re: adding columns to many tables - ORACLE

Stephen Badgett — Thu, 03 Mar 2005 20:20:03 GMT

You were all help in this and I do find this to be a great solution. Thank you for the answer

Stephen