release notes.htm

Oracle PL/SQL procedure generator (second generator type)

<LI>After generating through the screen interface, save the generation by
clicking on the Commit icon on the toolbar.</LI>
<LI>Connect to SQL*Plus or some other SQL execution environment. Retrieve the
code and place it in a file (you can do this, for example, with the SPOOL
command in SQL*Plus). The dumpcode.sql script that ships with the PL/Generator
source code offers an example of the kind of script you can write/use from
within SQL*Plus. An example session with this program is shown below.</LI>
<LI>Compile the generated code from the files you just created. It is not
compiled for you; it is only placed in a PL/Generator database table
(plg_source).</LI>
</OL>
<DIR>
</DIR>
<DIR>
</DIR>
<P>The dumpcode.sql SQL*Plus script takes a single parameters: the name of the
table (or some string that will identify the code generated for that table.
Suppose, for example, that I have generated code for the DEPARTMENT table. I
can then issue this command:</P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">SQL&gt; @dumpcode department </FONT></P>
<P><FONT SIZE="2">This script then creates the following file (named
crefiles.cmd):</FONT></P>
<PRE>
 REM SPOOLED TO crefiles.cmd      
 SET TERMOUT OFF                                                                                      
 SET FEEDBACK OFF                                                                                     
 SET LINESIZE 120                                                                                     
 SET PAGESIZE 0                                                                                       
 SPOOL te_department.html                                                                             
 SELECT text FROM plg_source                                                                          
  WHERE driver = 'TE'   AND name = 'te_department.html'                                               
  ORDER BY line;                                                                                      
 SPOOL OFF                                                                                            
 SPOOL te_department.pkb                                                                              
 SELECT text FROM plg_source                                                                          
  WHERE driver = 'TE'   AND name = 'te_department.pkb'                                                
  ORDER BY line;                                                                                      
 SPOOL OFF                                                                                            
 SPOOL te_department.pks                                                                              
 SELECT text FROM plg_source                                                                          
  WHERE driver = 'TE'   AND name = 'te_department.pks'                                                
  ORDER BY line;                                                                                      
 SPOOL OFF                                                                                            
 SPOOL te_department.tpb                                                                              
 SELECT text FROM plg_source                                                                          
  WHERE driver = 'TE'   AND name = 'te_department.tpb'                                                
  ORDER BY line;                                                                                      
 SPOOL OFF                                                                                            
 SPOOL te_department.tps                                                                              
 SELECT text FROM plg_source                                                                          
  WHERE driver = 'TE'   AND name = 'te_department.tps'                                                
  ORDER BY line;                                                                                      
 SPOOL OFF                                                                                            
 SET TERMOUT ON                                                                                       
 SET FEEDBACK ON                                                                                      
 SET LINESIZE 120                                                                                     
 SET PAGESIZE 60                                                                                      </PRE>

<P><FONT SIZE="2">You can then execute the crefiles.cmd command file in
SQL*Plus to create the five files:</FONT></P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">SQL&gt; @crefiles.cmd </FONT></P>
<P><FONT SIZE="2">Notice that it will create the files in the current working
directory for SQL*Plus. You can then move those files to their final
destination.</FONT></P>
<P><B><I><FONT FACE="Arial" SIZE="2"><A NAME="Avoidance">Complete Avoidance of
UTL_FILE</A></FONT></I></B></P>
<P><FONT SIZE="2">In a future release, PL/Generator will not use UTL_FILE for
any of its I/O operations. In release 1.1, UTL_FILE is still needed if you have
any customization files or if you have modified the standard header file. The
standard header looks like this:</FONT></P>
<PRE>
 --//----------------------------------------------------------------------- 
 --//  PL/Generator Table Encapsulator for &quot;salhist&quot; 
 --//----------------------------------------------------------------------- 
 --//  (c) COPYRIGHT Personnel Policies, Inc. 1998. 
 --//               All rights reserved. 
 --// 
 --//  No part of this copyrighted work may be reproduced, modified, 
 --//  or distributed in any form or by any means without the prior 
 --//  written permission of Personnel Policies, Inc.. 
 --//----------------------------------------------------------------------- 
 --//  This software was generated by Quest Software's PL/Generator (TM). 
 --// 
 --//  For more information, visit www.Quest Software.com or call 1.800.REVEAL4 
 --//----------------------------------------------------------------------- 
 --//  Stored In:  te_SALHIST.pks 
 --//  Created On: November  28, 1998 20:29:31 
 --//  Created By: PLG2 
 --//  PL/Generator Version: 1.1 
 --//----------------------------------------------------------------------- 
 &nbsp; </PRE>

<P><FONT SIZE="2">If neither of the above conditions apply and you do
<I>not</I> want to use UTL_FILE at all (this way you do not have to ask the DBA
to modify the initialization parameters file nor do you have to move the driver
files to a directory on the server), then take the following steps:</FONT></P>
<OL>
</OL>
<OL>
<LI>Generate your code to the database (see <A HREF="#GenDB"><FONT
SIZE="2">&quot;Generating to the Database&quot;</FONT></A><FONT
SIZE="2">).</FONT></LI>
<LI>Call the PLGdoir.usedefhdr procedure (see below) to instruct PL/Generator
to use the standard header already stored in the database. </LI>
</OL>
<DIR>
</DIR>
<DIR>
</DIR>
<P>To request that the header always be retrieved from the database (which
contains the standard header shipped with the product), call this procedure: 
</P>
<PRE>
 PROCEDURE PLGdoir.usestdhdr 
    (drv IN VARCHAR2, 
     tab IN VARCHAR2, 
     sch IN VARCHAR2 := NULL); </PRE>
<DIR>
</DIR>

<P><FONT SIZE="2">where drv is the driver (PLGte.driver if you are performing
table encapsulation), tab is the name of the table or database object to which
the action is applies, sch is the schema to which the action applies. </FONT> 
</P>
<P>If you want to set the use of the standard header as a standard for all
tables in the SCOTT schema, you would execute this command:</P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">SQL&gt; exec plgdoir.usestdhdr
(plgte.driver, plgdoir.c_global) </FONT></P>
<P><FONT SIZE="2">Once this command is COMMITTed to the database, you can
generate code from the screen interface and this new setting would be
applied.</FONT></P>
<P>Note: the default setting of PL/Generator is to read the header information
from the driver file (in other words, it assumes that you do <I>not</I> want to
use the standard header) that you may have modified. This is necessary to
support backward compatibility with PL/Generator 1.0.</P>
<P>Again, in a future release of PL/Generator, such dependencies will become
transparent to you.</P>
<P><B><I><FONT FACE="Arial" SIZE="2"><A NAME="Primary">The Update Primary Key
Procedure</A></FONT></I></B></P>
<P><FONT SIZE="2">In most schemas, you will not want to let developers update
primary key values. If, on the other hand, you do require this capability, you
can ask PL/Generator to created a procedure specifically for this purpose by
calling the following program:</FONT></P>
<PRE>
 PROCEDURE PLGdoir.setupdpky 
    (drv IN VARCHAR2, 
     tab IN VARCHAR2, 
     sch IN VARCHAR2 := NULL); </PRE>
<DIR>
</DIR>

<P><FONT SIZE="2">where drv is the driver (PLGte.driver if you are performing
table encapsulation), tab is the name of the table or database object to which
the action is applies, sch is the schema to which the action applies. </FONT> 
</P>
<P>If you want, for example, to request an update of the primary key of the
Salary History table in the APPDEV schema, you would execute this command:</P>
<PRE>
 SQL&gt; exec plgdoir.setupdpky (plgte.driver, 'salhist', 'appdev') </PRE>

<P><FONT SIZE="2">Once this command is COMMITTed to the database, you can
generate code from the screen interface and this new setting would be
applied.</FONT></P>
<P>Note: the Update Primary Key procedure does <I>not</I> at this time perform
a cascading update to the foreign keys of all tables referencing that primary
key.</P>
<P><B><I><FONT FACE="Arial" SIZE="2"><A NAME="Columns">Applying Functions to
Columns</A></FONT></I></B></P>
<P><FONT SIZE="2">PL/Generator 1.1 allows you to specify a function that is to
be applied to a value before it is assigned to a column in an INSERT or UPDATE
statement. With release 1.1, you can now ask that the entire value intended for
the column be replaced with another value or expression. This is useful when
you want to </FONT><A HREF="#Defining"><FONT SIZE="2">define standard audit
columns on a table</FONT></A><FONT SIZE="2">. </FONT></P>
<P>If you want the column to be set according to the string in the Function
field, simply preface your text with a &quot;*&quot;. For example, if you want
a particular date column to always be set to SYSDATE, place this text in the
Function field on the Columns screen for that table:</P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">*SYSDATE </FONT></P>
<P><FONT SIZE="2">Suppose that you want to do something a bit more complex,
such as automatically populate one column with the value from another column.
In this case, you will want to specify a function string as a replacement with
the *, but you also will need to be careful about how you specify the original
column from which the new value is derived. Consider a table that contains two
columns: NAME and UC_NAME. The UC_NAME column always must contain the
upper-case version of the NAME column. The function specification in the
Columns screen should then be:</FONT></P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">*UPPER(&lt;parameter for NAME&gt;) </FONT>
</P>
<P><FONT SIZE="2">where &lt;parameter for NAME&gt; is the name of the parameter
containing the value intended for the NAME column. The default format for that
parameter is &quot;NAME_IN&quot;, but you can alter the prefix and suffix in
the Coding Standards screen. Assuming that you have relied on the defaults,
your entry for the Function for UC_NAME would then be:</FONT></P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">*UPPER(name_in) </FONT></P>
<P><FONT SIZE="2">If you decide to use a prefix of &quot;i_&quot; for IN
parameters, then you would supply this value for the Function:</FONT></P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">*UPPER(i_name) </FONT></P>
<P><FONT SIZE="2">A future release of PL/Generator may provide a way to
automatically determine the appropriate parameter name for you, but that is not
currently available.</FONT></P>
<P>&nbsp;</P>
<P><B><FONT FACE="Arial" SIZE="4"><A NAME="Working">Working with PL/Generator
</A></FONT></B></P>
<P><FONT SIZE="2">This section contains advice and directions on how to work
with PL/Generator to handle two common scenarios:</FONT></P>
<OL>
</OL>
<OL>
<LI>Defining standard audit columns on a table</LI>
<LI>Reducing the size of generated identifiers that are too long</LI>
<LI><P><B><I><FONT FACE="Arial" SIZE="2"><A NAME="Defining">Defining Standard
Audit Columns </A></FONT></I></B></P>
<P><FONT SIZE="2">A common requirement with database tables is to add a set of
audit columns keep tracking of who inserted when and who changed when. The
demonstration tables (tedemo.sql in the demo subdirectory) all contain such
fields. Here, for example, is the definition of the department table:</FONT> 
</P>
<PRE>
 CREATE TABLE DEPARTMENT ( 
   department_id  NUMBER(2), 
   name           VARCHAR2(14), 
   loc_ID         NUMBER(3) 
   ,created_by    VARCHAR2(100) DEFAULT USER 
   ,created_on    DATE DEFAULT SYSDATE 
   ,changed_by    VARCHAR2(100) DEFAULT USER 
   ,changed_on    DATE DEFAULT SYSDATE         
   ); </PRE>

<P><FONT SIZE="2">As you can see, I have defined default values for these rows
which will handle values on INSERT, but not UPDATE. I want to make sure that
whenever one of these DML actions take place through the table encapsulation
package, the changed_by and changed_on audit columns are updated automatically.
I also want to make sure that the created columns are not modifiable through
the generated package.</FONT></P>
<P>To accomplish this, I will take the following actions:</P>
<OL START="0">
<LI>Hide the created_by and created_on columns. Their values are populated
automatically at the database level. </LI>
<LI>Set the function value for each of the changed fields to an <I>override</I>
value. </LI>
</OL>
</LI>
</OL>
<DIR>
</DIR>
<DIR>
</DIR>
<P>To do this for a single table, simply open up the Columns screen for that
table and change the entry for the Hide field for the two created columns to
YES. Then for the changed columns, enter values in the Function field for each
of the columns as shown below:</P>
<CENTER>
<P ALIGN="CENTER"></P>
<TABLE FRAME="BORDER" CELLSPACING="1" BORDERCOLOR="#000000" CELLPADDING="7"
 WIDTH="532">
<TR>
<TD WIDTH="55%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><B><FONT SIZE="2">Column Name </FONT></B><FONT SIZE="2"></FONT></P>
</TD>
<TD WIDTH="45%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><B><FONT SIZE="2">Function </FONT></B><FONT SIZE="2"></FONT></P>
</TD>
</TR>
<TR>
<TD WIDTH="55%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">changed_on </FONT></P>
</TD>
<TD WIDTH="45%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">*SYSDATE </FONT></P>
</TD>
</TR>
<TR>
<TD WIDTH="55%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>