generator_api.htm

Oracle PL/SQL procedure generator (second generator type)

</FONT>
<P>
I can also make this the standard across all schemas with the following call: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setretcd (PLGte.driver, tab=&gt;PLGdoir.c_global, sch=&gt;PLGdoir.c_global); </DIR>
</DIR>

</FONT>
<P>
This same pattern of setting values at different levels of the hierarchy is available in the following sections and will not be repeated. 
<P>&nbsp; 

<B><FONT  SIZE=4><P><A NAME="RetrievalOptions">Setting Additional Retrieval Options</A> 
</B></FONT>
<P>
This section documents options you can toggle to add to or reduce the number of code elements used to retrieve information from the table contents. 
<B><I>
<P>

<A NAME="GeneratingCOUNT">Generating COUNT functions</A> </B></I>
<P>
You can request generation of functions with return the COUNT of rows for: 

<UL>
<LI>The entire table </LI>
<LI>A single primary key (useful to know whether or not a primary key is present or valid) </LI>
<LI>A foreign key value (one function for each foreign key in the table)</LI>
</UL>

<P>
To generate these functions, call the following procedure: <DIR>
<DIR>

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

</FONT><P>To turn off generation of these functions, call the following procedure: <DIR>
<DIR>

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

<P>&nbsp; </DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</FONT>
<P>

<B><I><A NAME="insideSQL">Enabling Execution of Functions inside SQL</A> </B></I>
<P>
You can request that RESTRICT_REFERENCES pragmas are placed on functions generated into a package by calling the PLGdoir.setrestrictref procedure. The header for this program is:
<PRE>
   PROCEDURE PLGdoir.setrestrictref (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      );
</PRE>
You can disable the use of these pragmas by calling the norestrictref procedure:

   <PRE>PROCEDURE PLGdoir.nosetrestrictref (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      );
</PRE>
So if you wanted to enable all functions generated by the Table Encapsulator driver for use within SQL, across all tables in the SCOTT schema, you would execute this command:
<PRE>
PLGdoir.setrestrictref (PLGte.driver, PLGdoir.c_global, 'SCOTT');
</PRE>

<P>
<B><FONT  SIZE=4><P><A NAME="SettingPerformance">Setting Performance/Tracing Options</A> 
</B></FONT><P>PL/Generator offers a number of options which can improve the performance of your encapsulation: 

<UL>
<LI>Cache data in per-user PL/SQL tables to allow for faster lookups. </LI>
<LI>Create stand alone programs for update, insert and delete so that you can execute these actions through a standard API, but without having to load the entire encapsulation package into memory. </LI>
<LI>Work with cursor variables instead of PL/SQL records. This feature can both improve the performance of your application, but also make it possible to access the full API from within a JDBC environment. </LI>
<LI>Define additional cursors that employ a subset of columns from the table. This is useful when a table has many columns, but they are generally not needed for many query operations.</LI>
</UL>


<B><I><FONT ><P><A NAME="CachingData"> 
<P>Caching Data for Faster Access</A> </B></I></FONT>
<P>
If you have a table which is static during a user session and relatively small, you will achieve much better performance on lookups by loading the entire table into user memory (PL/SQL tables). Instead of going to the database, you simply grab the value from your own memory area. If the table is relatively large and a user tends to hit a certain subset (hotspot) in that table, you might alternately want to make previously-fetched rows available from user memory if that row is needed again.  
<P>
PL/Generator offers the ability to generate table encapsulators that implement both of these caching options. The fact that you are caching data is transparent to the user; the public interface is not affected. 
<P>
To specify that you want the entire table loaded upon instantiation of the package, use the setloadall program: <DIR>
<DIR>

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

</FONT><P>To specify that you want rows from the table loaded into memory as they are fetched, use the setloadincr program: <DIR>
<DIR>

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

</FONT><P>To specify that you do not want any memory-based data reserved after fetching, use the setloadnone program: <DIR>
<DIR>

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

</FONT>
<P>
Note: if your system has a large amount of memory, you could even consider caching large tables using this method. One customer of PL/Generator, for example, is using a Forte front-end, so that a single connection is maintained to the database, and transactions managed within that connection by Forte. Their plan is to load any and every static table, no matter how large, into memory, thereby dramatically improving application performance.  
</FONT><B><I>

<FONT ><P><A NAME="EmployingCursor">Employing Cursor Variables</A> 
</B></I></FONT>
<P>
You can advantage of cursor variables to return information from functions and cursors (instead of normal static cursors and %ROWTYPE record structures). 
<P>
These programs are available: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGdoir.setcurvar (<BR>
   drv IN VARCHAR2, <BR>
   tab IN VARCHAR2, <BR>
   sch IN VARCHAR2 := NULL); 
