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 creates attribute tables.
CREATE OR REPLACE PROCEDURE create_attr_table(table_name VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || table_name ||
'(ATTRIBUTE_ID NUMBER PRIMARY KEY,
ATTRIBUTE_NAME VARCHAR2(150) NOT NULL,
ATTRIBUTE_VALUE VARCHAR2(150))';
END create_attr_table;
This procedure is invoked by the application whenever a user determines that additional attributes are required for a particular application form. That form will then have its own attribute table created, and the user can then provide additional fields/attributes to customize the form as needed.
How It Works
Dynamic SQL can be used to create database objects at runtime. In this recipe, it is used to create tables. Native dynamic SQL is used in this example, and the EXECUTE IMMEDIATE statement performs the work. When creating a table at runtime, generate a string that contains the necessary SQL to create the object. Once that task has been completed, issue the EXECUTE IMMEDIATE statement passing the generated SQL string. The format to use along with the EXECUTE IMMEDIATE statement to create objects is as follows:
EXECUTE IMMEDIATE SQL_string;
The SQL_string in this example is a dynamically created string that will create an object. In the case of creating objects, the USING clause is not used because you cannot use bind variables for substituting object names or attributes such as column names.
Please use care when concatenating user input variables with SQL text because the technique poses a security concern. Specifically, you open the door to the much-dreaded SQL injection attack. Refer to Recipe 8-14 for more details and for information on protecting yourself.