I have to admit that using GUI applications like SQL Developer has its advantages but sometimes I still prefer using SQL*PLUS… more so because my roots are as an Oracle DBA. Old school techniques can still trump modern GUI’s. The reason I use SQL*PLUS is that it gives us the ability to generate DDL statements and spool the output to files/scripts which we auto execute, but the real beauty of this approach begins when we start using scripts that call other scripts that generate the DDL. I’ll explain in more detail…

The designs we usually follow are done in a consistent way e.g. for the following example of auto creating “INSTEAD OF” triggers

• We use a consistent naming convention for our views e.g. [TABLE_NAME]_VW
• Our base tables are single column primary keys, using SYS_GUID() to generate the key value

What this allows us to do is use the database data dictionary to generate the DDL for our “INSTEAD OF” triggers using the SQL below:

set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set termout off
define table_name='&1'
spool auto_crtrigger.sql
select '
CREATE OR REPLACE TRIGGER '||view_name||'_IOTD'||CHR(13)||CHR(10)||
'INSTEAD OF DELETE ON '||view_name||CHR(13)||CHR(10)||
'REFERENCING OLD AS OLD NEW AS NEW'||CHR(13)||CHR(10)||
'BEGIN'||CHR(13)||CHR(10)||
'  UPDATE '||replace(v.view_name, '_VW','')||CHR(13)||CHR(10)||
'  SET    deleted_on = sysdate'||CHR(13)||CHR(10)||
'  ,      deleted_by = nvl(v(''APP_USER''),USER)'||CHR(13)||CHR(10)||
'  WHERE  '||( SELECT cols.column_name
               FROM   all_constraints cons, all_cons_columns cols
               WHERE  cols.table_name = replace(v.view_name, '_VW','')
               AND    cons.constraint_type = 'P'
               AND    cons.constraint_name = cols.constraint_name
               AND    cons.owner = cols.owner
             )||' = : old.'||
            ( SELECT cols.column_name
               FROM   all_constraints cons, all_cons_columns cols
               WHERE  cols.table_name = replace(v.view_name, '_VW','')
               AND    cons.constraint_type = 'P'
               AND    cons.constraint_name = cols.constraint_name
               AND    cons.owner = cols.owner
            )||';'||CHR(13)||CHR(10)||
'END;'||CHR(13)||CHR(10)||
'/'
from user_views v
where view_name NOT IN ('List of views to omit')
/
spool off
set feedback on
set embedded off
set heading on
set verify on
set termout on
@auto_crtrigger.sql

Now the even cooler thing is when you start adding in PROMPT statements for more complex code generation, here’s a script named “crdeltrg.sql” e.g.

set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set termout off
define table_name='&1'
spool auto_crtrigger.sql
select '
create or replace trigger aud#del#'||substr(replace(replace(replace(replace('&table_name','SECURITY','SEC'), 'INCUMBENCY', 'INC'), 'APPLICATION','APPL'), 'EMPLOYEE','EMP'),1,22)||CHR(10)||
'after delete on &table_name'||CHR(10)||
'for each row'||CHR(10)||
'declare'||CHR(10)||
'      tColumnTable audit_utils.tColumnTable;'||CHR(10)||
'begin'
from dual;
select '      tColumnTable('||rownum||').column_name := '''||column_name ||''''||';'||CHR(10)||
'      tColumnTable('||rownum||').old_value := : old.' || column_name ||';'||CHR(10)||
'      tColumnTable('||rownum||').data_type := ''' || data_type || '''' ||';'||CHR(10)||
'      tColumnTable('||rownum||').operation := ''' || 'DELETE' || '''' ||';'
from user_tab_columns where table_name = '&table_name'
/
prompt    audit_utils.dml_audit( '&table_name', tColumnTable, 'DELETE');;
prompt end;;
prompt /
exec audit_utils.check_audit_table_exists('&table_name');
spool off
set feedback on
set embedded off
set heading on
set verify on
set termout on
@auto_crtrigger.sql

And then here’s how we call the above script which takes a table name parameter, simply using another script as per below (so we have scripts calling scripts which is quite powerful)

set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set termout off

spool tmp.sql
select '@crdeltrig.sql '||table_name from user_tables where table_name not in ('AUD#ERROR_LOG')
and table_name not like '%_AUDIT'
/
spool off
set feedback on
set embedded off
set heading on
set verify on
set termout on
@tmp

The above automatically creates all our application auditing DELETE triggers which use a central auditing package to log the before and after images…. Code generation, whether it’s via SQL*PLUS, PLSQL or APEX is the perfect way to boost productivity and maintenance. So consistency and naming conventions are not just important for readability and maintainability, it’s also provides you the ability to auto generate code at the application level!