examples.htm

Oracle PL/SQL procedure generator (second generator type)

<OL>
<LI>Compile the JFile.java class and load it into Oracle.</LI>
<LI>Compile the XFile.pkg PL/SQL package.</LI>
</OL>
<P><FONT FACE="Arial" SIZE="4">Container for Array</FONT></P>
<P>Define a package to hold the file information.</P>
<P></P>
<PRE>   CREATE OR REPLACE PACKAGE dirinfo
      IS
         TYPE files_rt IS RECORD (
            dir VARCHAR2(200),
            separator CHAR(1),
            name VARCHAR2(200),
            bytes PLS_INTEGER
         );
         
         TYPE files_t IS TABLE OF files_rt
            INDEX BY BINARY_INTEGER;
         
         file files_t;
      END;
      / </FONT></PRE>

<P>Now make sure that the PL/Generator schema have access to the package:</P>
<P>GRANT EXECUTE ON dirinfo TO PUBLIC;</FONT>
</P>
<P><FONT FACE="Arial" SIZE="4">Populate the Array</FONT></P>
<P>I put all the steps into a stored procedure so that I can call it easily. 
</P>
<P></P>
<PRE>   CREATE OR REPLACE PROCEDURE listfiles (dir IN VARCHAR2, sep IN VARCHAR2 := '\')
      IS
         xfiles PLVtab.vc2000_table;
         v_row PLS_INTEGER;
      BEGIN
         /* Define the array; need schema since it is not a public synonym. */
         PLGCGML.defarray ('dirinfo', 'file', USER);
         
         /* Empty and then populate the array. */
         dirinfo.file.DELETE;
         
         XFile.getDirContents (
            dir,
            '%.pks', 
            xfiles,
            match_case =&gt; FALSE);
            
         v_row := xfiles.FIRST;
         LOOP
            EXIT WHEN v_row IS NULL;
            
            dirinfo.file(v_row).dir := dir;
            dirinfo.file(v_row).separator := sep;
            dirinfo.file(v_row).name := xfiles(v_row);
            dirinfo.file(v_row).bytes := 
               xfile.length (dir || sep || xfiles(v_row));
               
            v_row := xfiles.NEXT (v_row);
         END LOOP;
         
         /* Generate the text. */
         PLGCGML.genFile (
            'dirinfo.gdr', 
            show =&gt; true, 
            delarray =&gt; FALSE
            );
      END;
</FONT></PRE>

<P>/ <B><FONT FACE="Arial" SIZE="4"></FONT></B></P>
<P>CGML Solution </P>
<P></P>
<PRE>   #dirinfo.gdr
      [STOREIN]dirinfo.txt
      [FOREACH]file
         [dir][separator][name] contains [bytes] characters.
      [ENDFOREACH]</FONT></PRE>

<P><B><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></B></P>
<UL>
<LI>Well, the really wonderful thing is that I have defined a new array and
deployed it in my CGML script. The CGML commands themselves are &quot;the same
old thing&quot;.</LI>
<LI>On top of that, I have leveraged Java to give me information about
operating system files that would otherwise be impossible to obtain with 100%
PL/SQL code</LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B></P>
<P>I issued this command in SQL*Plus:</P>
<P>SQL&gt; exec listfiles ('c:\temp');</FONT>
</P>
<P>and here is the generated text:</P>
<P></P>
<PRE>   c:\temp\te_employee.pks contains 15334 characters.
      c:\temp\te_loc.pks contains 7296 characters.
      c:\temp\te_job.pks contains 7528 characters.
      c:\temp\te_locemp.pks contains 8896 characters.
      c:\temp\te_department.pks contains 9109 characters.
      c:\temp\te_salhist.pks contains 9893 characters.
      c:\temp\te_charge_items.pks contains 35522 characters.</FONT></PRE>

<P>Pretty darn cool, if we at Quest Software do say so ourselves!</P>
<HR>
<P><A NAME="9"><B><I><FONT FACE="Arial" SIZE="5">Create an audit database
trigger for updates to tables in your schema.</FONT></I></B></A></P>
<P>A common requirement it to create an audit of changes made to a table.
Oracle provides an audit facility, but it usually does not support the
granularity needed: which column changed? What was the old value? What is the
new value? When did the change take place?</P>
<P>This code can be very tedious to write (to say the least), largely because
the only way to compare the old and new values is by referencing the :OLD and
:NEW &quot;pseudo-records&quot;. These are data structures available only
within the database trigger, and they cannot be passed like records through a
parameter list. You must, therefore, write all of the comparison code and audit
logic in each trigger. Yuch!</P>
<P>An additional complication is that if you want to record only changes made
to the table, you need to compare the old and new column values, but you also
need to take into account NULL comparisons. If it was NULL before and is NOT
NULL now, the &quot;=&quot; and &quot;!=&quot; operators will not work as
expected.</P>
<P>This scenario is addressed very cleanly by CGML. You just &quot;write it
once&quot; and then deploy it to any table.</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>You will find all of the text shown below in the audit.gdr file. First, I
need to create my audit table:</P>
<P></P>
<PRE>   DROP TABLE [objname]_aud;

      CREATE TABLE [objname]_aud  (
      [FOREACH]col
         [colname]_o [data_declaration], 
      [ENDFOREACH]   
      [FOREACH]col
         [colname]_n [data_declaration], 
      [ENDFOREACH]   
      [FOREACH]col
         [colname]_f CHAR(1), 
      [ENDFOREACH]
         created_on DATE,
         created_by VARCHAR2(30)   
         );</FONT></PRE>

<P>I create sets of columns for old values, new values and also a flag for each
column indicating if there was a change. I also throw in audit columns for the
audit row itself.</P>
<P>Next I construct the header for trigger:</P>
<P></P>
<PRE>   CREATE OR REPLACE TRIGGER [objname]_upd_audit
      AFTER UPDATE ON [objname]
      FOR EACH ROW
      DECLARE
         audit_rec [objname]_aud%ROWTYPE;
      BEGIN</FONT></PRE>

<P>and then I build my IF statement. Notice all the code to handle the various
NULL-NOT NULL scenarios:</P>
<P></P>
<PRE>   [FOREACH]col
      IF :OLD.[colname] != :NEW.[colname] OR
         (:OLD.[colname] IS NULL AND :NEW.[colname] IS NOT NULL) OR
         (:OLD.[colname] IS NOT NULL AND :NEW.[colname] IS NULL)
      THEN
         audit_rec.[colname]_f := 'Y';
         audit_rec.[colname]_n := :NEW.[colname];
         audit_rec.[colname]_o := :OLD.[colname];
      ELSE
         audit_rec.[colname]_f := 'N';
      END IF;
      [ENDFOREACH]</FONT></PRE>

<P>Finally, it is time for the INSERT:</P>
<P></P>
<PRE>   INSERT INTO [objname]_aud VALUES (
      [FOREACH]col
         audit_rec.[colname]_O, 
      [ENDFOREACH]   
      [FOREACH]col
         audit_rec.[colname]_N, 
      [ENDFOREACH]   
      [FOREACH]col
         audit_rec.[colname]_f,
      [ENDFOREACH]   
         SYSDATE,
         USER
         );</FONT></PRE>

<P><B><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></B></P>
<UL>
<LI>The [data_declaration] object tag allows me to very easily reconstruct the
table column definitions. I want to do this rather than a CREATE TABLE AS
SELECT FROM because I want to leave all the constraints behind.</LI>
<LI>I perform multiple passes through the col array to construct my various
sets of columns.</LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B></P>
<P>Here is the table creation statement for the department audit table:</P>
<P></P>
<PRE>   CREATE TABLE department_aud  (
         department_id_o NUMBER (2),
         name_o VARCHAR2 (14),
         loc_id_o NUMBER (3),
         department_id_n NUMBER (2),
         name_n VARCHAR2 (14),
         loc_id_n NUMBER (3),
         department_id_f CHAR(1),
         name_f CHAR(1),
         loc_id_f CHAR(1),
         created_on DATE,
         created_by VARCHAR2(30)
         );</FONT></PRE>

<P>As for the trigger itself, it gets kind of long and repetitive, even for a
small table like department, so I will show you one of the IF statements and
then skip down to the INSERT:</P>
<P></P>
<PRE>   CREATE OR REPLACE TRIGGER department_upd_audit
      AFTER UPDATE ON department
      FOR EACH ROW
      DECLARE
         audit_rec department_aud%ROWTYPE;
      BEGIN
         IF :OLD.department_id != :NEW.department_id OR
            (:OLD.department_id IS NULL AND :NEW.department_id IS NOT NULL) OR
            (:OLD.department_id IS NOT NULL AND :NEW.department_id IS NULL)
         THEN
            audit_rec.department_id_f := 'Y';
            audit_rec.department_id_n := :NEW.department_id;
            audit_rec.department_id_o := :OLD.department_id;
         ELSE
            audit_rec.department_id_f := 'N';
         END IF;
         ...
         INSERT INTO department_aud VALUES (
            audit_rec.department_id_O,
            audit_rec.name_O,
            audit_rec.loc_id_O,
            audit_rec.department_id_N,
            audit_rec.name_N,
            audit_rec.loc_id_N,
            audit_rec.department_id_f,
            audit_rec.name_f,
            audit_rec.loc_id_f,
            SYSDATE,
            USER
            );
      END;</FONT></PRE>
      

<P>/</P>
<HR>
<P><A NAME="10"><B><I><FONT FACE="Arial" SIZE="5">Create an entire insert
procedure that accepts as IN arguments every column in a table, performs the
insert and if a duplicate is found for the primary key, automatically switches
to an UPDATE.</FONT></I></B></A></P>
<P>An insert procedure header surely needs to be implemented at some point. So
let's do it now!</P>
<P>TO BE COMPLETED</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></B></P>
<P>#insproc.gdr</FONT></P>
<P><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></P>
<UL>
<LI>I use two different column arrays: pkycol and nonpkycol </LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B></P>
<HR>
<P><A NAME="11"><I><B><FONT FACE="Arial" SIZE="5">Customize the PL/Generator Table
Encapsulation driver to generate customized code specific to each table in your
schema.</FONT></I></B></A></P>
<P>PL/Generator allows you to customize the table encapsulation packages it
generates by setting up text in separate customization files. These files are
then merged into the driver to produce the final packaged code. You can use
CGML to write customizations that will be applied to more than one table,
automatically adapting to the specific structures of that table in each case. 
</P>
<P>TO BE COMPLETED</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>CGML Highlights </P>
<P>Sample of Generated Text </P>
<HR>
<P><A NAME="12"><I><B><FONT FACE="Arial" SIZE="5">Construct procedures to manage
your packaged cursors more easily.</FONT></I></A></B></P>
<P>TO BE COMPLETED</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>#pkgcur.gdr</FONT></P>
<P><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></P>
<P>Sample of Generated Text </P>
<HR>
<P><A NAME="13"><I><FONT FACE="Arial" SIZE="5"><B>Provide a general solution
for mutating table problems with index-by tables of records.
</B></FONT></I></A></P>
<P>TO BE COMPLETED</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>CGML Highlights </P>
<P>Sample of Generated Text </P>
<HR>
<P><A NAME="14"><I><b><FONT FACE="Arial" SIZE="5">Generate HTML documentation for
a table</FONT></I></A></b></P>
<P>TO BE COMPLETED</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>CGML Highlights </P>
<P>Sample of Generated Text </P>
<HR>
<P><A NAME="15"><I><b><FONT FACE="Arial" SIZE="5">Generate a PL/SQL program that
wraps a Java Stored Procedure and makes the Java technology available in
PL/SQL.</FONT></I></b></A></P>
<P>TO BE COMPLETED</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>CGML Highlights </P>
<P>Sample of Generated Text </P>
</BODY>
</HTML>