8-9. Altering a Table at Runtime
Problem
Your application provides the ability to add attributes to forms in order to store additional information. You need to provide users with the ability to make those attribute fields larger or smaller based upon their needs.
Solution
Create a procedure that will provide the ability to alter tables at runtime using native dynamic SQL. The procedure in this solution will accept two parameters, those being the table name to be altered and the column name along with new type declaration. The procedure assembles a SQL string using the arguments provided by the user and then executes it using native dynamic SQL. The following code demonstrates this solution:
CREATE OR REPLACE PROCEDURE modify_table(tab_name VARCHAR2,
tab_info VARCHAR2) AS
sql_text VARCHAR2(1000);
BEGIN
sql_text := 'ALTER TABLE ' || tab_name ||
' MODIFY ' || tab_info;
DBMS_OUTPUT.PUT_LINE(sql_text);
EXECUTE IMMEDIATE sql_text;
DBMS_OUTPUT.PUT_LINE('Table successfully altered…'),
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An error has occurred, table not modified'),
END;
The procedure determines whether the user-defined data is valid. If so, then the EXECUTE IMMEDIATE statement executes the SQL string that was formed. Otherwise, the user will see an alert displayed.
How It Works
Similar to creating objects at runtime, Oracle provides the ability to alter objects using dynamic SQL. The same technique is used for constructing the SQL string as when creating an object, and that string is eventually executed via the EXECUTE IMMEDIATE statement. The EXECUTE IMMEDIATE statement for altering a table at runtime uses no clause, because it is not possible to use bind variables with an ALTER TABLE statement. If you try to do pass in bind variable values, then you will receive an Oracle error.
The following format should be used when issuing the EXECUTE IMMEDIATE statement for SQL text containing an ALTER TABLE statement:
EXECUTE IMMEDIATE alter_table_sql_string;
The most important thing to remember when issuing a DDL statement using dynamic SQL is that you will need to concatenate all the strings and variables in order to formulate the final SQL string that will be executed. Bind variables will not work for substituting table names or column names/attributes.