syntax.htm

Oracle PL/SQL procedure generator (second generator type)

		<P>Notice that countpkycols is treated as an object tag, while 10 is treated as a literal.</P>
		<P>3. If the current column name is &quot;last_name&quot;...</P>
		<PRE>   [if]colname[eq]last_name 
   [endif] </PRE>
		<P>Notice that colname is treated as an object tag, while last_name is treated as a literal.</P>
		<P>4. If table data is loaded into a cache and there is only one primary key column...</P>
		<PRE>   [if]loadany[and]onepkycol 
   [endif]</PRE>
		<P>
<HR ALIGN="CENTER">
</P>
		<P><A NAME="ItLogic"></A><FONT SIZE="5" FACE="Arial"><I><B> Iterative Logic</B></I></FONT></P>
		<P>CGML supports iterative processing against elements in an array with the FOREACH statement.</P>
		<PRE>   [FOREACH]array
      body of loop
   [ENDFOREACH]label
</PRE>
		<P>where array is the name of the array and the label is an optional &quot;comment&quot; you can add after the
		[ENDFOREACH] command to document the iterative clause that is terminating.</P>
		<P>The array must be defined in the driver language. An array in PL/Generator is an index-by table (a.k.a., PL/SQL
		table), in which each row contains either a record of multiple pieces of data (named fields) or points back to
		another table containing records. The contents of each field in a row of these arrays is accessible as an object
		tag. You will, of course, need to know the names of these fields in order to include this data in your generated
		text. You can also add a [BETWEEN] clause to the FOREACH statement as show below:</P>
		<PRE>   [FOREACH]array[BETWEEN]between_text    
   [ENDFOREACH]label  </PRE>
		<P>The between_text will then be appended to each line of text except the last in the loop execution. This is useful,
		for example, when you are building a parameter list and need a comma between arguments, but not after the last
		one. This is shown below:</P>
		<PRE>     PROCEDURE insert_row (
     [FOREACH]col[BETWEEN],
       [colname]_in IN [coldattype]
     [ENDFOREACH]
     );</PRE>
		<P>Note: the [BETWEEN] clause will not work properly when you have loops and IF statements nested inside the [FOREACH].
		Here is an example of CGML that will <I>not </I>propagate the BETWEEN text properly:</P>
		<PRE>    [FOREACH]col[BETWEEN],
      [IF]coldatatype[EQ]VARCHAR2
      [colname]
      [ELSE]
      TO_CHAR ([colname])
      [ENDIF]
    [ENDFOREACH]
</PRE>
		<P>Within an array, you can reference object tags that identify information from the current row in the array.
		In the above example, I reference [colname] to obtain the column name and [coldatatype] to include the datatype
		of the column.</P>
		<P>See the section titled &quot;Pre-Defined Arrays&quot; to find out how to see which arrays are defined, and the
		object tags available within each array. You can also define your own arrays; this advanced topic is explored in
		the section &quot; Defining or Extending Drivers&quot;.</P>
		<P>Note: CGML does not curently support WHILE, REPEAT UNTIL or simple loops, only the FOR loop.</P>
		<P>
<HR ALIGN="CENTER">
</P>
		<P><A NAME="arrayops"></A><FONT SIZE="4" FACE="Arial"><B> Standard Array Operators</B></FONT></P>
		<P><FONT FACE="Arial">Every CGML array comes with a standard set of operators you can reference in your CGML statements
		to get information about the contents of that array. The names of the operators are shown in the table below:</FONT>
		
		<TABLE BORDER="0">
			<TR>
				<TD WIDTH="135"><U>Tag/Operator Name</U></TD>
				<TD><U>Description</U></TD>
			</TR>
			<TR>
				<TD WIDTH="135">
					<PRE>MULTI&lt;array&gt;S</PRE>
				</TD>
				<TD>Returns TRUE if array has more than one row in it.</TD>
			</TR>
			<TR>
				<TD WIDTH="135">
					<PRE>ONE&lt;array&gt;</PRE>
				</TD>
				<TD>Returns TRUE if array has just one row in it.</TD>
			</TR>
			<TR>
				<TD WIDTH="135">
					<PRE>HAVE&lt;array&gt;</PRE>
				</TD>
				<TD>Returns TRUE if the array has at least one row in it.</TD>
			</TR>
			<TR>
				<TD WIDTH="135">
					<PRE>NUM&lt;array&gt;S</PRE>
				</TD>
				<TD>Returns the number of rows defined in the array.</TD>
			</TR>
		</TABLE>
