CGML

I saw this from OTN and found it very useful...every programmer wants to be a lazy one, me too...[@more@]

Table of Contents

• How to use the examples
• Create a header for an insert procedure that accepts as IN arguments every column in a table
• Create a function to check for equality between two records based on the same table %ROWTYPE
• Create cursors to query from a table by primary key and foreign keys
• Generate a procedure to write the contents of a particular table to a file, with column values separated by a delimiter you pass as an argument
• Build a package that transfers the contents of a particular table-based record through database pipes
• Create DROP statements for all the tables and views in your schema
• Defining an Array of Files in a Directory
• Create an audit database trigger for updates to tables in your schema
• Create an entire insert procedure that accepts as IN arguments every column in a table
• Customize the PL/Generator Table Encapsulation driver to generate customized code specific to each table in your schema
• Construct procedures to manage your packaged cursors more easily
• Provide a general solution for mutating table problems with index
• Generate HTML documentation for a table
• Generate a PL/SQL program that wraps a Java Stored Procedure

---

INTRODUCTION

The best way to learn to use CGML is to examine and play around with examples. This document offers many different "templates" or CGML scripts that provide a set of instructions to the PL/Generator engine. We call them templates because they represent in a generic way structures of code that you want to generate for a specific database object or other data structure you have identified to PL/Generator.

You can run these templates yourself by taking these steps:

1. Follow the directions in the section titled "Working with CGML" to set up your test environment.

1. For each challenge, copy your CGML file to testCGML.gdr in your default directory.
2. Run the test.sql script in SQL*Plus. It will display your results on the screen and also write them to the CGML.tst file.

When you run test.sql, you will be prompted for the name of the table against which you want to generate your text. You can easily make copies of this file to run for specific tables or even to avoid the specification of a database object all together.

Each example proposes a challenge and then offers the CGML Solution (the CGML commands needed to achieve the goal), a description of the CGML Highlights (CGML features of note used in the solution) and a Sample of Generated Text (so you can get a feel for what you should see yourself when you run the script).

---

Create a header for an insert procedure that accepts as IN arguments every column in a table.

This procedure would be just one element of a more comprehensive encapsulation of a table behind a layer of PL/SQL code. This layer gives you better control over data integrity and improved (consistent) error handling.

CGML Solution

You will want to obtain the column information from two arrays: pkycol and nonpkycol (there is not single array for all columns). The following text uses a loop to scan through the array and construct IN argument syntax.

   # inshdr.gdr
      [STOREIN]CGML.tst
     PROCEDURE insert_row (
      [FOREACH]col[BETWEEN],
         [colname]_in IN [data_type]
      [ENDFOREACH]
         );

Notice that I construct an IN parameter based on the column name and its datatype. An even better approach would be to avoid hard-coded datatype declarations and instead rely on anchored datatypes as follows:

   #inshdr2.gdr
      [STOREIN]CGML.tst
      PROCEDURE insert_row (
      [FOREACH]col[BETWEEN],
         [colname]_in IN [objname].[colname]%TYPE
      [ENDFOREACH]
         );

CGML Highlights

• The STOREIN command directs output to the desired file.
• The FOREACH loop goes through each column in the table, using BETWEEN to add a column between each parameter, except for the last.

Sample of Generated Text

Here is the output based on the department table, for both approaches:

   PROCEDURE insert_row (
         department_id_in IN NUMBER,
         name_in IN VARCHAR2,
         loc_id_in IN NUMBER
         );

      PROCEDURE insert_row (
         department_id_in IN department.department_id%TYPE,
         name_in IN department.name%TYPE,
         loc_id_in IN department.loc_id%TYPE
         );

---

Create a function to check for equality between two records based on the same table %ROWTYPE.

This is a handy example, since PL/SQL does not let you do boolean checks between records. The following logic is, for example, highly desirable but not compilable:

   DECLARE
         rec1 employee%ROWTYPE;   
        rec2 employee%ROWTYPE;
      BEGIN
         IF rec1 = rec2 /* Only in your dreams! */

Instead, you must compare the values in corresponding fields of each record. And then, of course, you have to worry about NULL values. It can get complicated and, for large records, it can get downright tedious.

CGML Solution

