CodeGen and APEX.

When building code to support your APEX application, it is always nice to be able to generate large parts of that code. I am really fond of creating code using the QDA. The only trouble is that you need to create all the packages separately. What I have found works for me is to create a single script for the sequence, all the packages and the trigger creation. This is something that had to be done by hand because QNXO generates separate files for the different parts of the Table API.
I could create a new script and just copy in all the lines in the other scripts, but what would happen if one was to change? I would have to make that change in multiple places. Something I would rather not do. So instead of creating a new script with copies of the current code I decided to copy the ‘install’ script and adapt that to create the complete install script. Instead of calling a different file using the @@ option in SQL*Plus (or PL/SQL Developer of course), I [INCLUDE]d the scripts I needed. This way I am using the exact same script which is being used when creating the QDA scripts ‘the old way’. I had a bit of a problem generating the sequence and the triggers. I appeared that some setup scripts were not run.
In the existing ‘tree’ these scripts were run at different levels of the hierarchy. I could of course create a similar hierarchy, but I don’t want that. I don’t like to be constrained to a special way to work. CodeGen supplies the possibility to run setup scripts prior to the actual script. There is my way out. I want to run some setup scripts prior to my actual script.
The setup scripts that need to be executed:
NAMING-CONVENTIONS-FOR:”QCGU Development Architecture for PL/SQL”
QDA_flags_for_table_processing
The actual script:
REM One full install script for the QDA code
REM Based on the existing scripts.
REM SPOD: Single Point Of Definition
REM (c) 2010: Bar Solutions
[include]primary_key_generation_controls
#
# Remove any lines that aren't relevant.
#
REM Remove any lines you don't need or don't apply to your situtation.

# 1.4 Do not always include this line.
[IF]{qda_generating_pky}[EQ]{qda_true}[AND]{pkycoldatatype_sql}[IN]INTEGER,NUMBER
CREATE SEQUENCE {sequence_name:[objname/a]};

[ENDIF]
[INCLUDE]Types Package Specification
SHOW ERRORS
[INCLUDE]Query Package Specification
SHOW ERRORS
[INCLUDE]Change Package Specification
SHOW ERRORS
[IF]{include_utility_packages}[EQ]{qda_true}
[INCLUDE]Utilities package specification
SHOW ERRORS
[ENDIF]
[INCLUDE]Query Package Body
SHOW ERRORS
[INCLUDE]Change Package Body
SHOW ERRORS
[IF]{include_utility_packages}[EQ]{qda_true}
[INCLUDE]Utilities package body
SHOW ERRORS
[ENDIF]
#
# 1.4 Apply logic here from the audit trigger script
[SETALIAS]l_pky_gen[TO]{qda_false}
[IF]{qda_generating_pky}[EQ]{qda_true}[AND]onepkycol
[SETALIAS]l_pky_gen[TO]{qda_true}
[ENDIF]
[IF]{l_pky_gen}[EQ]{qda_true}[OR][-]
{grp_int_table}[EQ]{qda_true}[OR][-]
{have_audit_columns}[EQ]{qda_true}
[INCLUDE]Audit triggers
[ENDIF]

And of course I need to copy the arguments from the scripts that are executed when I run the entire script group.
And there it is, a single script to install the packages for the QDA.

Leave a Reply

Your email address will not be published. Required fields are marked *