To see how you would use these operators, suppose you want to display the number of columns in a table and if there
		are any, generate a header and then each column name. Here is the CGML script to do that:</P>
		<PRE>Number of columns = [NUMCOLS]
[IF]havecol
Name of column
[FOREACH]col
   [colname]
[ENDFOREACH]
[ENDIF]		</PRE>
		<P>
<HR ALIGN="CENTER">
</P>
		<P><A NAME="arrays"></A><FONT SIZE="4" FACE="Arial"><B> Pre-Defined Arrays</B></FONT></P>
		<P>Unless you are building an entirely new driver from the ground up, you will be working within the context of
		or based upon the existing Table Encapsulation driver (this is the case, for example, when you run the PLGCGML.genFile
		procedure). This driver has a set of pre-defined arrays for your use.</P>
		<P>You can view the current set of arrays at any time by executing the query found in the showarrays.sql script
		(output is written to objtags.txt). Here is the output at the time this document was written (but it is subject
		to change!):</P>
		<P>
		<TABLE BORDER="0">
			<TR>
				<TD><U> ARRAY</U></TD>
				<TD><U>DESCRIPTION</U></TD>
			</TR>
			<TR>
				<TD></TD>
				<TD></TD>
			</TR>
			<TR>
				<TD>COL</TD>
				<TD>Array of all columns, data directly from the data dictionary</TD>
			</TR>
			<TR>
				<TD>CONS</TD>
				<TD>Array of check constraints</TD>
			</TR>
			<TR>
				<TD>CONSCOL</TD>
				<TD>Array of check constraint columns</TD>
			</TR>
			<TR>
				<TD>CURS</TD>
				<TD>Array of cursors</TD>
			</TR>
			<TR>
				<TD>DBOBJECT</TD>
				<TD>Array of database objects</TD>
			</TR>
			<TR>
				<TD>DESCCOL</TD>
				<TD>Array of descriptor columns</TD>
			</TR>
			<TR>
				<TD>FKY</TD>
				<TD>Array of foreign keys</TD>
			</TR>
			<TR>
				<TD>FKYCOL</TD>
				<TD>Array of foreign key columns</TD>
			</TR>
			<TR>
				<TD>FKYDESC</TD>
				<TD>Array of foreign key descriptor indexes</TD>
			</TR>
			<TR>
				<TD>FKYDESCCOL</TD>
				<TD>Array of foreign key descriptor index columns</TD>
			</TR>
			<TR>
				<TD>GENCOL</TD>
				<TD>Array of all columns, data post-processed for PL/Generator</TD>
			</TR>
			<TR>
				<TD>IND</TD>
				<TD>Array of unique indexes</TD>
			</TR>
			<TR>
				<TD>INDCOL</TD>
				<TD>Array of unique index columns</TD>
			</TR>
			<TR>
				<TD>INVALID_IDENTS</TD>
				<TD>Array of invalid identifiers</TD>
			</TR>
			<TR>
				<TD>NONPKYCOL</TD>
				<TD>Array of non-primary key columns</TD>
			</TR>
			<TR>
				<TD>PKYCOL</TD>
				<TD>Array of primary key columns</TD>
			</TR>
			<TR>
				<TD>UCONSCOL</TD>
				<TD>Array of distinct check constraint columns</TD>
			</TR>
			<TR>
				<TD>UPDCOL</TD>
				<TD>Array of columns with own update procedure</TD>
			</TR>
		</TABLE>
</P>
		<P>