Here's some CGML that will take care of this problem (for non-object database tables, anyway!):

   #recseq.gdr
      [STOREIN][objname].req
      CREATE OR REPLACE FUNCTION [objname]_recseq (
         rec1 IN [objname]%ROWTYPE, 
         rec2 IN [objname]%ROWTYPE
         )
      RETURN BOOLEAN
   IS
      unequal_records EXCEPTION;
      retval BOOLEAN;
   BEGIN
   [FOREACH]col
      retval := rec1.[colname] = rec2.[colname] OR
         (rec1.[colname] IS NULL AND rec2.[colname] IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;
  [ENDFOREACH]
      RETURN TRUE;
   EXCEPTION
      WHEN unequal_records THEN RETURN FALSE;
   END;
   /

CGML Highlights

• In this STOREIN command, I have even made the name of the output file change with the name of the table. This comes in handy when you want to avoid overwriting an existing file and also just generate a whole lot of text for a variety of tables.

Sample of Generated Text

Here is an example of the output from this CGML for the location or loc table:

   CREATE OR REPLACE FUNCTION loc_recseq (
      rec1 IN loc%ROWTYPE,
      rec2 IN loc%ROWTYPE
      )
   RETURN BOOLEAN
   IS
      unequal_records EXCEPTION;
      retval BOOLEAN;
   BEGIN
      retval := rec1.loc_id = rec2.loc_id OR
         (rec1.loc_id IS NULL AND rec2.loc_id IS NULL);
      IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;

      retval := rec1.regional_group = rec2.regional_group OR
         (rec1.regional_group IS NULL AND rec2.regional_group IS NULL);
         IF NOT NVL (retval, FALSE) THEN RAISE unequal_records; END IF;

         RETURN TRUE;
      EXCEPTION
         WHEN unequal_records THEN RETURN FALSE;
      END;
      /

As you can see, the only way I can get to the RETURN TRUE statement is if the corresponding fields have matching values or both are NULL.

And I am sure that it is easy for you to see how to extend this to compare three or even four different records, as you may need.

---

Create cursors to query from a table by primary key and foreign keys.

We usually write the same kind of cursors over and over again in our programs. Get me the row for a given primary key, fetch through all the rows for a particular foreign key value set, etc. Why not just generate it instead?

CGML Solution

This solution is a bit long, but it sure is better than writing those queries again and again!

   #cursors.gdr
      [STOREIN][objname].crs
         CURSOR [objname]_bypky 
         IS
            SELECT
            [FOREACH]col[between],  
               [colname] 
            [ENDFOREACH]          
              FROM [objname]
             ORDER BY
      [FOREACH]pkycol[between],
               [colname]
      [ENDFOREACH]
            ;
         CURSOR [objname]_forpky (
      [FOREACH]pkycol[between],
               [colname]_in IN [coldatatype]
      [ENDFOREACH]
            ) 
         IS
            SELECT
            [FOREACH]col[between],  
               [colname] 
            [ENDFOREACH]          
              FROM [objname]
             WHERE
             [FOREACH]pkycol[between] AND
                [colname] = [colname]_in
             [ENDFOREACH]
            ;
      [FOREACH]fky
         CURSOR row_for_[fkyname] (
         [FOREACH]fkycol[between],
               [colname]_in IN [coldatatype]
         [ENDFOREACH]
            )
         IS
            SELECT
         [FOREACH]col[between],  
               [colname] 
         [ENDFOREACH]          
              FROM [objname]
             WHERE
         [FOREACH]fkycol[between] AND 
                [colname] = [colname]_in
         [ENDFOREACH] 
            ;

      [ENDFOREACH]fky

CGML Highlights

• As you can see, I can loop through all the columns in the table, just the columns in the primary key or just the columns in a particular foreign key. No matter which loop, I can reference the same database tags [colname] and [coldatatype].
• I am taking advantage of nested loops in this example: For each foreign key, for each column in each foreign key. There is no limitation to the nestings of loops in CGML.
• I can place the name of the foreign key directly inside the cursor name to make sure the resulting cursor is unique.

Sample of Generated Text

Here is part of the output from this CGML text for the employee table:

      CURSOR employee_bypky
         IS
            SELECT
               employee_id,
               last_name,
               ...
               department_id
              FROM employee
             ORDER BY
               employee_id
            ;

         CURSOR employee_forpky (
               employee_id_in IN number
            )
         IS
            SELECT
               employee_id,
               last_name,
               ...
              FROM employee
             WHERE
                employee_id = employee_id_in
            ;

         CURSOR row_for_EMP_DEPT_LOOKUP (
               department_id_in IN number
            )
         IS
            SELECT
               employee_id,
               last_name,
               ...
               department_id
              FROM employee
             WHERE
                department_id = department_id_in
            ;

You can also take a similar step for all of your table's indexes. I will leave that as an exercise for the PL/Generator user.

The best approach, by the way, would be to put all of these cursors inside a package, so that they can be reused throughout your application set.

---

Generate a procedure to write the contents of a particular table to a file, with column values separated by a delimiter you pass as an argument.

A handy utility, but another tedious task. Just imagine a table with 100 columns! You also need to know about the UTL_FILE package.

CGML Solution

This procedure offers default values for the file name and the delimiter, so that you can call it with nothing more than the directory in which the file should be placed, as in:

SQL> exec loc2file ('c:temp');

Whenever you need a "table to file" procedure for another table, just generate it from:

   #tab2file.gdr
      [STOREIN][objname]2file.sp
      CREATE OR REPLACE PROCEDURE [objname]2file (
         loc IN VARCHAR2,
         file IN VARCHAR2 := '[objname].dat', 
         delim IN VARCHAR2 := '|'
         )
      IS
         fid UTL_FILE.FILE_TYPE;
         line VARCHAR2(32767); -- VARCHAR2(1023); prior to 8.0.5
      BEGIN
         fid := UTL_FILE.FOPEN (loc, file, 'W');

         FOR rec IN (SELECT * FROM [objname])
         LOOP
            line := 
         [FOREACH]col
            [IF][coldatatype][eq]VARCHAR2
               rec.[colname] || delim ||
            [ELSIF][coldatatype][eq]CHAR
               rec.[colname] || delim ||
            [ELSIF][coldatatype][EQ]DATE
               TO_CHAR (rec.[colname]) || delim ||
            [ELSIF][coldatatype][EQ]NUMBER
               TO_CHAR (rec.[colname]) || delim ||
            [ELSIF][coldatatype][EQ]INTEGER
               TO_CHAR (rec.[colname]) || delim ||
            [ELSE]
               rec.[colname] || delim ||
            [ENDIF]
         [ENDFOREACH]
               NULL;
            UTL_FILE.PUT_LINE (fid, line);
         END LOOP;
         UTL_FILE.FCLOSE (fid);
      END;
      /

CGML Highlights

• I use an IF ELSIF statement to check the datatype of each column. I can then build the string properly (numbers and dates have to be TO_CHAR-ed, but strings can be concatenated directly into the line.
• Notice that I couldn't put the string "|| delim ||" on the [FOREACH]col statement with a BETWEEN clause. That is due to a current restriction in CGML; namely, that if I have a nested loop or IF statement inside a loop, the BETWEEN on the outer loop will not be propagated throughout the body of the loop.

Sample of Generated Text

   CREATE OR REPLACE PROCEDURE department2file (
         loc IN VARCHAR2,
         file IN VARCHAR2 := 'department.dat',
         delim IN VARCHAR2 := '|'
         )
      IS
         fid UTL_FILE.FILE_TYPE;
         line VARCHAR2(32767); -- VARCHAR2(1023); prior to 8.0.5
      BEGIN
         fid := UTL_FILE.FOPEN (loc, file, 'W');

         FOR rec IN (SELECT * FROM department)
         LOOP
            line :=
               TO_CHAR (rec.department_id) || delim ||
               rec.name || delim ||
               TO_CHAR (rec.loc_id) || delim ||
               NULL;
            UTL_FILE.PUT_LINE (fid, line);
         END LOOP;
         UTL_FILE.FCLOSE (fid);
      END;
      /

And just think how much harder it would be to build (but relatively easy to generate) if you wanted to pass a dynamic WHERE clause. Then you would need to bring DBMS_SQL to bear on the problem! I will leave that as an exercise for the CGML student.

---

Build a package that transfers the contents of a particular table-based record through database pipes (procedure to pack/send, procedure to receive/unpack).

If you are going to take advantage of database pipes, you need to know how to program with DBMS_PIPE. And if you want to move the contents of a table's row through a pipe via a PL/SQL record, you have to pack each individual field value to send it, and then unpack every field after receiving the message. Lots of repetitive, mind-numbing, unproductive work. Or you can use CGML...

CGML Solution

Let's start with the package specification:

   #tabpipe.gdr
      [STOREIN][objname]_pipe.pkg
      CREATE OR REPLACE PACKAGE [objname]_pipe
      --// Wrapper around pipe based on [objname]
      --// Designed by Steven Feuerstein, Quest Software
      IS
         c_name CONSTANT VARCHAR2(200) := '[objname]_pipe';

         PROCEDURE send (
      [FOREACH]col
            [colname]_in IN [coldatatype], 
      [ENDFOREACH]
            wait IN INTEGER := 0
            );

         PROCEDURE receive (
      [FOREACH]col
            [colname]_out OUT [coldatatype],
      [ENDFOREACH]
            wait IN INTEGER := 0
            );
      END;
      /  

As you can see, it is the "same old thing": loop through each individual column in the table and construct the parameter lists to send and receive.

And here is the package body:

   CREATE OR REPLACE PACKAGE BODY [objname]_pipe
      IS
         PROCEDURE send (
      [FOREACH]col
            [colname]_in IN [coldatatype], 
      [ENDFOREACH]
            wait IN INTEGER := 0
            )
         IS
            stat INTEGER;
         BEGIN
            DBMS_PIPE.RESET_BUFFER;
      [FOREACH]col
            DBMS_PIPE.PACK_MESSAGE ([colname]_in);
      [ENDFOREACH]

            stat := DBMS_PIPE.SEND_MESSAGE (c_name, wait);
         END;

         PROCEDURE receive (
      [FOREACH]col
            [colname]_out OUT [coldatatype],
      [ENDFOREACH]
            wait IN INTEGER := 0
            )
         IS
            stat INTEGER;
         BEGIN
            --// Receive next message and unpack for each column. //--
            stat := DBMS_PIPE.RECEIVE_MESSAGE (c_name, wait);

            IF stat = 0
            THEN
      [FOREACH]col
               DBMS_PIPE.UNPACK_MESSAGE ([colname]_out);
      [ENDFOREACH]
            END IF;
         END;
      END;
      /   

CGML Highlights

• Nothing too fancy going on here; the main advantage is that you can bury lots of "smarts" about how to use DBMS_PIPE inside this one driver and then everyone can take advantage of it to get their jobs done right with a minimum of effort.

Sample of Generated Text

   CREATE OR REPLACE PACKAGE bonus_pipe
      --// Wrapper around pipe based on bonus
      --// Designed by Steven Feuerstein, Quest Software
      IS
         c_name CONSTANT VARCHAR2(200) := 'bonus_pipe';

         PROCEDURE send (
            ename_in IN varchar2,
            job_in IN varchar2,
            sal_in IN number,
            comm_in IN number,
            wait IN INTEGER := 0
            );

         PROCEDURE receive (
            ename_out OUT varchar2,
            job_out OUT varchar2,
            sal_out OUT number,
            comm_out OUT number,
            wait IN INTEGER := 0
            );
      END;
      /
      CREATE OR REPLACE PACKAGE BODY bonus_pipe
      IS
         PROCEDURE send (
            ename_in IN varchar2,
            job_in IN varchar2,
            sal_in IN number,
            comm_in IN number,
            wait IN INTEGER := 0
            )
         IS
            stat INTEGER;
         BEGIN
            DBMS_PIPE.RESET_BUFFER;
            DBMS_PIPE.PACK_MESSAGE (ename_in);
            DBMS_PIPE.PACK_MESSAGE (job_in);
            DBMS_PIPE.PACK_MESSAGE (sal_in);
            DBMS_PIPE.PACK_MESSAGE (comm_in);

            stat := DBMS_PIPE.SEND_MESSAGE (c_name, wait);
         END;

         PROCEDURE receive (
            ename_out OUT varchar2,
            job_out OUT varchar2,
            sal_out OUT number,
            comm_out OUT number,
            wait IN INTEGER := 0
            )
         IS
            stat INTEGER;
         BEGIN
            --// Receive next message and unpack for each column. //--
            stat := DBMS_PIPE.RECEIVE_MESSAGE (c_name, wait);

            IF stat = 0
            THEN
               DBMS_PIPE.UNPACK_MESSAGE (ename_out);
               DBMS_PIPE.UNPACK_MESSAGE (job_out);
               DBMS_PIPE.UNPACK_MESSAGE (sal_out);
               DBMS_PIPE.UNPACK_MESSAGE (comm_out);
            END IF;
         END;
      END;
      /

Of course, this is a very minimal implementation. You will want to add error handling, resetting of buffers, and so on. You can even get "fancy" and throw in specialized programs to analyze the received data.

---

Create DROP statements for all the tables and views in your schema.

Sure you can do this by constructing the drop statements in a SELECT statement. It gives you a hint, however, of the kind of DBA scripts and utilities you can build with CGML.

CGML Solution

You can accomplish this task with nothing more than the following CGML statements:

   #drop.gdr
      [STOREIN]CGML.tst
      [FOREACH]dbobject
      DROP [objecttype] [objectname];
      [ENDFOREACH]

and the following call to PLGCGML.genFile:

PLGCGML.genFile ('%', 'testCGML.gdr', show=> TRUE, single_pass=> TRUE);

In other words, you pass "%" for the name of the object. That will then fill up the PLGgen.dbobject array with the names of all the tables and views in the schema.

CGML Highlights

• This CGML takes advantage of another array, DBOBJECT, and two of its tags: objecttype and objectname.

The DBOBJECT array is a kind of "meta" array in CGML. It is used by PL/Generator to perform multiple generation sessions for a wild-carded object specification. I have adopted it for use in the above CGML script to generate text manipulating those objects, rather than constructing text based on "internal" characteristics of those objects.

---

Defining an Array of Files in a Directory

This example requires Oracle 8.1; it uses Java to get information about the files in a specified directory. For a more basic example of defining a new array, see the section "Defining New Arrays". This example also assumes that you know how to compile Java classes and load those classes into Oracle. In order for the example to work, you will need to:

1. Compile the JFile.java class and load it into Oracle.
2. Compile the XFile.pkg PL/SQL package.

Container for Array

Define a package to hold the file information.

   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;
      / 

Now make sure that the PL/Generator schema have access to the package:

GRANT EXECUTE ON dirinfo TO PUBLIC;

Populate the Array

I put all the steps into a stored procedure so that I can call it easily.

   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 => 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 => true, 
            delarray => FALSE
            );
      END;

/

CGML Solution

   #dirinfo.gdr
      [STOREIN]dirinfo.txt
      [FOREACH]file
         [dir][separator][name] contains [bytes] characters.
      [ENDFOREACH]

CGML Highlights

• 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 "the same old thing".
• 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

Sample of Generated Text

I issued this command in SQL*Plus:

SQL> exec listfiles ('c:temp');

and here is the generated text:

   c:tempte_employee.pks contains 15334 characters.
      c:tempte_loc.pks contains 7296 characters.
      c:tempte_job.pks contains 7528 characters.
      c:tempte_locemp.pks contains 8896 characters.
      c:tempte_department.pks contains 9109 characters.
      c:tempte_salhist.pks contains 9893 characters.
      c:tempte_charge_items.pks contains 35522 characters.

Pretty darn cool, if we at Quest Software do say so ourselves!

---

Create an audit database trigger for updates to tables in your schema.

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?

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 "pseudo-records". 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!

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 "=" and "!=" operators will not work as expected.

This scenario is addressed very cleanly by CGML. You just "write it once" and then deploy it to any table.

CGML Solution

You will find all of the text shown below in the audit.gdr file. First, I need to create my audit table:

   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)   
         );

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.

