generator_api.htm

Oracle PL/SQL procedure generator (second generator type)

You can specify the descriptor column(s) for a table with the OIR's setdescindex procedure: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE setdescindex <BR>
   (drv IN VARCHAR2,<BR>
    tab IN VARCHAR2, <BR>
    unique_index IN VARCHAR2 := NULL, <BR>
    sch IN VARCHAR2 := NULL); </DIR>
</DIR>

</FONT>
<P>
You can provide one of the following entries for the unique index: 

<UL>
<LI>The name of the unique index. </LI>
<LI>The name of the leading column in the index. </LI>
<LI>NULL, which means that the first unique index that is <I>not</I> associated with the primary key will be used.</LI>
</UL>

<P>
The following call to this program defines ename as the descriptor column of emp: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setdescindex (PLGte.driver, 'emp', 'ename'); </DIR>
</DIR>

</FONT>
<P>
If you specify a descriptor index for a table, PL/Generator will create functions that return the primary key for a given descriptor and vice-versa. This primary key descriptor column(s) will also be used when processing foreign key information in other tables. 
<P>
&nbsp; 

<B><I><P><A NAME="Primary">The Update Primary Key Procedure</P>
</B></I></FONT><FONT SIZE=2><P>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:</P>
</FONT><PRE>
 PROCEDURE PLGdoir.setupdpky 
    (drv IN VARCHAR2, 
     tab IN VARCHAR2, 
     sch IN VARCHAR2 := NULL); </PRE><DIR>

<FONT SIZE=2><P>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. </P></DIR>

<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>
</FONT><PRE>
 SQL&gt; exec plgdoir.setupdpky (plgte.driver, 'salhist', 'appdev') </PRE>
<FONT SIZE=2><P>Once this command is COMMITTed to the database, you can generate code from the screen interface and this new setting would be applied.</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>


<B><FONT  SIZE=4><P><A NAME="ColumnCharacteristics">Setting Column Characteristics</A> </B></FONT>
<P>
You can set the following characteristics of code associated with the columns of a table: 

<UL>
<LI>Hide columns which you do not want to appear in the API. </LI>
<LI>Specify the columns for which individual update procedures and query functions should be defined. </LI>
<LI>Assign a function to be applied to the column before any updates or inserts.</LI>
</UL>

<B><I><P><A NAME="HidingColumns">Hiding columns</A> </B></I>
<P>
Sometimes you have columns in a table that are no longer used. You can hide these columns so that they will not appear in the parameter lists of any packaged interface programs by calling the hidecol procedure: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGdoir.hidecol <BR>
   (drv IN VARCHAR2,<BR>
    tab IN VARCHAR2, <BR>
    col IN VARCHAR2, <BR>
    sch IN VARCHAR2 := NULL); </DIR>
</DIR>

</FONT>
<P>
In the following example I hide the mgr column of the emp table. <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.hidecol (PLGte.driver, 'emp', 'mgr'); </DIR>
</DIR>

</FONT><B><I>
<P>

<A NAME="IndividualColumns">Updating individual columns</A> </B></I>
<P>
PL/Generator generates update procedures to perform UPDATEs. The default architecture is a single PKG.upd procedure that can update any or all non-primary key columns. You can also request that an update procedure be generated for one or more single columns. You might want to do this to keep constraint and trigger processing to a minimum (the single PKG.upd program will update a column to its current value if you are not actually changing its value; i.e., if you pass in NULL). 
<P>
Call the updatecol procedure shown below to specify that one or more columns should have their own update procedures: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGdoir.updatecol (<BR>
   drv IN VARCHAR2,<BR>
   tab IN VARCHAR2, <BR>
   col IN VARCHAR2, <BR>
   sch IN VARCHAR2 := NULL); </DIR>
</DIR>

</FONT><P>In the following example I request that the sal column of the emp table have its own update program: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.updatecol (PLGte.driver, 'emp', 'sal'); </DIR>
</DIR>

</FONT><B><I><P><A NAME="ApplyingFunctions">Applying functions to columns</A> </B></I>
<P>
</B></I></FONT><FONT SIZE=2><P>PL/Generator 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. You can 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>. </P>
<P>If you want the column to be set according to the string in the Function field, simply preface your text with a "*". 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>

</FONT><FONT FACE="Courier New" SIZE=2><P>*SYSDATE </P></DIR>

</FONT><FONT SIZE=2><P>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:</P><DIR>

</FONT><FONT FACE="Courier New" SIZE=2><P>*UPPER(&lt;parameter for NAME&gt;) </P></DIR>

</FONT><FONT SIZE=2><P>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 "NAME_IN", 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:</P><DIR>

</FONT><FONT FACE="Courier New" SIZE=2><P>*UPPER(name_in) </P></DIR>

</FONT><FONT SIZE=2><P>If you decide to use a prefix of "i_" for IN parameters, then you would supply this value for the Function:</P><DIR>

</FONT><FONT FACE="Courier New" SIZE=2><P>*UPPER(i_name) </P></DIR>

</FONT><FONT SIZE=2><P>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.</P>


<B><I><P><FONT SIZE=4><A NAME="Defining">Defining Standard Audit Columns </P>
</B></I></FONT><FONT SIZE=2><P>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:</P>
</FONT><FONT SIZE=3><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>
<FONT SIZE=2><P>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.</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>
</OL>
</OL>

<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></DIR>
</DIR>
</FONT>
<P ALIGN="CENTER"><CENTER><TABLE BORDER CELLSPACING=1 BORDERCOLOR="#000000" CELLPADDING=7 WIDTH=532>
<TR><TD WIDTH="55%" VALIGN="TOP"><DIR>
<DIR>
<DIR>

