Too many DBAs insist that DDL commands should never be fired in a production environment. Therefore, they often try to prevent DDLs from happening by any means, whether technical or organizational. In addition to strict internal policies, the authors have encountered database-level triggers that limit the whole system to only basic DML operations.
Of course, tightening security is not such a bad idea, but it should not stop you from providing the required functionality. Because of Dynamic SQL, DDL statements are no longer the exclusive prerogative of DBAs’ SQL*Plus scripts. They can be directly integrated into PL/SQL code and become a critical part of the overall solution. Although creating real database objects on the fly can indeed “cross the line” in a lot of formally regulated environments, there are legitimate cases that will still pass the strictest checks.
One such case comes from the authors’ experience maintaining a multi-tier system in which the communication between the middle tier and the database was accomplished using a connection pool. Eventually, the following problem was observed. At the end of the day, each session from the pool locked a significant number of segments from the TEMP tablespace. The reason was that one part of the system used global temporary tables (GTTs), defined as ON COMMIT PRESERVE. Once these GTTs acquire a segment, they do not release it even if you delete all rows. Obviously, the only way to reset the high-water mark is to use the TRUNCATE command. But TRUNCATE is a DDL statement and cannot be used in straight PL/SQL! That’s where Dynamic SQL comes to the rescue. The following module resets all GTTs in the defined session if any one of them was touched:
Unfortunately, there is no simple way to find out exactly which table was touched, so TRUNCATE is fired for all of them. But if the total number of GTTs is reasonably small, the overhead can be tolerated.
TIP
Remember that all DDL statements (even unsuccessful ones) always fire COMMIT before doing anything else. This implicit COMMIT should be taken into consideration if you add DDLs using Dynamic SQL to your procedural logic. Depending upon the requirements, you may consider using autonomous transactions to protect uncommitted changes.
Leave a Reply