Next I construct the header for trigger:

   CREATE OR REPLACE TRIGGER [objname]_upd_audit
      AFTER UPDATE ON [objname]
      FOR EACH ROW
      DECLARE
         audit_rec [objname]_aud%ROWTYPE;
      BEGIN

and then I build my IF statement. Notice all the code to handle the various NULL-NOT NULL scenarios:

   [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]

Finally, it is time for the INSERT:

   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
         );

CGML Highlights

• 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.
• I perform multiple passes through the col array to construct my various sets of columns.

Sample of Generated Text

Here is the table creation statement for the department audit table:

   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)
         );

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:

   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;

/

---

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.

An insert procedure header surely needs to be implemented at some point. So let's do it now!

TO BE COMPLETED

CGML Highlights

#insproc.gdr

CGML Solution

• I use two different column arrays: pkycol and nonpkycol

Sample of Generated Text

---

Customize the PL/Generator Table Encapsulation driver to generate customized code specific to each table in your schema.

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.

TO BE COMPLETED

CGML Solution

CGML Highlights

Sample of Generated Text

---

Construct procedures to manage your packaged cursors more easily.

TO BE COMPLETED

CGML Solution

#pkgcur.gdr

CGML Highlights

Sample of Generated Text

---

Provide a general solution for mutating table problems with index-by tables of records.

TO BE COMPLETED

CGML Solution

CGML Highlights

Sample of Generated Text

---

Generate HTML documentation for a table

TO BE COMPLETED

CGML Solution

CGML Highlights

Sample of Generated Text

---

Generate a PL/SQL program that wraps a Java Stored Procedure and makes the Java technology available in PL/SQL.

TO BE COMPLETED

CGML Solution

CGML Highlights

Sample of Generated Text

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11118/viewspace-1022247/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11118/viewspace-1022247/