Performing DDL from within PL/SQL is not natively supported by the language. So, the following script will generate an error:
BEGIN DROP TABLE blah; END;
Yet this is trivially easy to work around is limitation as the DDL command can simply be embedded in an EXECUTE IMMEDIATE call, i.e.:
BEGIN EXECUTE IMMEDIATE 'DROP TABLE blah'; END;
This approach to executing SQL is termed dynamic SQL as the SQL is constructed at run time, rather than being specified at compile time. Many Oracle experts will tell you that performing DDL from within PL/SQL is not good practice and there are good reasons for this advice. Generally speaking it’s not necessary. Database objects, such as tables, indexes, sequences, etc should be constructed in advance of the processes that operate on those objects. Stored procedures that operate on database objects won’t even compile if the objects are missing thanks to the way Oracle tracks dependencies. DDL is non-transactional and so is completely inappropriate for OLTP operations.
However, there are some scenarios where it may be appropriate to perform DDL from within PL/SQL, for example:
- Automating regular maintenance activities, such as table partition management
- ETL processes where it is necessary to do things such as disabling indexes and constraints
The approach to DDL I prefer to adopt is to split the process into the steps of:
- generating the necessary SQL, and
- execting that SQL.
By splitting the process I can have a simple package of reusable routines that returns me a SQL statement for each DDL activity that I wish to support. Another package can then take the generated DDL and execute it. Into this second package I can place whatever controls and processes that I want around the DDL execution, such as logging, security checks, etc. I can also be more selective on the latter package so that only certain activities are exposed.
The package I use for creating the DDL SQL statements is called PKG_DDL_SQL. This package contains a set of functions, each one returning a DDL string for a specific action. Each function accepts parameters appropriate to the DDL action. Where deemed appropriate default values for those parameters have been used. The returned value is a string type, using t_BIG_STRING types from the previously described PKG_DB_TYPE package (Hey, I don’t have an active imagination so “big string” is the best I could do!)
PKG_DDL_SQL contains routines to:
- truncate tables, partitions and subpartitions
- disable and enable constraints
- compile PL/SQL objects such as packages, procedures, functions and triggers
- drop objects, such as tables, indexes and PL/SQL code objects
- rename tables, partitions and subpartitions
So, as an example, if I wanted the DDL for renaming a table I could write:
BEGIN
dbms_output.put_line (pkg_ddl_sql.rename_table (p_table_owner => 'TABLE_OWNER'
,p_table_name => 'OLD_TABLE_NAME'
,p_new_name => 'NEW_TABLE_NAME'));
END;
and the response output is:
ALTER TABLE TABLE_OWNER.OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME
Generally speaking PKG_DDL_SQL is relatively “dumb”; it creates DDL statements for the inputs provided and does little else. However, it does add one bit of value to the overall process of executing dynamic DDL; it checks that identifiers specified are valid names. This check is done via the function DBMS_ASSET.SIMPLE_SQL_NAME and is designed to prevent SQL injection attacks. If a parameter for an object is not a valid name then DBMS_ASSET.SIMPLE_SQL_NAME will raise the excpetion ORA44003: string is not a simple SQL name, which is passed back out of PKG_DDL_SQL:
BEGIN
dbms_output.put_line (pkg_ddl_sql.rename_table (p_table_owner => 'TABLE_OWNER'
,p_table_name => 'INVALID''NAME'
,p_new_name => 'NEW_TABLE_NAME'));
END;
which results in:
BEGIN * ERROR at line 1: ORA-44003: invalid SQL name ORA-06512: at "SYS.DBMS_ASSERT", line 160 ORA-06512: at "DEVELOPER.PKG_DDL_SQL", line 530 ORA-06512: at line 2
Note, it is deliberate that PKG_DDL_SQL simply checks the validity of the identifiers and does not actually confirm their existence in the database, as could be done using DBMS_ASSET.SQL_OBJECT_NAME function. This is to permit the package to be used to generate DDL for objects that may not exist as at the point in time that the DDL is generated. Of course, you are welcome to alter the package if you wuld rather it behave otherwise.
Download scripts shown in this post.
Mark Hoxey 