syntax.htm

Oracle PL/SQL procedure generator (second generator type)

				<TD WIDTH="80%" VALIGN="TOP">The name of the file to which text is being written</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> create_at</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Date/time stamp at which generation occurred</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> objname</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Name of the object from which generation is occurring</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> schema</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Schema owning the object from which generation is occurring</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> dbobjname</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Underlying database object name, different from objname if objname is a synonym.</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> dbschema</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Underlying schema name, different from schema if the object is a synonym.</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> tabforview</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">The table on which a view is based</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> arrayname</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Name of current array in loop processing</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> target_schema</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Schema in which generated object will be created.</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> pkgname</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Name of default package for dynamic substitution. Value is &quot;PLGGEN&quot;.</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> pkyname</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Name of primary key for table</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> pkycolname</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Name of column in primary key if these is only one</TD>
			</TR>
			<TR>
				<TD WIDTH="20%" VALIGN="TOP"><B> progname</B></TD>
				<TD WIDTH="80%" VALIGN="TOP">Name of program currently being generated</TD>
			</TR>
		</TABLE>

	</UL><P>	<UL>
			<LI>If you do pass an object tag, do not include the object tag characters. In other words, this format is invalid:
		
		<PRE>
   [SET][colname][TO]abc  </PRE>
		<P>Instead, simply supply the object tag as shown below:</P>
		<PRE>[SET]colname[TO]abc</PRE>
		</UL><P><UL>
			<LI>You can also pass the name of a global PL/SQL variable, as shown below. First I create a package with global
			data:
		
		<PRE>   CREATE OR REPLACE PACKAGE mystuff
   IS
      myname VARCHAR2(100;
   END;
   /
</PRE>
		<P>And then I can reference those data structures in my CGML text as follows:</P>
		<PRE> [SET]mystuff.myname[TO]Steven </PRE>
		<P>Why would you want to use the SET command? Many of the aliases utilized in the Table Encapsulation driver are
		actually composed of a sequence of other aliases. The package name, for example, is represented by the alias:</P>
		<PRE> {package} </PRE>
		<P>but the package alias is constructed out of the following elements (this is the actual definition of that alias):</P>
		<PRE> {PLG&gt;apppre}{PLG&gt;pkgpre}{[objname]}{PLG&gt;pkgsuf} </PRE>
		<P>Suppose, then, that I want to change the object name that will appear in the {package} alias, without reconstructing
		the entire alias. I need to &quot;sneak inside&quot; the existing alias and change the outcome. I can do that by
		&quot;overriding&quot; the object name with a SET command as shown below:</P>
		<PRE>   [FOREACH]dbobject
   [SET]objname[EQ][objectname]
   @[gendir][gendelim]{PLG&gt;package}
   [ENDFOREACH]</PRE></UL>
		<P><FONT SIZE="4" FACE="Arial">Special Set Object Command</FONT></P>
		<P>If you execute a SET command specifying &quot;object&quot; as the variable, PL/Generator will load the information
		about that object (this means, primarily, the arrays of data associated with that object). The syntax for this
		command is:</P>
		<PRE>[SET]object[TO]&lt;object name&gt;</PRE>
		<P>Why would you want to set or load the object once you have already started parsing your CGML script? This will
		come in handy primarily when you have used the <A HREF="#execute">EXEC command</A> to <I>create</I> a new object.</P>
		<P>In the following script, for example, I create a table that is defined in a <A HREF="#create">reusable text
		object</A> and then load that object for use in subsequent CGML calls:</P>
		<PRE>[RTO]maketable
create table {rectab} (
    driver VARCHAR2(100),
    srcstring VARCHAR2(200),
    srctype VARCHAR2(100),
    plsql_command VARCHAR2(1000),
    returntype VARCHAR2(100),
    objid INTEGER,
    context VARCHAR2(200)
   )
# Must put your own stuff above
[ENDRTO]

# Create the table
[EXECRTO]maketable

# Load the object information
[SET]object[TO]src_rectype
</PRE>
		<P><FONT SIZE="4" FACE="Arial">Special Set Driver Command</FONT></P>
		<P>If you execute a SET command specifying &quot;driver&quot; as the variable, PL/Generator will load the information
		about that driver. The syntax for this command is:</P>
		<PRE>[SET]driver[TO]&lt;driver name&gt;</PRE>
		<P>The following command, for example, loads the Table Encapsulation driver:</P>
		<PRE>[SET]driver[TO]te</PRE>
		<P><FONT SIZE="4" FACE="Arial">The Set Alias Command</FONT></P>
		<P>You can create or modify the values of aliases with the SETALIAS command. It has this syntax:</P>
		<PRE>[SETALIAS]aliasname[TO]value </PRE>
		<P>Here are some rules governing the SETALIAS statement:</P>
		<UL>
			<LI>The aliasname string is the name of the alias whose value will be changed. If the alias does not already exist,
			it will be created and assigned the value. This new value is the string to the right of the [TO] clause.
			<LI>The text to the right of the [TO] clause can either be &quot;straight text&quot;, an alias, a object tag, or
			a combination of these three. By default, the string after the [TO] will be evaluated, that is, searched for aliases
			and object tags for dynamic substitution. If you want the text to be passed without evaluation, preface it with
			the escape character, \, as shown below:
		
		<PRE>      [SETALIAS]PLG&gt;tabcolref[TO]\[colname]
</PRE>
		<P>Use the \ character when you want to <I>defer </I>the dynamic substitution until the alias itself is evaluated.</P>
		<P>Note: you can always check for the current value of the escape or &quot;as is&quot; character by displaying
		the value of PLGgen.c_as_is.</P></UL>
		<P>Here is an example of the [SETALIAS] command. If the table from which I am generating has at lesat one hidden
		field, I want to use a programmer-defined record type, as opposed to a %ROWTYPE table declaration for my records.</P>
		<PRE>    [IF]havehidden
      [SETALIAS]PLG&gt;tableRT[TO]allcolsRT
   [ELSE]
      [SETALIAS]PLG&gt;tableRT[TO][objname]%ROWTYPE
   [ENDIF]</PRE>
		<P><A NAME="execute">
<HR ALIGN="CENTER">
</P>
		<H2></A><FONT SIZE="5" FACE="Arial"><I> Execute Statements</I></FONT></H2>
		<P>You can &quot;escape&quot; from CGML to the PL/SQL dynamic SQL engine to execute any line of code or SQL that
		you desire by using the EXEC statement. There are two different forms of EXEC:</P>
		<PRE>   [EXEC]statement    
   [EXECRTO]rto     </PRE>
		
		<H3>The [EXEC] Command</H3>
		<P>The [EXEC] command executes the text that follows that tag on the same physical line. The syntax is:</P>
		<PRE>[EXEC]statement</PRE>
		<P>Here are some rules governing the EXEC statement:</P>
		<UL>
			<LI>If the text supplied on the EXEC line does not end in a semi-colon, PL/Generator will assume that the text
			is an SQL statement (DML or DDL are both supported). If the text ends with a semi-colon, then it will be treated
			as a PL/SQL block.
			<LI>Your execution statement must fit on one physical line; no continuations are allowed at this time (see EXECRTO
			for a workaround to this restriction).
		</UL>
		<P>I can, for example, request that PL/Generator display the driver sources associated with the Table Encapsulation
		driver with this CGML text:</P>
		<PRE>   [exec]plgdoir.showdrvsrc('te')</PRE>
		<P>You can even use aliases in the EXEC statement; I can rewrite the previous text as follows:</P>
		<PRE>   [SETALIAS]newone[TO]plgdoir.showdrvsrc('te')    
   [exec]{newone}  </PRE>
		<P>Wow! This means that you can dynamically construct SQL statements or PL/SQL blocks in your CGML driver files
		and then execute the resulting commands.</P>
		<H3>The [EXECRTO] Statement</H3>
		<P>Using this form of the EXEC command, you can also construct complex, multi-line blocks of text, save them to
		a <A HREF="#create">reusable text object</A> and then execute that text as shown below:</P>
		<PRE>[RTO]dispinfo 
DBMS_OUTPUT.PUT_LINE ('So many possibilities...'); 
DBMS_OUTPUT.PUT_LINE ('Most Excellent!'); 
[ENDRTO]
[EXECRTO]dispinfo 
</PRE>
		<P>If the last line of your statements ends with a semi-colon, PL/Generator will automatically wrap your code inside
		a BEGIN-END block.</P>

		<P>This technique can come in hand when you want to use CGML to dynamically generate code, which is then executed
		dynamically to create objects needed in some later portion of your CGML. Mind twisting!</P>

		<P><A NAME="rto"><A NAME="create">
<HR ALIGN="CENTER">
</P>

		<P><FONT SIZE="5" FACE="Arial"><I><B> Create Reusable Text Objects</B></I></FONT></P>

		<P>It would be nice if everything could be known in advance of needing it. That is not the case, however, both
		in the real world and the virtual one. When it comes to code generation, </P>

		<P>To start creating a reusable text object (RTO), issue the RTO command in either of these formats:</P>
		<PRE>[RTO]&lt;rto_name&gt;[REPLACE]</PRE>

		<PRE>[RTO]&lt;rto_name&gt;[APPEND]</PRE>
		<P>where &lt;rto_name&gt; is the name of the reusable text object. </P>

		<P>To close the RTO, issue the following command:</P>
		<PRE>[ENDRTO]</PRE>
		<P>Notice that you do not specify the name of the RTO when you end it. You can only be creating one RTO at any
		given point; if you try to nest RTO creations, you will get a CGML processing error like this:</P>
		<PRE>Unable to translate [ENDRTO] </PRE>
		<P>To insert this RTO into your generated text, you can use the any of the <A HREF="#include">Include Statements</A>.</P>

		<P><A NAME="include">
<HR ALIGN="CENTER">
</P>

		<P><FONT SIZE="5" FACE="Arial"><I><B> Include Statements</B></I></FONT></P>

		<P>If you have sections of text (CGML commands, hard-coded text, etc.) that need to be repeated in multiple locations
		in your script, you would surely rather not type or cut-and-paste that text repeatedly. Then you have to maintain
		it in multiple places as well. To avoid this problem, CGML offers the ability to &quot;include&quot; text from
		either a file or a reusable text object.</P>

		<P>There are two include commands:</P>
		<PRE>[INCLUDE]&lt;name&gt;</PRE>

		<PRE>[SPECIAL]&lt;name&gt;</PRE>
		<P>where &lt;name&gt; is the name of a file or RTO. Use the SPECIAL command when you are including a customization
		file and wish to have PL/Generator add lines before and after the included text to indicate a customization section.</P>

		<P><A NAME="stop">
<HR ALIGN="CENTER">
</P>

		<P><FONT SIZE="5" FACE="Arial"><I><B> The STOP Statement</B></I></FONT></P>

		<P>You can use the [STOP] tag to stop the CGML processing if you encounter some sort of error that should halt
		generation. The syntax of the command is:</P>
		<PRE>[STOP]&lt;message&gt;</PRE>
		<P>where &lt;message&gt; is an optional message. It can be a literal string as in:</P>
		<PRE>[STOP]Reached max for report</PRE>
		<P>Or you can provide an alias so that the message does not need to be hard-coded, as in:</P>
		<PRE>[STOP]{problem_description}</PRE>
		<P>You can find out the status of your generation by examining the values returned by these two functions:</P>
		<PRE>PLGerr.errtext</PRE>
		<PRE>PLGerr.errcode</PRE>
		<P>The PLGerr.errtext will return the string you provided (either directly or through an alias) or, if no message
		was provided, then the string &quot;Generation Cancelled!&quot;. The PLGerr.errcode will return the number 200017.
		See the section on <A HREF="cgml.htm"><B>Troubleshooting</B></A> for more information.</P>

	
	</UL>
</UL>
<UL>
	<UL><A NAME="array_object">
		<IMG SRC="Array.GIF" WIDTH="431" HEIGHT="92" ALIGN="BOTTOM" BORDER="0"> 
<HR ALIGN="CENTER">

		<P><FONT SIZE="5">Table of Contents</FONT></P>
		<P>
		<UL>
			<LI><A HREF="#col"><B>COL</B></A>
			<LI><A HREF="#cons"><B>CONS</B></A>
			<LI><A HREF="#conscol"><B>CONSCOL</B></A>
			<LI><A HREF="#curs"><B>CURS</B></A>
			<LI><A HREF="#dbobjects"><B>DBOBJECTS</B></A>
			<LI><A HREF="#desccol"><B>DESCCOL</B></A>
			<LI><A HREF="#fky"><B>FKY</B></A>
			<LI><A HREF="#fkycol"><B>FKYCOL</B></A>
			<LI><A HREF="#fkydesc"><B>FKYDESC</B></A>
			<LI><A HREF="#fkydesccol"><B>FKYDESCCOL</B></A>
			<LI><A HREF="#gencol"><B>GENCOL</B></A>
			<LI><A HREF="#ind"><B>IND</B></A>
			<LI><A HREF="#indcol"><B>INDCOL</B></A>
			<LI><A HREF="#invalid"><B>INVALID_IDENTS</B></A>
			<LI><A HREF="#nonpkycol"><B>NONPKYCOL</B></A>
			<LI><A HREF="#pkycol"><B>PKYCOL</B></A>
			<LI><A HREF="#uconscol"><B>UCONSCOL</B></A>
			<LI><A HREF="#uind"><B>UIND</B></A>
			<LI><A HREF="#uindcol"><B>UINDCOL</B></A>
			<LI><A HREF="#updcol"><B>UPDCOL</B></A>
		</UL>
		
<HR ALIGN="CENTER">

		<P><FONT SIZE="4" FACE="Arial">Available Array Object Tags</FONT></P>
		
<HR ALIGN="CENTER">

		<P>The object tags available within each array for the Table Encapsulation driver (used as the source for the default
		TEST driver you will be creating) are shown in the following table:</P>
		
		<TABLE BORDER="1"  CELLSPACING="5"   BORDERCOLOR="#000000">
			<TR>
				<TD     VALIGN="TOP" BGCOLOR="#000000"><B>
									</B><FONT COLOR="#FFFFFF"><B>Array</B></FONT></TD>
				<TD      VALIGN="TOP" BGCOLOR="#000000"><B>
				
					</B><FONT COLOR="#FFFFFF"><B>Object Tag</B></FONT></TD>
				<TD     WIDTH="15%" VALIGN="TOP" BGCOLOR="#000000"><B>