<P>PROCEDURE PLGdoir.setnocurvar (<BR>
   drv IN VARCHAR2, <BR>
   tab IN VARCHAR2, <BR>
   sch IN VARCHAR2 := NULL); 
<P>FUNCTION PLGdoir.usecurvar (<BR>
   drv IN VARCHAR2, <BR>
   tab IN VARCHAR2, <BR>
   sch IN VARCHAR2 := NULL) <BR>
RETURN BOOLEAN; </DIR>
</DIR>

</FONT><P>To enable use of cursor variables for all tables in the scott schema, issue this command: <DIR>
<DIR>

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

</FONT><P>When you enable this option, the following code elements are created: 

<UL>
<LI>A function returning a cursor variable for every cursor declared in the package specification. </LI>
<LI>A function returning a cursor variable for every function which normally returns a %ROWTYPE or record type structure.</LI>
</UL>


<B><I>

<P>
<A NAME="RefiningCursor">Refining Cursor Select Lists</A></B></I>
<P>
The default set of cursors provided by PL/Generator return all columns for the following scenarios: 

<UL>
<LI>All rows, ordered by primary key </LI>
<LI>One row, for a specified primary key </LI>
<LI>All rows for a specific foreign key match. </LI>
<LI>In addition to these cursors, you can define additional cursors which employ the same WHERE and ORDER BY clauses as the default set, but have a modified list of columns/expressions returned by the cursor. You might do this when a table has many columns, but they are generally not needed for many query operations.</LI>
</UL>

<P>To define an additional set of cursors call the setcurinfo procedure: <DIR>
<DIR>

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

</FONT><P>where tab is the name of the table, cur is the "core" name of the cursor (this name will be supplemented by various suffixes and prefixes which make up the naming conventions), collist is the list of columns/expressions you want to use for this set of cursors, and sch is the schema (the default is the current schema, as returned by USER). 
<P>Suppose that I want to define a cursor for the employee table that returns only the compensation-related fields. I would then make a call as follows: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>BEGIN<BR>
   PLGdoir.setcurinfo (<BR>
   PLGte.driver, 'employee', 'comp', 'employee_id, salary, commission');<BR>
END;<BR>
/ </DIR>
</DIR>

</FONT><P>Note: in addition to the cursors, PL/Generator will also create procedures to open and close each of the cursors. 
</FONT><B><I>
<P>

<A NAME="GeneratingStand">Generating Stand-Alone Programs</A> </B></I>
<P>
When you execute or reference any element in a package, the entire package is loaded. Suppose that you want to insert a row into a table through the API, but that is <I>all</I> you need to do? You don't want to absorb the overhead of loading the package, but you don't want to bypass the API. Under these circumstances, you will want to request that stand-alone procedure be generated in addition to the packaged API.  
<P>
To turn on generation of stand-alone programs, call the following procedure: <DIR>
<DIR>

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

</FONT><P>To turn off generation of stand-alone programs, call the following procedure: <DIR>
<DIR>

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

</FONT><P>PL/Generator currently generates stand-alone update, insert and delete procedures. 

<B><I><P><A NAME="UsingPLVxmn">Using PLVxmn for execution tracing</A> </B></I>
<P>
The PLVxmn package offers a powerful execution trace/debug/production support facility. You can request that calls to PLVxmn.trace be embedded in your application by calling the following program: <DIR>
<DIR>

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

</FONT><P>For example, the following command requests that PLVxmn be used for all tables in your schema: <DIR>
<DIR>

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

<P>&nbsp; </DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>
</DIR>

</FONT><B><FONT  SIZE=4><P><A NAME="SettingNaming">Setting Naming Standards</A> </B></FONT>
<P>
PL/Generator offers a structure called an <I>alias</I>, which you can use to customize almost every aspect of the names and naming conventions for your generated code.  
<P>You will modify the default naming conventions (shown in the tables in this section) by calling the PLGdoir.setalias program in one of the following two forms: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE setalias<BR>
  (drv IN VARCHAR2,<BR>
   tab IN VARCHAR2,<BR>
   orig IN VARCHAR2,<BR>
   subst IN VARCHAR2,<BR>
   descrip IN VARCHAR2,<BR>
   aliastype IN VARCHAR2,<BR>
   ident_query IN VARCHAR2,<BR>
   ident_list IN VARCHAR2,<BR>
   sch IN VARCHAR2 := NULL); 
<P>PROCEDURE setalias<BR>
  (drv IN VARCHAR2,<BR>
   tab IN VARCHAR2,<BR>
   orig IN VARCHAR2,<BR>
   subst IN VARCHAR2,<BR>
   sch IN VARCHAR2 := NULL); </DIR>
</DIR>

</FONT><P>You should only provide values for the tab (table name or global), orig (original value), subst (the substitute value) and sch (the schema or global) arguments.