<HR ALIGN="CENTER">
</P>
		<P><A NAME="ArrObjTag"></A><B> </B><FONT SIZE="4" FACE="Arial"><B>Referencing Array Object Tags</B></FONT></P>
		<P>You can include qualified and unqualified object tags in your CGML text. The usual format is unqualified; here
		is an example:</P>
		<PRE>   PROCEDURE insert_row (
   [FOREACH]col[BETWEEN],
      [colname]_in 
   [ENDFOREACH]
      );</PRE>
		<P>Since colname is a element in the col array and col is the &quot;current&quot; array scope, the [colname] object
		tag will result in the names of each column to be generated.</P>
		<P>When you have nested array loops, however, you can sometimes have conflicts between element names. In this case,
		you will use &quot;dot notation&quot; to qualify the object tag for the outer loop to ensure that the correct value
		is generated.</P>
		<P>In the following example, I nest the Primary Key Columns array within the Update Columns array. Each has a element
		named &quot;colname&quot; associated with it. So I must use &quot;updcol.colname&quot; if I want to reference that
		column inside the pkycol loop.</P>
		<PRE>   [FOREACH]updcol
      FUNCTION [colname]$val (
      [FOREACH]pkycol[between],
         [colname]_in IN [objname].[updcol.colname]%TYPE
      [ENDFOREACH]
   [ENDFOREACH]</PRE>
		<P>
<HR ALIGN="CENTER">
<A NAME="control"></A><FONT SIZE="5" FACE="Arial"><I><B> Control Commands</B></I></FONT></P>
		
		<P><FONT FACE="Arial">CGML offers a wide array of commands that control the output and behavior of your CGML scripts.
		These commands include:</FONT></P>
	
		<P>
		<TABLE BORDER="0">
			<TR>
				<TD WIDTH="108"><U>Command</U></TD>
				<TD><U>Description</U></TD>
			</TR>
			<TR>
				<TD WIDTH="108"><A HREF="#output">STOREIN</A></TD>
				<TD>Defines the target that will receive the generated text</TD>
			</TR>
			<TR>
				<TD WIDTH="108"><A HREF="#output">CLOSE</A></TD>
				<TD>Closes the target</TD>
			</TR>
			<TR>
				<TD WIDTH="108"><A HREF="#set">SET</A></TD>
				<TD>Sets aliases and other elements in the CGML session</TD>
			</TR>
			<TR>
				<TD WIDTH="108"><A HREF="#rto">RTO</A></TD>
				<TD>Creates reusable text objects</TD>
			</TR>
			<TR>
				<TD WIDTH="108"><A HREF="#include">INCLUDE</A></TD>
				<TD>Includes reusable text objects</TD>
			</TR>
			<TR>
				<TD WIDTH="108"><A HREF="#include">SPECIAL</A></TD>
				<TD>Includes reusable text objects as customization areas</TD>
			</TR>
			<TR>
				<TD WIDTH="108"><A HREF="#stop">STOP</A></TD>
				<TD>Stops the CGML processing</TD>
			</TR>
		</TABLE>
</P>
		<P><A NAME="output">
<HR ALIGN="CENTER">
</A><FONT SIZE="5" FACE="Arial"><I><B> Specifying Output</B></I></FONT></P>
		<P>Use the following command in your driver file tells PL/Generator where to place the generated text. Here is
		the format of this statement:</P>
		<PRE>   [STOREIN]name </PRE>
		<P>where name is the name of the operating system file, if you are directing output to a file using UTL_FILE (the
		default). It can also be the name of a &quot;reusable text object&quot;, a.k.a., list, as well as the &quot;object
		name&quot; when storing the text in the PL/Generator source table.</P>

		<P>To close the file or terminate writing of the text to that &quot;target&quot;, issue the close command:</P>
		<PRE>   [CLOSE]</PRE>
		<P>You can at any point issue a new [STOREIN] command to direct the generated text to a new target. If you have
		already opened a target with a previous [STOREIN] command, PL/Generator will close that target and open the new
		one. <A NAME="SetVandA">
<HR ALIGN="CENTER">
</P>
		<P></A><FONT SIZE="5" FACE="Arial"><I><B> Set Variables and Aliases</B></I></FONT></P>
		<P><A NAME="set">CGML offers two SET commands, which allow you to modify the values of variables (a subset of which are object
		tags) and aliases.</P>
		<P><FONT SIZE="4" FACE="Arial">The Set Command</FONT></P>
		<P>Use the SET command to change the value of global PL/SQL variable. Here is the syntax of the SET command:</P>
		<PRE>[SET]variable[TO]value</PRE>
		<P>The following rules apply to this command:</P>
		<UL>
			<LI>The variable must be either the name of a variable defined in a package specification (ie, a globally-accessible
			data structure) or it must be one of the following object tags:
		
		<P>
		<TABLE BORDER="0" WIDTH="648">
			<TR>
				<TD WIDTH="20%" VALIGN="TOP" BGCOLOR="#000000"><FONT COLOR="#FFFFFF"><B> Object Tag</B></FONT></TD>
				<TD WIDTH="80%" VALIGN="TOP" BGCOLOR="#000000"><FONT COLOR="#FFFFFF"><B> Description</B></FONT></TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> target</B></TD>