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?
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

0
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

0
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

awking00Commented:
>>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?
0
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!
0
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
0
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.
0
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?
0
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;
/
0
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.
0

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!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.