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

8-8. Creating a Table at Runtime

Problem

Your application needs to have the ability to create tables based upon user input. The user has the ability to add additional attributes to some of your application forms, and when this is done, a new attribute table needs to be created to hold the information.

Solution

Create a table at runtime using native dynamic SQL. Write a procedure that accepts a table name as an argument and then creates a SQL string including the DDL that is required for creating that table. The table structure will be hard-coded since the structure for an attribute table will always be the same within your application. The code that follows demonstrates this technique by creating a procedure named CREATE_ATTR_TABLE that dynamically ...