📄 generator_api.htm
字号:
<HTML>
<HEAD>
<TITLE>PL/Generator API</TITLE>
</HEAD>
<BODY bgcolor="white" background="bar_products.gif">
<DIR>
<DIR>
<table border=0 cellspacing=0 cellpadding=0 valign=top width=650>
<tr><td width="51" valign=top>
<br>
<td valign="top" width="600"> <IMG SRC=generator.gif WIDTH=285 HEIGHT=48>
<br>
<hr>
<P>
<B><FONT SIZE=4>Part 4. Using the PL/Generator API</B></FONT></A>
<P>
The PL/Generator API or Application Programmatic Interface consists of a set of packages containing programs you can call to modify the behavior of PL/Generator and the code it generates.
<P>You will learn in Part 3 of this document about the Object Information Repository of PL/Generator, which stores information about the generated code characteristics, and also about a number of other PL/Generator features that are not currently available through the screen interface.
<P>
<A HREF="#InformationRepository"><FONT SIZE=3>About the PL/Generator Object Information Repository</FONT></A>
<P>
<A HREF="#KeyCharacteristics"><FONT SIZE=3>Setting Primary Key Characteristics</FONT></A>
<DIR>
<A HREF="#Specifying"><FONT SIZE=2>Specifying the Primary Key</FONT></A><BR>
<A HREF="#Generating"><FONT SIZE=2>Generating the Primary Key</FONT></A><BR>
<A HREF="#Setting"><FONT SIZE=2>Setting Primary Key Descriptor Columns</FONT></A><BR>
<A HREF="#Primary"><FONT SIZE=2>The Update Primary Key Procedure</FONT></A> <BR>
<P>
</DIR>
<A HREF="#ColumnCharacteristics"><FONT SIZE=3>Setting Column Characteristics</FONT></A>
<DIR>
<A HREF="#HidingColumns"><FONT SIZE="2">Hiding Columns</FONT></A><BR>
<A HREF="#IndividualColumns"><FONT SIZE="2">Updating Individual Columns</FONT></A><BR>
<A HREF="#ApplyingFunctions"><FONT SIZE="2">Applying Functions to Columns</FONT></A><BR>
<A HREF="#Defining"><FONT SIZE=2>Defining Standard Audit Columns</FONT></A>
</DIR>
<P>
<A HREF="#ErrorHandling"><FONT SIZE=3>Setting Error Handling</FONT></A>
<DIR>
<A HREF="#HandlingMethod"><FONT SIZE="2">Setting the Exception Handling Method</FONT></A>
</DIR>
<P>
<A HREF="#RetrievalOptions"><FONT SIZE="3">Setting Additional Retrieval Options</FONT></A>
<DIR>
<A HREF="#GeneratingCOUNT"><FONT SIZE="2">Generating COUNT Functions</FONT></A><BR>
<A HREF="#insideSQL"><FONT SIZE="2">Enabling Execution of Functions inside SQL</FONT></A>
</DIR>
<P>
<A HREF="#SettingPerformance"><FONT SIZE=3>Setting Performance/Tracing Options</FONT></A>
<DIR>
<A HREF="#CachingData"><FONT SIZE="2">Caching Data for Faster Access</FONT></A><BR>
<A HREF="#EmployingCursor"><FONT SIZE="2">Employing Cursor Variables</FONT></A><BR>
<A HREF="#RefiningCursor"><FONT SIZE="2">Refining Cursor Select Lists</FONT></A><BR>
<A HREF="#GeneratingStand"><FONT SIZE="2">Generating Stand-alone Programs</FONT></A><BR>
<A HREF="#UsingPLVxmn"><FONT SIZE="2">Using PLVxmn for Execution Tracing</FONT></A>
</DIR>
<P>
<A HREF="#SettingNaming"><FONT SIZE=3>Setting Naming Standards</FONT></A><P>
<A HREF="#Generatingfrom"><FONT SIZE=3>Generating from Views</FONT></A><P>
<A HREF="#Miscellaneous"><FONT SIZE=3>Miscellaneous Settings</FONT></A>
<DIR>
<A HREF="#CompanyName"><FONT SIZE="2">Setting the Company Name</FONT></A>
</DIR>
<P>
<A HREF="#GenerationSession"><FONT SIZE=3>Registering the Generation Session</FONT></A>
<P>
<A HREF="#Clearing"><FONT SIZE=3>Clear Object Information Repository Settings</FONT></A><BR>
<DIR>
<A HREF="#ClearSchema"><FONT SIZE="2">Clear Schema</FONT></A><BR>
<A HREF="#ClearObject"><FONT SIZE="2">Clear Object</FONT></A><BR>
<A HREF="#ClearScreens"><FONT SIZE="2">Clear Screens and Individual Fields</FONT></A>
</DIR>
<A HREF="#Customizing"><FONT SIZE=3>Customizing Generated Code</FONT></A>
<P>
<A HREF="#Managing"><FONT SIZE=3>Managing Privileges</FONT></A>
<DIR>
<A HREF="#Default"><FONT SIZE="2">Default Privileges</FONT></A><BR>
<A HREF="#Activating"><FONT SIZE="2">Activating the Privileges Mechanism</FONT></A><BR>
<A HREF="#Adding"><FONT SIZE="2">Adding a Privilege</FONT></A><BR>
<A HREF="#Removing"><FONT SIZE="2">Removing a Privilege</FONT></A><BR>
<A HREF="#Validating"><FONT SIZE="2">Validating Access</FONT></A>
</DIR>
<P>
<A HREF="#Code"><FONT SIZE=3>Validating the Code</FONT></A>
<DIR>
<A HREF="#Identifiers"><FONT SIZE="2">Validating Identifiers</FONT></A><BR>
<A HREF="#Size"><FONT SIZE=2>Reducing Size of Long Identifier Names</FONT></A> <BR>
<A HREF="#Automatic"><FONT SIZE="2">Automatic Invalid Identifier Correction</FONT></A><BR>
<A HREF="#Compile"><FONT SIZE="2">Compile Failures</FONT></A>
</DIR>
<A HREF="#GenDB"><FONT SIZE=3>Generating to the Database</FONT></A> <BR>
<P>
<A HREF="#Avoidance"><FONT SIZE=3>Complete Avoidance of UTL_FILE</FONT></A> <BR>
<P>
<A HREF="#Utility"><FONT SIZE=3>Export/Import Utility</FONT></A><P>
<A HREF="#Batch"><FONT SIZE=3>Batch Generation of Code</FONT></A><P>
<A HREF="#Keeping"><FONT SIZE=3>Keeping Generated Code Current</FONT></A>
<DIR>
<A HREF="#Registering"><FONT SIZE="2">Registering the Generation Session</FONT></A><BR>
<A HREF="#Regenerating"><FONT SIZE="2">Regenerating Code Based on Registered Sessions</FONT></A><BR>
<A HREF="#Automating"><FONT SIZE="2">Automating the Regeneration Process</FONT></A>
</DIR>
<P>
<HR>
<P>
<B><FONT SIZE=4>
<P>
<A NAME="InformationRepository">About The PL/Generator Object Information Repository</A>
</B></FONT>
<P>
The PL/Generator Objection Information Repository (OIR) is a database table containing special information about how you want your generated code to look. There are four levels of information within the OIR: <DIR>
<DIR>
<P>Global across schemas (for a specific driver)
<P>Global within a schema
<P>For a specific table or view (or other database object)
<P>For a column in a table </DIR>
</DIR>
<P>
With this hierarchy, you can specify a certain standard approach for all tables within a certain schema and then override that standard for specific tables within the schema. PL/Generator will search from the bottom up on that list until it finds an entry for the specified behavior.
<P>
The OIR is actually a table named plg_doir, which is created in the PL/Generator schema. The PLGdoir package offers a programmatic interface to this table. The programs in PLGdoir that you will be using are described below in the appropriate sections.
<P>
You will most likely want to place calls to the appropriate PLGdoir inside a file and then execute that (or the stored procedure created in the file). You will want to avoid calling PLGdoir programs directly from the SQL*Plus command line or other third-party execution environment, because they will not be readily available for re-execution.
<P>
Remember that most calls to the PLGdoir packaged programs make changes to the underlying table. If you exit from SQL*Plus and autocommit is turned on (the default), those changes will be committed. Generally, though, you will need to commit your changes explicitly before they will be saved and available through another connection to the Oracle database.
<P>
The following sections show you how to use different aspects of the OIR API. Characteristics of code are defined within the context of a specific driver. In almost every case, therefore, the first argument will be the driver with which you are working.
<P>
Since the first release of PL/Generator focuses on the generation f table encapsulation packages, the driver used in all examples is the value returned by the call to this named constant: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PLGte.driver </DIR>
</DIR>
</FONT>
<P>
PLGte is the package used to define the driver language within PL/Generator for table encapsulation and driver is a constant containing the name of the driver, which happens to be "TE".
<P>
<B>
<FONT SIZE=4><P><A NAME="KeyCharacteristics">Setting Primary Key Characteristics</A>
</B></FONT>
<P>
PL/Generator allows you to work with primary keys in the following ways:
<UL>
<LI>Define a primary key to PL/Generator if there is not one defined for the table. </LI>
<LI>Specify the method by which the next primary key is generated (i.e., a sequence, function or procedure). </LI>
<LI>Select a unique index as returning the primary key descriptor (a.k.a., name).</LI>
</UL>
<B><I>
<P>
<A NAME="Specifying">Specifying the Primary Key</A> </B></I></FONT>
<P>
Unlike most PL/SQL code generators, PL/Generator allows you to generate packages for tables that do not have a primary key defined. If this is the case, you can specify columns in OIR to be treated as part of the primary key for purposes of code generation.
<P>
To define a column as being part of the table's primary key, call the following program: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGdoir.setpkycol (<BR>
drv IN VARCHAR2,<BR>
tab IN VARCHAR2, <BR>
col IN VARCHAR2, <BR>
pos IN INTEGER := 1, <BR>
sch IN VARCHAR2 := NULL); </DIR>
</DIR>
</FONT>
<P>
where <I>tab</I> is the name of the table, <I>col</I> is the name of the column, <I>pos</I> is the position in the primary key (see note below concerning this value) and <I>sch</I> is the schema in which the table is located (the default of NULL is interpreted to be the current schema).
<P>
Suppose, for example, that we have the following table definition and no primary key: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>CREATE TABLE store (<BR>
region_id NUMBER,<BR>
store_id NUMBER,<BR>
name VARCHAR2(100); </DIR>
</DIR>
</FONT>
<P>
To define the primary key (for purposes of PL/Generator generation) as (region_id, store_id), you would issue the following two calls: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>BEGIN<BR>
PLGdoir.setpkycol (PLGte.driver, 'store', 'region_id', 1);<BR>
PLGdoir.setpkycol (PLGte.driver, 'store', 'store_id', 2);<BR>
END; </DIR>
</DIR>
</FONT>
<P>
Notice that I have specified the columns of the primary key in the same order as found in the table. The default position is 1, which means that if I did not specify those two different positions in my calls to PLGdoir.setpkycol, the result would be a primary key definition of just one column: store_id.
<P>
If you do take this approach to defining the primary key, you should also make sure that a unique index based on these same columns is present. You will then get the same generated code as you would with a real primary key constraint.
<P>
Note: when you specify multiple primary key columns, they should be defined in the same order they appear in the table definition. That is the order they will appear in the parameter list <I>regardless</I> of the order you specify in calls to PLGdoir.setpkycol. This limitation may change in future releases.
<B><I><FONT ><P><A NAME="Generating">Generating the primary key</A> </B></I></FONT>
<P>
Sometimes you will supply the primary key when you perform an insert. In many other situations, however, the primary key will be generated, usually by a sequence. PL/Generator supports three different methods for generating a primary key: by sequence, by procedure and by function. If you indicate in OIR that a primary key is generated, then the insert procedure (PKG.ins) will <I>return</I> the primary key in the parameter list as one or more OUT arguments. PL/Generator will also create a PKG.nextpky function that returns the next primary key.
<P>
To indicate that a primary key is to be generated from a sequence, call the setseqname procedure: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGdoir.setseqname<BR>
(drv IN VARCHAR2,<BR>
tab IN VARCHAR2,<BR>
seq IN VARCHAR2, <BR>
sch IN VARCHAR2 := NULL); </DIR>
</DIR>
</FONT>
<P>
To indicate that a primary key is to be generated from a procedure, call the setpkyproc procedure: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGdoir.setpkyproc <BR>
(drv IN VARCHAR2,<BR>
tab IN VARCHAR2, <BR>
proc IN VARCHAR2, <BR>
sch IN VARCHAR2 := NULL); </DIR>
</DIR>
</FONT>
<P>
To indicate that a primary key is to be generated from a function, call the setpkyfunc procedure: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PROCEDURE PLGdoir.setpkyfunc <BR>
(drv IN VARCHAR2,<BR>
tab IN VARCHAR2, <BR>
func IN VARCHAR2, <BR>
sch IN VARCHAR2 := NULL); </DIR>
</DIR></FONT>
<P>
In the following example I associate a sequence for the emp table: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setseqname (PLGte.driver, 'emp', 'empno_seq'); </DIR>
</DIR>
</FONT>
<P>
In the next call, I specify that a procedure be called which will in return generate the primary key for the current table: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setpkyproc (<BR>
PLGte.driver,<BR>
'STREET', <BR>
'location_pkg.ins (location_type_pkg.location_type_name$pky (''STREET''), retval)'); </DIR>
</DIR>
</FONT>
<P>
This is more complex an action than you are likely to take with the OIR. Keep in mind, however, that if you set up a procedure call to generate/return the next primary key, you must return that primary key (a single integer value) through an OUT or IN OUT argument named "retval".
<P>
These set primary key procedures also allow you to use wild-carding to set this property for multiple tables. Consider the following call: <DIR>
<DIR>
<FONT FACE="Courier,Courier New" SIZE=1><P>PLGdoir.setseqname (PLGte.driver, '%', '_pk_seq'); </DIR>
</DIR>
</FONT>
<P>
In this case, PLGdoir detects that the sequence "name" starts with a "_" and so is treated as a suffix. It then identifies the tables which match the first argument (which means all tables, in this case). For each table, it constructs a sequence name of the format <table_name>_pk_seq. If it finds a sequence by that name, then an entry in OIR is recorded.
<P>
If your sequence "name" ends with a "_", then it is treated as a prefix and the corresponding scan is then performed.
<P>
At this time, PL/Generator only recognizes the underscore as a prefix or suffix indicator.
<B><I><P><A NAME="Setting">Setting Primary Key Descriptor Columns</A> </B></I>
<P>
You specify the primary key column(s) in a table-level constraint. But there is usually also another column or columns which serves as the "descriptor" of the table. For example, in the classic emp table, empno is the primary key while ename is the descriptor. The descriptor column is usually unique, but the presence of a unique index on a column is not enough to necessarily identify the descriptor column. To set the descriptor column(s), you will select the unique index which contains those (and only those) columns.
<P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -