Build scripts with the help of the dictionary

It is quite common when working with databases that often have to perform any job creation or alteration of structures, analysis, compilation, etc. objects on the database. This tends to create a DDL script with a number of judgments, which in most cases the only thing that changes is the name of the object to be treated. 
You can save a lot of work using the dictionary of the database to dynamically build these judgments.

As an example will create a new field to store the date of creation of records in all tables in a schema of an Oracle database. To do so would use the following statement:

SELECT 'ALTER TABLE' | | OWNER | | '. " | | TABLE_NAME | | 
'ADD FECHA_CREACION DATE DEFAULT SYSDATE,' 
FROM ALL_TABLES 
WHERE OWNER = 'HR';

The result would be something like this:

HR.DEPARTMENTS ALTER TABLE ADD FECHA_CREACION DATE DEFAULT SYSDATE; 
HR.EMPLOYEES ALTER TABLE ADD FECHA_CREACION DATE DEFAULT SYSDATE; 
HR.JOB_HISTORY ALTER TABLE ADD FECHA_CREACION DATE DEFAULT SYSDATE; 
ALTER TABLE ADD HR.JOBS FECHA_CREACION DATE DEFAULT SYSDATE; 
HR.LOCATIONS ALTER TABLE ADD FECHA_CREACION DATE DEFAULT SYSDATE; 
HR.REGIONS ALTER TABLE ADD FECHA_CREACION DATE DEFAULT SYSDATE; 
HR.COUNTRIES ALTER TABLE ADD FECHA_CREACION DATE DEFAULT SYSDATE;

Now remove only save these judgments in a script and 
execute, or launch directly from the application 
use to interact with our database

To have to (or prefer) to work from a terminal or online 
commands, how to do this would be the same with SQLPLUS 
follows:

SQL> SET HEADING OFF 
SQL> SET FEEDBACK OFF 
SQL> SPOOL C: \ campo_auditoria.sql 
SQL> SELECT 'ALTER TABLE' | | OWNER | | '. " | | TABLE_NAME | | 
'ADD FECHA_CREACION DATE DEFAULT SYSDATE,' 
FROM ALL_TABLES 
WHERE OWNER = 'HR'; 
SQL> SPOOL OFF; 
SQL> SET FEEDBACK ON 
SQL> SET HEADING ON

And finally run the script generated, although we recommend a review of the sentences generated 
SQL> @ C: \ campo_auditoria.sql