Procedure in Oracle

Can anyone provide me a procedure to add multiple columns into multiple tables?
For individual table is Alter table table_name add ( column_name varchar2(50), city varchar2(25));

What I would like to know is the procedure for adding multiple columns into multiple tables.
lium1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Something like this?

assuming the table name to add the columns is 'tab1', create a string and just replace the table_name with the actual table name?

Not sure why you need pl/sql but this should work.
declare
     add_string varchar2(255) := 'Alter table table_name add ( column_name varchar2(50), city varchar2(25))';

begin
     execute immediate replace(add_string,'table_name','tab1');
end;
/

Open in new window

MikeOM_DBACommented:
Building up on slightwv's solution, try this if you can select the tables with a certain prefix (or regular expression):
DECLARE
   CURSOR Tab_Csr
   IS
      SELECT Table_Name
        FROM User_Tables
       WHERE Table_Name LIKE 'MY_TAB%';

   Add_String   VARCHAR2 ( 255 )
      := 'Alter table table_name add ( column_name varchar2(50), city varchar2(25))';
   Sql_String   VARCHAR2 ( 255 );
BEGIN
   DBMS_OUTPUT.Enable ( 1000000 );

   FOR T IN Tab_Csr
   LOOP
      Sql_String   := REPLACE ( Add_String, 'table_name', T.Table_Name );
      DBMS_OUTPUT.Put_Line ( Sql_String );

      EXECUTE IMMEDIATE Sql_String;
   END LOOP;
END;
/

Open in new window

slightwv (䄆 Netminder) Commented:
The loop was sort of implied.  I didn't post it because the source of table names was unknown.

Since the question mentioned a procedure, maybe that will pass in a CSV as a varchar2 that will need to be parsed or an array for the loop.

Since you posted it, I would further simplify it:
begin
	for i in ( SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE 'MY_TAB%' ) loop
	dbms_output.put_line('Altering table: ' || i.table_name);
	execute immediate 'Alter table ' || i.table_name || ' add ( column_name varchar2(50), city varchar2(25))';
	end loop;
end;
/

                                          

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
>>Can anyone provide me a procedure to add multiple columns into multiple tables?<<
Are the columns to be added always the same (name and data type) and are the tables to which they are to be added defined in any way?
lium1Author Commented:
Thanks to both of you: Slightwv and Awking00 and sorry for not stating the issue correctly.
This is what I like to do: adding different columns to different tables.
Please check the procedure and tell me it is correct or not!

Create or replace procedure procedure_ name
as
Alter table table_name1 add(column_1 varchar2(25), column_2 varchar2(25));
Alter table table_name2 add(column_3 varchar2(25), column_4 num(25));
Alter table table_name3 add(column_5 varchar2(25), column_6 date);

Or do you have better way!
slightwv (䄆 Netminder) Commented:
>>Please check the procedure and tell me it is correct or not!

Not correct.  You cannot perform DDL (create/alter/etc... a table) inside of a procedure that way.

You need the execute immediate to execute DDL.

As far as how to do it:
Just place the alter statements in a .sql file and execute it using sqlplus.

Create bob.sql with those alter statements.
Then from the SQL prompt:
SQL> @/path_to_bob/bob
lium1Author Commented:
It is more or less like one time thing! Then, I guess you probably say why create a procedure.

To answer your questions, no, the columns are not to be added always the same way.

I am not sure I understand your second question. The tables could be vary in time, some of them need to be added some columns sometime. I am not sure I answer your question correctly.
slightwv (䄆 Netminder) Commented:
>>Then, I guess you probably say why create a procedure.

You should not create a procedure.  Just create a script file and execute it.

repeat:  DO NOT CREATE A PROCEDURE FOR THIS.

You should have a release schedule or some form of change control.  Your .sql file with the DDL commands in them should be part of the build.

There is NO benefit to create a stored procedure that can and only will be executed once.  Then it would need to be dropped/removed after it is executed.

Don't do it.  Forget it was even considered.

That said:
Why were you thinking a procedure in the first place?
lium1Author Commented:
Slightwv,

I can create procedure like this, can't I?

Create or replace procedure procedure_ name as

begin
      for i in ( SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE 'MY_TAB%' ) loop
      dbms_output.put_line('Altering table: ' || i.table_name);
      execute immediate 'Alter table ' || i.table_name || ' add ( column_name varchar2(50), city varchar2(25))';
      end loop;
end;
/

or

Create or replace procedure procedure_ name as

DECLARE
   CURSOR Tab_Csr
   IS
      SELECT Table_Name
        FROM User_Tables
       WHERE Table_Name LIKE 'MY_TAB%';

   Add_String   VARCHAR2 ( 255 )
      := 'Alter table table_name add ( column_name varchar2(50), city varchar2(25))';
   Sql_String   VARCHAR2 ( 255 );
BEGIN
   DBMS_OUTPUT.Enable ( 1000000 );

   FOR T IN Tab_Csr
   LOOP
      Sql_String   := REPLACE ( Add_String, 'table_name', T.Table_Name );
      DBMS_OUTPUT.Put_Line ( Sql_String );

      EXECUTE IMMEDIATE Sql_String;
   END LOOP;
END;
/
slightwv (䄆 Netminder) Commented:
>>I can create procedure like this, can't I?

You 'CAN' but, again, why do you want to?  For a one time change, create a simple sql script.  forget a stored procedure.

I didn't check the code you posted for validity but they look OK from a quick scan.

That said:
In the last update you posted you wanted to add different column names with different data types to different tables.  That would be really hard to do with code.

The only time you might think about a pl/sql loop for this is if you wanted to add the same column to several hundred or more tables.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lium1Author Commented:
Thanks to all!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.