BrockN.com

The following function will add a new column called SOME_NEW_COLUMN with a NUMBER datatype to all tables in the current schema.  This function does not take any parameters.

CREATE OR REPLACE FUNCTION ADD_COLUMN_FUNCTION
RETURN INTEGER
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;

RETURN 1;

END;
/

You can execute the above function by running the following.

DECLARE
X NUMBER;
BEGIN
X := ADD_COLUMN_FUNCTION;
END;
/

If you wanted the function to add the column to a single table, your function would look like this.

CREATE OR REPLACE FUNCTION ADD_COLUMN_FUNCTION_2 (var_Table_Name VARCHAR2)
RETURN INTEGER
IS

temp_String varchar2(200);

BEGIN

temp_String := ‘ALTER TABLE ‘ || var_Table_Name || ‘ ADD SOME_NEW_COLUMN_2 NUMBER’;

EXECUTE IMMEDIATE temp_String;

RETURN 1;

END;
/

You can execute the above function by running the following where TEMP2 is the table name we are adding the column to.

DECLARE
X NUMBER;
BEGIN
X := ADD_COLUMN_FUNCTION_2(‘TEMP2′);
END;
/