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