release notes.htm

Oracle PL/SQL procedure generator (second generator type)

<P><FONT SIZE="2">changed_by </FONT></P>
</TD>
<TD WIDTH="45%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">*USER </FONT></P>
</TD>
</TR>
</TABLE>
</CENTER>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">The use of the leading asterisk tells PL/Generator to use
this value <I>in place of</I> any value provided, including NULL.</FONT></P>
<P>You can also set these values as the standard throughout your entire schema
by issuing these commands directly through the API:</P>
<PRE>
PLGdoir.setcolfunc (PLGte.driver, PLGdoir.c_global, 'changed_on', '*SYSDATE'); 
PLGdoir.setcolfunc (PLGte.driver, PLGdoir.c_global, 'changed_by', '*USER'); </PRE>

<P><FONT SIZE="2">By specifying PLGdoir.c_global for the table name, you are
asking that this action be applied immediately to all tables currently defined
in the schema. It is not currently possible to make this global setting through
the screen interface.</FONT></P>
<P>Ideally, when you take this approach, you would also like to hide these
columns from the user. While PL/Generator lets you hide columns (removing them
from the parameter list), it will also remove those columns from the INSERT and
UPDATE statements, which you do not want.</P>
<P>So at least in the current version of PL/Generator, you will need to keep
these columns &quot;exposed&quot;. They will, however, have a default value of
NULL and can be ignored when calls to the update and insert procedures are
made.</P>
<P>Consider the loc table in the demonstration script. After generating a
package for loc and specifying audit fields as described above, I end up with
this update procedure:</P>
<PRE>
    PROCEDURE te_loc.upd ( 
       loc_id_in IN loc.loc_id%TYPE, 
       regional_group_in IN loc.regional_group%TYPE DEFAULT NULL, 
       changed_by_in IN loc.changed_by%TYPE DEFAULT NULL, 
       changed_on_in IN loc.changed_on%TYPE DEFAULT NULL, 
       rowcount_out OUT INTEGER, 
       reset_in IN BOOLEAN DEFAULT TRUE 
       ); </PRE>

<P><FONT SIZE="2">Suppose that I want to update the regional group for location
10. I would need to write code like this:</FONT></P>
<PRE>
 DECLARE 
    numupd INTEGER; 
 BEGIN 
    te_loc.upd (10, 'New Region Group', rowcount_out =&gt; numupd); 
 END; </PRE>

<P><FONT SIZE="2">Notice that I use named notation (=&gt;) to skip over the
audit fields and provide the OUT variable needed to determine how many rows I
had updated successfully.</FONT></P>
<P><B><I><FONT FACE="Arial" SIZE="2"><A NAME="Size">Reducing Size of Long
Identifier Names</A></FONT></I></B></P>
<P><FONT SIZE="2">PL/Generator constructs identifiers (names of procedures,
cursors, etc.) from various components. Sometimes the resulting string is
invalid -- longer than the 30 characters allowed by the Oracle database. In
these cases, PL/Generator will take steps automatically to reduce the size of
the identifier. The result is usually a truncated program name, which
unfortunately is not necessarily the most readable format.</FONT></P>
<P>If you do have a problem of identifiers that are too long, you can usually
avoid the problem by supplying an alias or abbreviation for a named element in
the database.</P>
<P>This situation occurs most readily with foreign keys. Consider the salary
history table, salhist, that is a defined in the demonstration script
(tedemo.sql). There is a foreign key defined for the table named
&quot;SALHIST_EMP_LOOKUP&quot;. </P>
<P>The table encapsulation driver creates a cursor for each foreign key, and
procedures to open and close the cursor. The name of the open procedure would
be:</P>
<DIR>
</DIR>
<P>open_salhist_emp_lookup_all_cur </P>
<P>and this is too long for Oracle (31 characters). So if you do not take any
special actions, this open procedure will be truncated to
&quot;open_salhist_emp_lookup_all_cu&quot;.</P>
<P>Suppose you are not satisfied with that truncation. You can take one of two
actions:</P>
<OL>
</OL>
<OL>
<LI>Define an abbreviation using the PLGdoir.setobjabbrev procedure (which must
be called directly from SQL*Plus or a PL/SQL Integrated Development
Environment, as it is not yet available in the screen interface). </LI>
<LI>Create an alias for the foreign key itself through the Coding Standards
screen.</LI>
</OL>
<DIR>
</DIR>
<DIR>
</DIR>
<P>To take the first approach, execute this command in SQL*Plus:</P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">SQL&gt; exec plgdoir.setobjabbrev ('te',
'salhist', 'sh') </FONT></P>
<P><FONT SIZE="2">Save the changes, regenerate, and you will find that your
open procedure is now named this:</FONT></P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">open_sh_emp_lookup_all_cur </FONT></P>
<P><FONT SIZE="2">If you take the second approach, open the Coding Standards
screen for the salhist table and click on the Add button. Then fill in the
fields in that screen as follows:</FONT></P>
<CENTER>
<P ALIGN="CENTER"></P>
<TABLE FRAME="BORDER" CELLSPACING="1" BORDERCOLOR="#000000" CELLPADDING="7"
 WIDTH="540">
