However, there are some scenarios where it may be appropriate to perform DDL from within PL/SQL, for example: 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.
Specifically: values that were inserted into the column before the trigger was defined or while the trigger was disabled.
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.
Constraint behavior depends on constraint state, as explained in .
Constraints are easier to write and less error-prone than triggers that enforce the same rules.
SIMPLE_SQL_NAME will raise the excpetion ORA44003: string is not a simple SQL name, which is passed back out of PKG_DDL_SQL: 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. 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.
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.
Without a column list, the trigger fires when any column of the associated table is updated.