Procedure in Oracle

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

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!

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.

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

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;
/

Thanks to all!