Oracle and PL/SQL Recipes: A Problem-Solution Approach

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, ...