Package: PKG_DDL

In my last post, PKG_DDL_SQL, I talked about performing DDL from within PL/SQL and how I like to split the DDL logic into the two parts of:

1. Generate the DDL command
2. Execute the generated command

The first part is covered by the PKG_DDL_SQL package. This post looks at the second part; the execution of the DDL.

As noted previously, executing DDL can be as simple as:

EXECUTE IMMEDIATE <ddl_command>;

and if we are using PLG_DDL_SQL to create the DDL command then we can substitute a call to PKG_DDL_SQL for the DDL command, e.g.:

EXECUTE IMMEDIATE pkg_ddl_sql.truncate_table('MY_SCHEMA','MY_TABLE');

However, when it comes to DDL there are other considerations beyond the simple execution of the command. These include:

• Security
• Logging of actions
• Ability to disable DDL execution

Security

The ability to execute DDL from within PL/SQL is quite powerful given that it is easy to destroy your database in many different ways. I have worked at places that have implemented wonderfully generic routines along the lines of:

PROCEDURE do_ddl (ddl_command IN VARCHAR2)
AS
BEGIN
   EXECUTE IMMEDIATE ddl_command;
END do_ddl;

and widely granted access to such a procedure. Given that the routine will execute with the privileges of the owner of the routine and not the invoker this strategy is simply asking for trouble. To restrict the amount of dammage that can be done in this type of scenario, at a minimum AUTHID CURRENT_USER should be used to ensure that the DDL is executed with the privileges of the invoker and not the owner.

Logging of actions

One of the key reasons for splitting the generation of the DDL command from its execution is that it permits the action to be logged moments before it is invoked. As mentioned in previous posts, instrumentation of code is key to figuring out what happened when things go wrong. DDL is non-transactional so when an incorrect command is executed you can’t simply rollback and pretend nothing happened. Chances are your process has created a mess that needs to be cleaned up and the log of what got executed is your starting point for that clean up activity.

Ability to disable DDL execution

Along with logging of the DDL, during the development you will probably want to test out what your process will do without actually executing it. When playing around with dropping objects or truncating tables, one logical slip and you’ll have your coworkers rather irritated that they will have to wait around for the database to be restored. Generating the DDL commands that will be executed, logging them but not actually executing them allows you to confirm that your process will do what you expect… or, if you please, confirm that you would have trashed your database if you had actually executed the commands. Building some sort of switch into the code to disable execution makes testing things rather convenient.

PKG_DDL

With all of the above in mind, PKG_DDL is the package structure I usually implement to handle executing DDL in a procedural manner.

The package specification simply exposes routines that perform simple DDL operations, e.g.:truncate_table, disable_trigger, etc. As well as routines that execute what are the commands returned by PKG_DDL_SQL there are routines that execute a series of DDL commands, such as disable_table_triggers, which disables all triggers on the table specified. Other than the DDL routines there are routines to control the overall behaviour of the package:

• enable – enables the actual execution of the DDL commands
• disable – disables the executions of the DDL commands
• enable_display – enables the display of the SQL commands before they are executed, via DBMS_OUTPUT
• disable_display – disables the display of the SQL commands before they are executed

The above routines are toggles which once set stay in effect for the duration of the session. By default, I tend to have the execution enabled but the display disabled. This can be easily changed as both a controlled via default values in the package body.

You’ll note that PKG_DDL is declared with AUTHID CURRENT_USER. This allows for PKG_DDL to be associated with a utility type account and can be granted from there without creating significant security concerns. Specific processes that require exeucting DDL can then call the package and the DDL will be executed with the rights of those packages.

Within the body of PKG_DDL each routine is quite simple. The basic logic is to generate the DDL command, via a call to PKG_DDL_SQL, and execute it. For routines that execute multiple DDL commands you’ll find simple FOR loops that process each individual action in turn, e.g. disable_table_triggers loops through the individual triggers on the table specified and disables each one.

Execution of any DDL is done from within the private EXECUTE_STRING procedure. This routine controls the logging, display and whther the command is actually executed or not. I consider logging of the DDL to be separate to the display of the DDL, which is done via DBMS_OUTPUT. Some people may think this is unnecessary and so are free to either merge these or remove whichever mechanism they do not require.

You will also note that package utilises other packages I have noted in previous posts, namely:

• PKG_DB_TYPE for variable declarations, such as t_object_name and t_big_string
• PKG_APP_INFO for setting and restoring the session ACTION setting
• PKG_ERR for handling any exceptions that occur

Note that application logging is done from within the procedure LOGGER and I have yet to blog about the package referenced, PKG_LOG. Simply substitute whatever logging mechanism that you use.

Exception handling is limited to calling the LOG_LAST_ERR routine and raising the exception to the next level. At a minimum PKG_DDL should provide you with a log of the DDL command that was executed and the exception that was raised. Working out what needs to be done from there tends to be rather simple.

I have found that this approach to DDL is easily extensible; it’s just a matter of adding a DDL command generation routine to PKG_DDL_SQL and a correspodning routine to PKG_DDL. It’s also easy to create complex DDL sequences by stringing together the individual routines. So, whenever faced with the need to execute DDL from within a PL/SQL process I make a quick judgement call as to whether the command is general enough to be reusable in other situations and, if so, I will include it in PKG_DDL. I note that the package currently lacks commands to toggle the visibility of indexes, setting indexes to be unuable, rebuilding indexes and altering the file associated with external tables so it look slike i’ll be updating these packages in the not too distiant future…

---

Download scripts shown in this post.