generator_api.htm

Oracle PL/SQL procedure generator (second generator type)

<P>
Suppose that you want to change the way that parameter names are formed from column information. The default for IN parameters is [colname}_in. Instead, you want to use a format like pr_[colname]. You want, in other words, a <I>prefix</I> and no suffix, where the prefix is "p" for parameter and "r" for read-only. You would then make these calls: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setalias (PLGte.driver, PLGdoir.c_global, 'inpre', 'pr_');<BR>
PLGdoir.setalias (PLGte.driver, PLGdoir.c_global, 'insuf'); </DIR>
</DIR>

</FONT><P>In other words: you set the prefix and null out the suffix for the "in" wrapper. 
<P>We would suggest that for now you stick with the defaults. The tedemo.sql script does make a number of calls to PLGdoir.setalias, in case you want to explore these options. 
<P>One of the first places you may want to use aliases is to give meaningful names to database constraints. When you define a foreign key constraint, for example, if you do not give it a name, it will be assigned a SYS* name by Oracle. These names will result in code like this (from the employee encapsulation): <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE p_lookup_fkydescs (<BR>
--// Foreign key columns for SYS_C00709 --//<BR>
   pr_job_fk IN employee.job_fk%TYPE,<BR>
   pw_SYS_C00709 OUT te_JOB.I_JOB_FUNCTION_rt,<BR>
--// Foreign key columns for SYS_C00710 --//<BR>
   pr_manager_id IN employee.manager_id%TYPE,<BR>
   pw_SYS_C00710 OUT te_EMPLOYEE.I_EMPLOYEE_NAME_rt,<BR>
--// Foreign key columns for SYS_C00711 --//<BR>
   pr_department_id IN employee.department_id%TYPE,<BR>
   pw_SYS_C00711 OUT te_DEPARTMENT.I_DEPARTMENT_NAME_rt,<BR>
   record_error BOOLEAN := TRUE<BR>
); </DIR>
</DIR>

</FONT><P>Clearly, a less than ideal naming convention for arguments. I can replace those names with others as follows: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setalias (PLGte.driver, 'employee', 'sys_c00709', 'function_lookup');<BR>
PLGdoir.setalias (PLGte.driver, 'employee', 'sys_c00710', 'manager_lookup');<BR>
PLGdoir.setalias (PLGte.driver, 'employee', 'sys_c00711', 'department_lookup'); </DIR>
</DIR>

</FONT><P>

<A NAME="Generatingfrom">Check the Coding Standards screen to view the current set of aliases which will apply to a generation session at that given level. 
<B><FONT  SIZE=4><P>Generating from Views</A> 
</B></FONT><P>In many organizations, developers write their applications against views instead of tables, usually for security purposes. PL/Generator allows you to generate a TE package for any view to which you have access. The issue is how much data dictionary information will be available to PL/Generator to affect the structure of the resulting code. If PLG only has the view name to work with, then it can only obtain the names of the columns for that view from ALL_TAB_COLUMNS.  
<P>
You have two basic options when generating from a view: 
<P>
1. To get a minimally useful encapsulation package, make sure you specify primary key column(s) for the view through calls to PLGdoir.setpkycol. PLG will not be able to generate program units based on foreign keys, unique indexes and constraints, but it will at least construct sensible parameter lists for the basic encapsulation cursors and programs. 
<P>
2. If the view is a "wrapper" for a table, you can define the underlying table to PLG and thereby generate a complete encapsulation package for the view. What is meant by a view as table "wrapper"?  <DIR>
<DIR>

<P>
a. The column list in the view is exactly the same (including same order) as that in the underlying table. 
<P>
b. The view is updateable (against a single table) according to the rules set out by Oracle. If your FROM clause contains just a single table, it is updateable (with various exceptions). If there are multiple tables, all the columns in the SELECT list should belong to the "main" table. </DIR>
</DIR>

<P>
You can associate an underlying table with a view by calling the PLGdoir.settabforview procedure. The header for this program is: <DIR>
<DIR>

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

</FONT><P>In the following example, I specify that for the purpose of encapsulation of the emps_in_ny view I want PL/Generator to use the employee table. <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.set_tabforview (PLGte.driver, 'emps_in_ny', 'employee'); <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="Miscellaneous">Miscellaneous Settings</A> </FONT><I>

<P><A NAME="CompanyName">Setting the Company Name</A> </B></I>
<P>
PL/Generator uses a driver file to place a standard header at the top of your generated code. You can modify this, but again, you will probably want to leave it intact if at all possible. It generates a standard copyright warning, including the company name. You can set your company name with the following program: <DIR>
<DIR>

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

</FONT><P>For example, the following command sets the company name for generation purposes to "ACME": <DIR>
<DIR>

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

</FONT><P>Here is an example of the generated header: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>CREATE OR REPLACE PACKAGE street_pkg<BR>
/*<BR>
|| PL/Vision Table Package Generator Facility:<BR>
|| Package Specification For "street"<BR>
||<BR>
|| (c) COPYRIGHT LightMyFile, Inc 1997. All rights reserved. No part of this<BR>
|| copyrighted work may be reproduced, modified, or distributed in any form or<BR>
|| by any means without the prior written permission of LightMyFile, Inc.<BR>
||<BR>
|| Stored In: street_pkg.sps<BR>
|| Created On: July 11, 1997 09:37:20<BR>
|| Created By: LMFADMIN<BR>
*/ <DIR>
<DIR>
<DIR>
<DIR>
<DIR>
<DIR>
<DIR>

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

