examples.htm

Oracle PL/SQL procedure generator (second generator type)

            )
         IS
            SELECT
               employee_id,
               last_name,
               ...
               department_id
              FROM employee
             WHERE
                department_id = department_id_in
            ;
      &nbsp;</FONT></PRE>

<P>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.</P>
<P>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.</P>
<HR>
<P><A NAME="5"><B><I><FONT FACE="Arial" SIZE="5">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. </FONT></I></B></A></P>
<P>A handy utility, but another tedious task. Just imagine a table with 100
columns! You also need to know about the UTL_FILE package. </P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>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:</P>
<P>SQL&gt; exec loc2file ('c:\temp');</FONT> 
</P>
<P>Whenever you need a &quot;table to file&quot; procedure for another table,
just generate it from: </P>
<P></P>
<PRE>   #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;
      /</FONT></PRE>

<P><B><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></B></P>
<UL>
<LI>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.</LI>
<LI>Notice that I couldn't put the string &quot;|| delim ||&quot; 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.</LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B><FONT
FACE="Arial" SIZE="4"></FONT></P>
<P></P>
<PRE>   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;
      /</FONT></PRE>

<P>And just think how much harder it would be to build (but relatively easy to
<I>generate</I>) 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.</P>
<HR>
<P><A NAME="6"><B><I><FONT FACE="Arial" SIZE="5">Build a package that transfers
the contents of a particular table-based record through database pipes
(procedure to pack/send, procedure to receive/unpack).</FONT></I></B></A></P>
<P>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...</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>Let's start with the package specification:</P>
<P></P>
<PRE>   #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;
      /  </FONT></PRE>

<P>As you can see, it is the &quot;same old thing&quot;: loop through each
individual column in the table and construct the parameter lists to send and
receive. </P>
<P>And here is the package body: </P>
<P></P>
<PRE>   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;
      /   </FONT></PRE>

<P><B><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></B></P>
<UL>
<LI>Nothing too fancy going on here; the main advantage is that you can bury
lots of &quot;smarts&quot; 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.</LI>
</UL>
<P><B><FONT FACE="Arial" SIZE="4">Sample of Generated Text </FONT></B></P>
<P></P>
<PRE>   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;
      /</FONT></PRE>

<P>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 &quot;fancy&quot;
and throw in specialized programs to analyze the received data.</P>
<HR>
<P><A NAME="7"><B><I><FONT FACE="Arial" SIZE="5">Create DROP statements for all
the tables and views in your schema.</FONT></I></B></A></P>
<P>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.</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Solution </FONT></B></P>
<P>You can accomplish this task with nothing more than the following CGML
statements: </P>
<P></P>
<PRE>   #drop.gdr
      [STOREIN]CGML.tst
      [FOREACH]dbobject
      DROP [objecttype] [objectname];
      [ENDFOREACH]</FONT></PRE>

<P>and the following call to PLGCGML.genFile: <FONT FACE="Courier New"
SIZE="2"></FONT></P>
<P>PLGCGML.genFile ('%', 'testCGML.gdr', show=&gt; TRUE, single_pass=&gt;
TRUE);</P>
<P>In other words, you pass &quot;%&quot; 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.</P>
<P><B><FONT FACE="Arial" SIZE="4">CGML Highlights </FONT></B></P>
<UL>
<LI>This CGML takes advantage of another array, DBOBJECT, and two of its tags:
objecttype and objectname.</LI>
</UL>
<P>The DBOBJECT array is a kind of &quot;meta&quot; 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 &quot;internal&quot; characteristics of those objects. </P>
<HR>
<P><A NAME="8"><B><I><FONT FACE="Arial" SIZE="5">Defining an Array of Files in
a Directory</FONT></I></B></A></P>
<P>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 &quot;Defining New Arrays&quot;. 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:</P>