<P><B><FONT SIZE=2>Column Name</DIR>
</DIR>
</DIR>
</B></FONT></TD>
<TD WIDTH="45%" VALIGN="TOP"><DIR>
<DIR>
<DIR>

<B><FONT SIZE=2><P>Function</DIR>
</DIR>
</DIR>
</B></FONT></TD>
</TR>
<TR><TD WIDTH="55%" VALIGN="TOP"><DIR>
<DIR>
<DIR>

<FONT SIZE=2><P>changed_on</DIR>
</DIR>
</DIR>
</FONT></TD>
<TD WIDTH="45%" VALIGN="TOP"><DIR>
<DIR>
<DIR>

<FONT SIZE=2><P>*SYSDATE</DIR>
</DIR>
</DIR>
</FONT></TD>
</TR>
<TR><TD WIDTH="55%" VALIGN="TOP"><DIR>
<DIR>
<DIR>

<FONT SIZE=2><P>changed_by</DIR>
</DIR>
</DIR>
</FONT></TD>
<TD WIDTH="45%" VALIGN="TOP"><DIR>
<DIR>
<DIR>

<FONT SIZE=2><P>*USER</DIR>
</DIR>
</DIR>
</FONT></TD>
</TR>
</TABLE>
</CENTER></P>

<FONT SIZE=2><P>The use of the leading asterisk tells PL/Generator to use this value <I>in place of</I> any value provided, including NULL.</P>
<P>You can also set these values as the standard throughout your entire schema by issuing these commands directly through the API:</P>
<DIR>
</FONT><PRE>
PLGdoir.setcolfunc (PLGte.driver, PLGdoir.c_global, 'changed_on', '*SYSDATE'); 
PLGdoir.setcolfunc (PLGte.driver, PLGdoir.c_global, 'changed_by', '*USER'); </PRE>
</DIR>
<FONT SIZE=2><P>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.</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 "exposed". 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>
</FONT><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>
<FONT SIZE=2><P>Suppose that I want to update the regional group for location 10. I would need to write code like this:</P><DIR>
</FONT><PRE>
 DECLARE 
    numupd INTEGER; 
 BEGIN 
    te_loc.upd (10, 'New Region Group', rowcount_out =&gt; numupd); 
 END; </PRE></DIR>
<FONT SIZE=2><P>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.</P>


</FONT><B><FONT  SIZE=4><P><A NAME="ErrorHandling">Setting Error Handling</A> 
</FONT><I>
<P>
<A NAME="HandlingMethod">Setting the exception handling method</A> 
</B></I>
<P>
PL/Generator supports three styles of exception handling: 

<TABLE CELLSPACING=0 BORDER=0 CELLPADDING=7 WIDTH=710>
<TR><TD WIDTH="40%" VALIGN="TOP">
<P>PL/Vision- based exception handling</TD>
<TD WIDTH="60%" VALIGN="TOP">
<P>Exceptions raised in the program are logged through a call to PLVexc.recNgo. The error is then re-raised. This is the recommended approach for normal PL/SQL development.</TD>
</TR>
<TR><TD WIDTH="40%" VALIGN="TOP">
<P>Normal PL/SQL exception handling</TD>
<TD WIDTH="60%" VALIGN="TOP">
<P>Exceptions raised in the programs are propagated out of those programs. Use this approach if you do not want to take advantage of PL/Vision exception handling features like Bailout and standardized logging.</TD>
</TR>
<TR><TD WIDTH="40%" VALIGN="TOP">
<P>Status return codes</TD>
<TD WIDTH="60%" VALIGN="TOP">
<P>Rather than propagate exceptions, the programs return status information (error code and error message) in the parameter list. This is useful when your PL/SQL programs are called from a non-PL/SQL environment such as Powerbuilder or Pro*C.</TD>
</TR>
</TABLE>

<P>You will call one of the following programs to set the exception-handling style in your packages: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE setpsexc (<BR>
   drv IN VARCHAR2,<BR>
   tab IN VARCHAR2, <BR>
   raisemech IN VARCHAR2 := NULL,<BR>
   handlemech IN VARCHAR2 := NULL,<BR>
   sch IN VARCHAR2 := NULL);  
<P>PROCEDURE setplvexc (<BR>
   drv IN VARCHAR2,<BR>
   tab IN VARCHAR2, <BR>
   raisemech IN VARCHAR2 := NULL,<BR>
   handlemech IN VARCHAR2 := NULL,<BR>
   setcontext IN BOOLEAN := FALSE,<BR>
   sch IN VARCHAR2 := NULL); 
<P>PROCEDURE setretcd (<BR>
   drv IN VARCHAR2,<BR>
   tab IN VARCHAR2, <BR>
   raisemech IN VARCHAR2 := NULL,<BR>
   handlemech IN VARCHAR2 := NULL,<BR>
   successval IN VARCHAR2 := 0,<BR>
   failureval IN VARCHAR2 := NULL, /* = SQLCODE */<BR>
   failuremsg IN VARCHAR2 := NULL, /* = SQLERRM */<BR>
   sch IN VARCHAR2 := NULL);  </DIR>
</DIR>

</FONT><P>Suppose that I wanted to use the return code approach for the items table. I would then issue this command (most likely in my teinit.sql script): <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setretcd (PLGte.driver, 'items'); </DIR>
</DIR>

</FONT><P>It is most likely, however, that I will be using the same approach for all tables in my schema. Rather than repeat the above command for each table, I can issue a single call. In the following example, I specify PL/Vision exception handling for all tables in the SCOTT schema: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setplvexc (PLGte.driver, PLGdoir.c_global); </DIR>
</DIR>