</FONT>
<B><I><P><A NAME="Abbrv">Setting Object Abbreviation</P>
</B></I></FONT><P>You can now provide an abbreviation for your object (usually table or view) name. When PL/Generator constructs an identifier that is too long, it will replace any occurrence of the object name with its abbreviation. This will often resolve the invalid identifier situation.</P>
<P>To set an object abbreviation, call the PLGdoir.setobjabbrev procedure as defined below:</P>
<PRE>
PROCEDURE PLGdoir.setobjabbrev
   (drv IN VARCHAR2,
    tab IN VARCHAR2,
    abbrev IN VARCHAR2,
    sch IN VARCHAR2 := NULL);</PRE>
<FONT SIZE=2><P>So to specify "emp" as an abbreviation for the "employee" table in the Table Encapsulation driver, you would execute this command in SQL*Plus or some other PL/SQL environment:</P>
</FONT><PRE>
PLGdoir.setobjabbrev (
PLGte.driver,
'employee',
'emp',
USER);</PRE>
<FONT SIZE=2><P>You can also obtain the currently-defined abbreviation for a table by calling the following function:</P>
</FONT><PRE>
FUNCTION PLGdoir.objabbrev 
(drv IN VARCHAR2,
tab IN VARCHAR2,
sch IN VARCHAR2 := NULL)
RETURN VARCHAR2</PRE>

<P>
<FONT  SIZE=4><B><A NAME="GenerationSession">Registering the Generation Session</A></B></FONT> 
<P>
After PL/Generator generates its code, it registers that generation session in the OIR by call the PLGdoir.reggen procedure. This registration information will be used by PL/Generator to automate regeneration of code when the underlying database object changes (this will most likely happen through a procedure that is executed on a scheduled basis through DBMS_JOB). 
<P>
This registration information will be saved only if changes to the OIR are committed.  
<P>
You can have PL/Generator perform this commit automatically (immediately after the call to PLGdoir.reggen) by calling this program: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGgen.autocommit;  </DIR>
</DIR>

</FONT><P>You can turn off the autocommit feature of PL/Generator (the default setting) by calling this program: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGgen.noautocommit; </DIR>
</DIR>

</FONT><P>The following function, finally, indicates the status of auto-commit. <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>FUNCTION PLGgen.autocommitting RETURN BOOLEAN; </DIR>
</DIR>

</FONT><P>If autocommit is not enabled, then you will see this message after you finish generating code: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>PL/Generator Table Encapsulation Session<BR>
----------------------------------------<BR>
Generating encapsulator package for SCOTT.DEPARTMENT<BR>
Encapsulation of SCOTT.loc completed successfully (COMMIT to register). </DIR>
</DIR>

</FONT><P>If autocommit is enabled, you will see this message: <DIR>
<DIR>

<FONT FACE="Courier,Courier New" SIZE=1><P>SQL&gt; exec plggen.autocommit<BR>
SQL&gt; @te department<BR>
PL/Generator Table Encapsulation Session<BR>
----------------------------------------<BR>
Generating encapsulator package for SCOTT.DEPARTMENT<BR>
Encapsulation of SCOTT.loc completed successfully. <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="Clearing">Clear Object Information Repository Settings</A> </B></FONT>
<P>
Use the following API calls to remove entries you have made to the Object Information Repository. You will, in most cases, have the choice of clearing entries at the driver, schema or object (table/view) level. Each delete program accepts arguments for driver, object and schema. The following table shows what values to enter in order to clear entries at the appropriate level:

<TABLE BORDER="1">
<TR>
	<TD><B>Level</B></TD>
	<TD><B>Driver</B></TD>
	<TD><B>Table</B></TD>
	<TD><B>Schema</B></TD>
</TR>
<TR>
	<TD>Driver</TD>
	<TD>PLGte.driver or name of other driver</TD>
	<TD>PLGdoir.c_global</TD>
	<TD>PLGdoir.c_global</TD>
</TR>
<TR>
	<TD>Schema</TD>
	<TD>PLGte.driver or name of other driver</TD>
	<TD>PLGdoir.c_global</TD>
	<TD>Schema name</TD>
</TR>
<TR>
	<TD>Object</TD>
	<TD>PLGte.driver or name of other driver</TD>
	<TD>Object name</TD>
	<TD>Schema name</TD>
</TR>
</TABLE>

	
<P>
So, for example, to clear all entries for the error handling screen at the driver level, I would enter this command:

<PRE>
PLGdoir.delscreen (
   PLGte.driver, 
   PLGdoir.c_global, 
   PLGdoir.c_scr_errhdlg, 
   PLGdoir.c_global);
</PRE>
To clear all entries for the error handling screen at the schema level for SCOTT, I would enter this command:
<PRE>
PLGdoir.delscreen (
   PLGte.driver, 
   PLGdoir.c_global, 
   PLGdoir.c_scr_errhdlg, 
   'SCOTT');
</PRE>
To clear all entries for the error handling screen at the object level for table SCOTT.DEPARTMENT, I would enter this command:
<PRE>
PLGdoir.delscreen (
   PLGte.driver, 
   'department', 
   PLGdoir.c_scr_errhdlg, 
   'SCOTT');
</PRE>
<P>

<A NAME="ClearSchema"><I><B>Clear Schema</B></I></A>

<P>
Call the PLGdoir.del procedure to delete all OIR entries for a schema. The header for this program is:
<PRE>
   PROCEDURE PLGdoir.del
      (drv IN VARCHAR2,
       tab IN VARCHAR2 := NULL,
       sch IN VARCHAR2 := NULL);
</PRE>
You must specify the driver, '%' for tab ("all tables" since you are clearing all entries for a schema), and the name of your schema. 
<P>