<TR>
<TD WIDTH="42%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><B><FONT SIZE="2">Field Name </FONT></B><FONT SIZE="2"></FONT></P>
</TD>
<TD WIDTH="58%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><B><FONT SIZE="2">Value </FONT></B><FONT SIZE="2"></FONT></P>
</TD>
</TR>
<TR>
<TD WIDTH="42%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">Name </FONT></P>
</TD>
<TD WIDTH="58%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">SALHIST_EMP_LOOKUP </FONT></P>
</TD>
</TR>
<TR>
<TD WIDTH="42%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">Formed by </FONT></P>
</TD>
<TD WIDTH="58%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">shemplu </FONT></P>
</TD>
</TR>
<TR>
<TD WIDTH="42%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">Description </FONT></P>
</TD>
<TD WIDTH="58%" VALIGN="TOP"><DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">New name for foreign key </FONT></P>
</TD>
</TR>
</TABLE>
</CENTER>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT SIZE="2">Then when you generate the package next, you will find that
your open procedure is now named this:</FONT></P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">open_shemplu_all_cur </FONT></P>
<P><FONT SIZE="2">In addition, the name of the cursor itself is changed to max
the open and close procedures.</FONT></P>
<P><B><FONT FACE="Arial" SIZE="4"><A NAME="Driver">Changes to Table
Encapsulation Driver and Generated Code</A></FONT></B></P>
<UL>
</UL>
<UL>
<LI><FONT SIZE="2">Qualified references in SQL: all references to PL/SQL
variables within SQL statements are qualified with the PL/SQL program unit
name. This ensures that you will not have naming conflicts between column names
and variable/parameter names. It may also offer slight improvements ion
execution time of the SQL. </FONT></LI>
<LI>No use of SELECT *. None of the queries in the generated code uses the
SELECT * syntax. Instead, all columns are listed explicitly to improve
performance. </LI>
<LI>Update Primary Key procedure: you can now include procedures that allow you
to update one or more columns of the primary key. Note: these procedures do
<I>not</I> currently implement a cascading update. You will have to update
these values in each table in which this primary key appears as a foreign key. 
</LI>
<LI>Every package body will now contain an initialization section, though the
default is that the initialization section will simply contain the NULL;
operator.</LI>
<LI>The initrec procedure now accepts an &quot;allnull&quot; Boolean argument.
If FALSE (the default), then the record is initialized with the default values
defined in the table definition. If allnull is passed as TRUE, then all fields
in the record are set to NULL.</LI>
</UL>
<DIR>
</DIR>
<DIR>
</DIR>
<P><B><FONT FACE="Arial" SIZE="4"><A NAME="Bugs">Bugs Fixed in PL/Generator
1.1</A></FONT></B></P>
<UL>
</UL>
<UL>
<LI><FONT SIZE="2">The show_invalid_indents procedure raised an error. It will
no longer do this, but you also no longer need to use this procedure. Invalid
identifiers are fixed automatically and a report on such identifiers are
included at the end of the package specification. </FONT></LI>
<LI>Constraint columns are now correctly parsed from constraints and the
parameter lists to validation or check procedures are constructed accurately.
Errors could occur when the same column appeared in more than constraint.</LI>
</UL>
<DIR>
</DIR>
<DIR>
</DIR>
<P><B><FONT FACE="Arial" SIZE="4"><A NAME="Limitations">Limitations/Known
Problems</A></FONT></B></P>
<UL>
</UL>
<UL>
<LI>Due to bugs in earlier Oracle RDBMS releases, PL/Generator is only
supported on releases Oracle7 Release 7.3.4/Oracle8 Release 8.0.4 and above. 
</LI>
<LI>Due to problems with the auto-compilation feature of PL/Generator, it has
been disabled. </LI>
<LI>If you hide a column that is part of a foreign key or primary key, the
generated code may contain errors. If you hide a column, constraints involving
that column will be ignored in the encapsulation package. </LI>
<LI><FONT SIZE="2">You cannot vary the driver sources available for different
objects. This means that you must use, for example, the same header file (the
default being tehdr.gdr) for all objects when using the Table Encapsulation
driver. </FONT></LI>
<LI>If you are running your generated code in 7.3.2, you may encounter errors
if you have requested caching of data. </LI>
<LI>The check constraint validation programs do not apply code defined in the
Function fields of the Columns screen to an argument before validation occurs. 
</LI>
<LI>The import/export facility in the PLGadmin package has been temporarily
disabled. Contact Quest Software Support if you need this capability. </LI>
<LI>If you generate code against the same table in two different sessions, the
second generation will be blocked from completion until you commit or rollback
in the first session. </LI>
<LI>If you specify in the Columns screen that you want to update a column in
the primary key, your generated update procedure will not compile due to
multiple parameters with the same name. If you want to update one or more
columns in the primary key, call the PLGdoir.setupdpky procedure as specified
in <A HREF="#Primary"><FONT SIZE="2">The Update Primary Key
Procedure</FONT></A>. </LI>
<LI><FONT SIZE="2">If you generate a package for a table that contains a column
with the same name as the package, that package specification will not compile.
The generated code looks like this (for example):</FONT></LI>
</UL>
<DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">employee employee.employee%TYPE</FONT></P>
<P><FONT SIZE="2">and that is simply too confusing for the compiler.</FONT></P>
<P>To get around this problem, you will need to supply a non-default prefix or
suffix for the Field of Record entry in the Coding Standards screen. The
default for both is NULL. If, for example, you supply a prefix of
&quot;f_&quot;, then the generated field declaration will look like this:</P>
<DIR>
</DIR>
<P><FONT FACE="Courier New" SIZE="2">f_employee employee.employee%TYPE</FONT> 
</P>
<P><FONT SIZE="2">and the code will then compile without error.</FONT></P>
<P></P>
</BODY>
</HTML>