The following stored procedure will add a new column called SOME_NEW_COLUMN with a NUMBER datatype to all tables in the current schema. This stored procedure does not take any parameters.
CREATE OR REPLACE PROCEDURE ADD_COLUMN_STORED_PROCEDURE IS
temp_String varchar2(200);
temp_Table_Name varchar2(30);
CURSOR cursor_User_Tables IS
SELECT TABLE_NAME
FROM USER_TABLES;
BEGIN
FOR rec IN cursor_User_Tables
LOOP
temp_Table_Name := rec.TABLE_NAME;
temp_String := ‘ALTER TABLE ‘ || temp_Table_Name || ‘ ADD SOME_NEW_COLUMN NUMBER’;
EXECUTE IMMEDIATE temp_String;
END LOOP;
END;
/
If you wanted the stored procedure to add the column to a single table, your stored procedure would look like this.
CREATE OR REPLACE PROCEDURE ADD_COLUMN_STORED_PROCEDURE_2 (var_Table_Name VARCHAR2) IS
temp_String varchar2(200);
BEGIN
temp_String := ‘ALTER TABLE ‘ || var_Table_Name || ‘ ADD SOME_NEW_COLUMN_2 NUMBER’;
EXECUTE IMMEDIATE temp_String;
END;
/
