CPL

Using RECORD binds for SQL in PL/SQL programs
Backround
A PL/SQL RECORD is the datatype that corresponds to a row in a schema-level table. It is the natural construct to use when manipulating table rows programatically, especially when a row is read (via SELECT, UPDATE...RETURNING or DELETE...RETURNING), manipulated programatically, and then recorded (via INSERT or UPDATE) in an another table with the same shape. It has these advantages over representing the column values in individual variables...
 the declaration is compact, using mytable%rowtype
 by using %rowtype, the declaration is guaranteed to match the corresponding schema-level template and is immune to schema-level changes in definition of the shape of the table
 the record variable can be used as a formal and actual parameter to a procedure or function giving compact and guaranteed correct notation and allowing optimizations in the implementation of parameter passing.
 and of particular relevance in this discussion the SQL-PL/SQL interface allows a syntax which does not list the columns of the source/target table explicitly, again allowing for robust code which has a greater degree of schema-independence.
Status at Oracle9i Version 9.0.1
As the matrix below shows, the use of RECORDs in the SQL-PL/SQL interface was greatly restricted. The syntax was supported only for the the single row SELECT case (but for that in both static SQL and native dynamic SQL). Thus the advantages listed above were not yet capable of being realized.
Restrictions Removed in Version 9.2.0
A large number of restrictions have been lifted at Version 9.2.0. Full support is now provided for all flavors of SELECT.
And support is provided (with some minor restrictions -see below) for all static SQL flavors of INSERT, DELETE and UPDATE.
Restrictions Remaining in Version 9.2.0
 No use is supported of EXECUTE IMMEDIATE in connection with INSERT, UPDATE or DELETE. (It is supported for SELECT, as stated above.) In other words, RECORDs are not yet supported for DML using native dynamic SQL.
 With DELETE and UPDATE...RETURNING the column-list must be written explicitly in the SQL statement.
 In the bulk syntax case, you cannot reference fields of the in-bind table of records elsewhere in the SQL statement (esp eg in the where clause).
Guide to the Samples
These samples rely on the employees table. This is in the hr schema which is installed in the seed database. The script to create this schema is demo/schema/human_resources/hr_cre.sql under the Oracle Home directory.
This file, start.htm, and all the files it refers to, rec*.htm, are on the plsql/demo directory under the Oracle Home directory. First run rectab.sql and then recpkg.sql to set up the environment. Then you can copy the code samples straight from the browser to SQL*PLus.
There's a corresponding set of plain text files, rec*.sql, (only on the plsql/demo directory) which can be run with start or @ at the SQL*Plus prompt. The file rec_all.sql runs them all and cleans up after itself.
Navigation Matrix for Samples
What static SQL native dynamic SQL
SELECT
- single row ok ok
- many rows, single row syntax ok ok
- many rows, bulk syntax PLS-00597 @9.0.1

illustrates LIMIT clause PLS-00597 @9.0.1

illustrates LIMIT clause
INSERT
- single row ORA-00906 @9.0.1 PLS-00457 @9.2.0
- many rows, bulk syntax ORA-00906 @9.0.1

illustrates SAVE EXCEPTIONS PLS-00457 @9.2.0
DELETE with RETURNING
- single row ORA-00600 @9.0.1 PLS-00429 @9.2.0
- many rows, bulk syntax PLS-00597 @9.0.1 PLS-00429 @9.2.0
UPDATE with RETURNING
- single row ORA-00600 @9.0.1 PLS-00429 @9.2.0
- many rows, bulk syntax PLS-00597 @9.0.1 PLS-00429 @9.2.0
UPDATE ... SET ROW =
no need for RETURNING since you already have the RECORD before you do the SQL
- single row ORA-00904 @9.0.1 PLS-00457 @9.2.0
- many rows, bulk syntax PLS-00436 @9.2.0 PLS-00457 @9.2.0


Key
supported in 9.0.1
new in 9.2.0
partial support in in 9.2.0
not supported, 9.2.0 or earlier
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 7-May-2002
Associative arrays
New in Oracle9i Database Version 9.2.0
Associative array is the new name for index-by table (even earlier known as PL/SQL table). These have been available for some time, but before Version 9.2.0 the only possible declaration was...
type my_tab_t is table of number index by binary_integer;
Version 9.2.0 introduces two new possibilities...
type my_tab_t is table of number index by pls_integer;
type my_tab_t is table of number index by varchar2(4000);
The datatype for of is not remarkable in the above, but the datatype for index by is. The size of the varchar2 may be anything up to the legal limit of 32767.
Note: A declaration other than with index by pls_integer or index by varchar2(n) still fails, thus...
PLS-00315: Implementation restriction: unsupported table index type
...so if, say, it is required to index by date, then a To_Char conversion must be used.
The new ability for index by pls_integer removes the need to use the older binary_integer in any new coding exercise.
The new ability for index by varchar2 allows many new exciting coding possibilities, and is responsible for the name change for the feature to associative arrays, also in keeping with general terminology use when discussing 3GLs.
For example, index-by tables are often used to cache table values to give a performance boost in special situations, especially where very frequent lookup is called for. It has previously been necessary to code this explictly. This comparison shows how an index by varchar2 table simplifies the coding. And not only is the coding simpler, it's more efficient!
For completeness, this comparison allows you to time populating an index by varchar2 table with one million tuples, and to time populating a database table with the same data. The PL/SQL table is about 20 times faster. It also shows the paradigm for stepping through the elements of an index by varchar2 table...
idx := the_table.First;
while idx is not null
loop
-- do something
idx := the_table.Next(idx);
end loop;
This scenario provides an illustration of the power of an index by varchar2 table to deliver a uniquely elegant and efficient solution.
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 13-May-2002
Utl_File
Overview of enhancements introduced in
Oracle9i Database Version 9.2.0
Pre 9.2.0, the way to denote the director(ies) for files was via the UTL_FILE_DIR initialization parameter. This suffered from the disadvantages that the instance had to be bounced to make changes to the list of directories and that there was no secutity scheme (all users could access files on all diretories). Version 9.2.0 allows the same mechanism (the DIRECTORY schema object) to be used with Utl_File as is used for BFILEs. You should consider the UTL_FILE_DIR initialization parameter as slated for deprecation.
The line length limit for Utl_File.Get_Line and Utl_File.Put_Line has been increased from 1K to 32K.
The following new API primitives have been introduced...
For operating system file management
 procedure Fgetattr
 procedure Fcopy
 procedure Fremove
 procedure Frename
For handling RAW data
 procedure Fseek
 function Fgetpos
 procedure Get_Raw
 procedure Put_Raw
In additon, Get_Line and Get_Line_Nchar have acquired a new defaulted binary_integer parameter len. And Put_Line has acquired a new defaulted boolean parameter autoflush.
Utl_File performance is improved via transparent internal reimplementation.
To explore some of the possible exceptions in the following tests, you'll need to log on to the datatbase machine as the Oracle user and create and delete files.
Create the UTL_FILE_TEST DIRECTORY
and the PROGRAMMER test user
These samples uniformly use the new-in-9.2.0 approach for the location formal parameter by providing the name of a directory as the actual parameter. We'll use the same filesystem directory that's designated for user dump files. And for convenience, we'll let the test user query the v$parameter view and execute Dbms_Pipe. Create the user.
API
The package exposes the following prcedures and functions. None is overloaded.
Opening, writing and closing
 function Fopen
 function Is_Open
 procedure Put_Line
 procedure Fclose
 procedure Fclose_All
This sample illustrates all of the above APIs. This illustrates the new 9.2.0 functionality to open using a directory schema object.
Mimicing Unix ls, cp, mv, rm
 procedure Fgetattr
 procedure Fcopy
 procedure Frename
 procedure Fremove
This sample illustrates all of the above APIs, all of which are new in 9.2.0.
Handling line-oriented character data
This sample demonstrates autoflush by writing a line to file and waiting on a message via Dbms_Pipe from another session to give you time to observe with tail -f (on Unix, or the equivalent on other operating systems). Create the Wait package. Otherwise, this sample shows only pre-9.2.0 functionality.
 procedure Get_Line
 procedure Put
 procedure Putf
 procedure Put_Line
 procedure New_Line
 procedure Fflush
This sample illustrates all of the above APIs. and illustrates the new len parameter to Get_Line and the new autoflush parameterto Put_Line.
Handling line-oriented NCHAR data
Each procedure (or function) for NCHAR is identical in shape and meaning to its counterpart for VARCHAR2 with the exception that Put_Line_Nchar doesn't have an autoflush parameter. Thus no code samples are provided.
 function Fopen_Nchar
 procedure Get_Line_Nchar
 procedure Put_Nchar
 procedure Putf_Nchar
 procedure Put_Line_Nchar
Handling RAW data
 procedure Put_Raw
 procedure Get_Raw
 function Fgetpos
 procedure Fseek
This sample illustrates Get_Raw. This sample illustrates Put_Raw. This sample illustrates Fseek.
Note: Port-specific Bug #2546782, raised against 9.2.0 on Windows 2000, reports wrong output from Utl_File.Put_Raw.
Exceptions
 file_open
 charsetmismatch
 invalid_path
 invalid_mode
 invalid_filehandle
 invalid_operation
 read_error
 write_error
 internal_error
 invalid_maxlinesize
 invalid_filename
 access_denied
 invalid_offset
 delete_failed
 rename_failed
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 10-June-2002
Cursor reuse in PL/SQL static SQL
Background
In order that a SQL satement can be executed, it has to be parsed (checked for syntactic and semantic correctness) and the execution plan has to be calculated. All this costs computational resources.
To save on these costs, the Oracle instance maintains a system-wide LRU cache (aka the shared cursor cache) - exposed via v$sqlarea - of previously encountered SQL statements and appropriate derived information so that when the next SQL statement is submitted for parsing it is checked for match against the cached ones. (The definition of the criterea for a match is beyond the scope of this tutorial. Roughly speaking, the current statement must be both textually identical to its match candidate, famously to the extent of whitespace and upper/lower case identity, and the types of the bind variables must match.) When the current statement is matched, the stored derived information (parse tree, execution plan, etc) is reused and computational cost is saved.
Moreover, the above processing has to be done in the context of a cursor which itself has to be opened and associated with the SQL statement in question, again at some cost.
A given session might have one or several concurrently open cursors. Information on these, including the foreign key reference to v$sqlarea is exposed via v$open_cursor.
Advanced programmers using the "difficult" interfaces to cursor manipulation - eg OCI in a C programming environment or the Dbms_Sql package in a PL/SQL programming environment - typically code explicit, relatively elaborate approaches to minimize cursor costs. This cost-saving paradigm is described below.
Programmers whose requirements can be satisfied by static SQL in a PL/SQL programming environment enjoy the benefits of an implicit implementation of this cost-saving paradigm while writing simple, easy-to-maintain, code.
However, it is possible to subvert these implicit benefits by careless programing. This tutorial presents the conceptual background for understanding this and some guidelines for avoiding such mistakes.
 The explicitly programmed cost-saving paradigm
 Observing v$open_cursor
 Benefiting from the cost-saving paradigm implicitly
by using PL/SQL static SQL
 Demonstrating the LRU behavior of the PL/SQL cursor cache
 How careless programing can subvert the benefit
of the PL/SQL cursor cache
 How to diagnose and avoid subverting the benefit
of the PL/SQL cursor cache
 How_To_Diagnose.htmPL/SQL REF CURSORs don't (up to Oracle9i) implement
the cost-saving paradigm
 Ref_Cursors_Basic.htmREF CURSORs: the bigger picture
 PL/SQL native dynamic SQL does not
implement the cost-saving paradigm
Conclusion
 Using Dbms_Sql gives you the ultimately fine-grained level of control over cursors and thus it allows you to implement an approach which makes maximal possible reuse of the resources used to set up a cursor and to parse the SQL statement, even when this is a soft-parse (ie the statement is found in the v$sqlarea cache).
Moreover using Dbms_Sql develops your mental model in this critical area of functionality, so it's good at least to study it and practice with it a little.
There are some use cases (for example when you don't know the number of bind variables until runtime) which are not supported using native dynamic SQL, and in such cases Dbms_Sql is the only viable approach.
Thus Oracle recommends that you use native dynamic SQL when its syntax supports your requirements, and that you use Dbms_Sql only when your functional requirements cannot be satisfied by native dynamic SQL. The theoretical performance advantage of Dbms_Sql over native dynamic SQL is in fact balanced by the performance advantages of the latter's tighter integration into the PL/SQL language, so you should not need to choose Dbms_Sql for performance reasons when other approaches are viable.
 Static SQL constructs in PL/SQL, using both explicit and implicit cursors, give you the benefits of the cost-saving paradigm without the effort of programming it.
This is achieved because the PL/SQL runtime system does not actually close your cursor (in the sense of the actions impemented by Dbms_Sql.Close_Cursor, aka a hard-close) when the PL/SQL close statement is executed, or when an implicit cursor statement completes. Rather, it just soft-closes the cursor you think you've closed, ie it marks it as a candidate for later hard-close in the PL/SQL cursor cache, a LRU cache of potentially re-usable open cursors exposed via v$open_cursor.
 It is possible to subvert this by careless programming, but it's easy to diagnose and correct such errors by monitoring v$open_cursor.
 REF CURSORs used with static SQL do not currently (up to Oracle9i) give you the benefit of the cost-saving paradigm. The compiler does have information, and this may be improved in a later release.
 Native dynamic SQL, which often implies the use of a REF CURSOR opened with a dynamic string, does not implement the cost-saving paradigm since it's current exposure in PL/SQL (up to Oracle9i) does not give language constructs to distinguish between opening and parsing on the one hand and binding, executing and fetching on the other. However (as sated above) this penalty is offset by the greater efficiency due to it's tighter integration.
 There is no reason ever to omit the close statement to balance the open for any of the above flavors of cursor construct. If you do omit it, you've programmed a potential memory leak: this is simply bad code! Nevertheless, the PL/SQL runtime system generally rescues you from such mistakes. However, you should not rely on this.
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 4-Jun-2003
Using CASE Statements in PL/SQL programs
While CASE constructs don't offer any fundamentally new semantics, they do allow a more compact notation and some elimination of repetition with respect to what otherwise would be expressed with an IF construct. Consider the implementation of a decision table whose predicate is the value of a particular expression. These two fragments…
case n
when 1 then Action1;
when 2 then Action2;
when 3 then Action3;
else ActionOther;
end case;
…and…
if

n = 1 then Action1;
elsif n = 2 then Action2;
elsif n = 3 then Action2;
else ActionOther;
end if;
…are semantically almost identical. But coding best practice gurus generally recommend the CASE formulation because it more directly models the idea. By pulling out the decision expression n to the start and by mentioning it only once the programmer's intention is clearer. This is significant both to the proof reader and to the compiler, which therefore has better information from while to generate efficient code. For example, the compiler knows immediately that the decision expression needs to be evaluated just once. And, since the IF formulation repeats the decision expression for each leg, there's a greater risk of typographical error which can be difficult to spot.
Moreover, the CASE formulation makes it explicit that the coded cases are the only ones that need handling (see the discussion of the case_not_found exception below).
CASE constructs are available in most programming languages. Oracle9i introduces them in PL/SQL (and in SQL).
CASE Expressions
A CASE expression selects a result and returns it. To select the result, the CASE expression uses a selector, an expression whose value is used to select one of several alternatives. Consider these two semantically almost identical fragments
text := case n
when 1 then one
when 2 then two

when 3 then three
else other
end case;
…and
if
n = 1 then text := one;
elsif n = 2 then text := two;
elsif n = 3 then text := three;
else text := other;
end if;
The CASE formulation makes it explicit that the intention of the fragment is to provide a value for text. This sample illustrates using a CASE expression in PL/SQL.
An alternative to the CASE expression is the CASE statement, where each WHEN clause can be an entire PL/SQL block.
Searched CASE Statement AND Searched CASE Expression
For both the CASE statement and the CASE expression, the searched variant tests each leg on an arbitrary boolean expression, rather than on equality on a single expression common for all legs, thus
case
when n = 1 then Action1;
when n = 2 then Action2;
when n = 3 then Action3;
when ( n > 3 and n < 8 ) then Action4through7;
else ActionOther;
end case;
…and…
text := case
when n = 1 then one

when n = 2 then two
when n = 3 then three
when ( n > 3 and n < 8 ) then four_through_seven
else other
end;
Note: With the CASE formulation as with the IF formulation, the leg which is selected for particular data values will in general depend on the order in which the legs are written. Consider…
case
when this_patient.pregnant = 'Y' then Action1;
when this_patient.unconscious = 'Y' then Action2;
when this_patient.age < 5 then Action3;

when this_patient.gender = 'F' then Action4;
else ActionOther;
end case;
An unconscious pregnant woman will receive Action1. This sample illustrates using the Searched CASE constructs in PL/SQL.
CASE_NOT_FOUND Exception
A subtle difference between the CASE construct and the corresponding IF construct occurs when the ELSE leg is omitted. With the IF consruct, if none of the legs is selected then there is no action. But with the CASE construct, if none of the legs is selected then the case_not_found exception (ORA-06592: CASE not found while executing CASE statement) is raised, thus…
...
p:=0; q:=0; r:=0;
case
when p = 1 then Action1;
when r = 2 then Action2;
when q > 1 then Action3;
end case;
exception
when case_not_found
then Dbms_Output.Put_Line ( 'Trapped: case_not_found' );
...
This sample illustrates trapping the exception in PL/SQL.
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 7-Jan-2003
Bulk Binding Enhancements
Overview
The assigning of values to PL/SQL variables in SQL statements is called binding. The binding of an entire collection at once is called bulk binding. Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. Typically, using bulk binds improves performance for SQL statements that affect four or more database rows. The more rows affected by a SQL statement, the greater the performance gain from bulk binds. Following are the bulk binding enhancements supported by Oracle9i (Oracle 9.0.1 release and later)
 Handling and Reporting Exceptions
 Bulk Binding in Native Dynamic SQL
 Defining
 In-Binding
 Out-Binding
Oracle9i Enhancement For Bulk Fetch From Cursor Variable Assigned By Native Dynamic SQL is described in the section under "Table Functions and Cursor Expressions".
Business Benefits of Bulk Binding Enhancements
 Increased speed and scalability for appropriate applications
 Improved functionality by virtue of better exception handling
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 7-Jan-2003
Table Functions and Cursor Expressions
Overview
Cursor expressions (sometimes known as cursor subqueries) are an element of the SQL language and pre-Oracle9i were supported in SQL and by certain programming environments but not by PL/SQL. Oracle9i introduces PL/SQL support for cursor expressions. For example, a cursor expression can be used in the SELECT statement used to open a PL/SQL cursor, and manipulated appropriately thereafter. It can also be used as an actual parameter to a PL/SQL procedure or function, which has great significance in connection with table functions.
Table functions were also supported (in rudimentary form) in pre-Oracle9i, but a number of major enhancements have been made at Oracle9i. A table function can now be written to deliver rows pipeline fashion as soon as they are computed, dramatically improving response time in a “first rows” scenario. It can now be written to accept a SELECT statement as input, allowing an indefinite number of transformations to be daisy-chained, avoiding the need for storage of intermediate results. And it can now be written so that its computation can be parallelized to leverage Oracle’s parallel query mechanism.
The enabling of parallel execution of a table function means that it’s now possible to leverage the power of PL/SQL in the Extract,Transform and Load (aka ETL) phase of data warehouse applications without serialization.
 Cursor Variables - Recap
 Oracle9i enhancement for bulk fetch from cursor variable assigned by native dynamic SQL
 Manipulating Cursor Expressions in PL/SQL
 Using a Cursor Expression as an actual parameter to a PL/SQL function
 "YOUNG MANAGERS" Scenario
 Table Functions - Recap
 Pipelined Table Functions - New in Oracle9i
 Piping data from one Table Function to the next - New in Oracle9i
 The "Young Managers" Scenario revisited - Table Function approach
 Fanout: Using Table Functions with side effects
 Parallelizing Table Function execution - New in Oracle9i
Syntax for Table Function based on Schema-Level Type
When a table function is written to return a schema-level type, the syntax required to invoke it is somewhat verbose. For completeness it is illustrated in code sample.
Business benefits of Table Functions and Cursor Expressions
 Cursor expressions allow encapsulation of logic for re-use in compatible query situations, giving increased developer productivity and application reliability.
 Table functions give increased functionality by allowing sets of tuples from arbitrary external data sources and sets of tuples synthesized from arbitrary computations to be invoked (as if they were a table) in the FROM list of a SELECT clause. For convenience they can be used to define a VIEW, giving new functionality.
 Table functions can be used to deliver the rows from an arbitrarily complex PL/SQL transformation sourced from Oracle tables (including therefore other table functions) as a VIEW, without storage of the calculated rows. This gives increased speed and scalability. And increased developer productivity and application reliability.
 Taking the VIEW metaphor a step further, the input parameters to the table function allow the VIEW to be parameterizable, increasing code re-usability and therefore increasing developer productivity and application reliability.
 A table function with a ref cursor input parameter can be invoked with another table function as the data source. Thus table functions can be daisy-chained allowing modular program design and hence increased ease of programming, re-use and application robustness.
 Table function execution can be parallelized giving improved speed and scalability. This, combined with the daisy-chaining feature, makes table functions particularly suitable in datawarehouse applications for implementing Extraction, Transformation and Load operations.
 Fanout (DML from an autonomous transaction in the table function) adds functionality of particular interest in datawarehouse applications.
 A table function allows data stored in nested tables to be queried as if it were stored relationally, and data stored relationally to be queried as if it were stored as nested tables. (This is illustrated in the Runner's Training Logs example scenario presented in the code sample for Multilevel Collections.) This allows genuine independence between the format for the persistent storage of data and the design of the applications which access it. (A VIEW can be defined on a table function, and INSTEAD OF triggers can be created on the VIEW to complete the picture.)
Multilevel Collections
Overview
There are two schema-level collection prototypes: VARRAY and (nested) TABLE. Both define one-dimensional ordered arrays of elements of a specified type, and can be leveraged in the creation of user-defined schema-level types thus…
create type Arr_t is varray(255) of number;
/
…or…
create type Tab_t is table of varchar2(2000);

/
If appropriate, the element type can be an object type thus…
create type Obj_t is object ( a number, b varchar2(4000), c date );
/
Instances of schema-level types based on VARRAY or TABLE can be stored as fields of a column in a relational database table thus…
create type Arr_t is varray(255) of Obj_t;

/
create table t (id number, arr Arr_t);
/
insert into t ( id, arr ) values
( 1, Arr_t ( Obj_t ( 1, 'one', '1-Jan-01' ), Obj_t ( 2, 'two', '2-Jan-01' ) ) );
insert into t ( id, arr ) values
( 5, Arr_t ( Obj_t ( 5, 'five', '5-Jan-01' ), Obj_t ( 6, 'six', '6-Jan-01' ) ) );
The main differences between VARRAY and TABLE are:
 TABLE is unordered but VARRAY preserves order
 VARRAY has a defined upper bound whereas the TABLE is unbounded.
 A VARRAY field is stored inline for small sizes (<= 4000 bytes) and in an opaque system-managed LOB within the given relational table for larger sizes, while a TABLE field is stored as several rows in a separate opaque system managed relational table.
This impacts the efficiency of access, leading to a generically familiar trade-off: non-negotiable maximum collection size with faster access versus unlimited collection size with slower access.

PL/SQL allows variables of user-defined types and provides mechanisms for passing data stored in schema-level collections to and from the corresponding PL/SQL structures thus…
declare
cursor c is select id, arr from t;

v_id number;
v_arr Arr_t;
begin
open c;
loop
fetch c into v_id, v_arr; exit when c%notfound;
Dbms_Output.Put_Line ( v_id );
for j in v_arr.first..v_arr.last
loop
Dbms_Output.Put_Line ( v_arr(j).a, v_arr(j).b, v_arr(j).c );
end loop;

end loop;
close c;
end;
/
PL/SQL also allows types based on VARRAY or TABLE to be declared within library units. This will typically be in a package for reuse across several library units. In addition, PL/SQL allows the index-by variant of TABLE. (This variant is not allowed as the basis of a schema-level type.) All the above is supported pre-Oracle9i.
Oracle9i Enhancements
 Collections of Collections - New in Orcale9i
 "Runner's Training Logs" Example Scenario
Business Benefits of Multilevel Collections
Storing data as collection instances in a column of a database table is a pre-optimization to favor certain access paths (typically accessing all the elements of the collection for each selected row). PL/SQL is needed to populate and query such collection instances. Modeling data as a collection in a PL/SQL program is essential for the implementation of certain algorithms (see for example the perfect triangles algorithm in code sample ). A collection can be used as the target of a bulk bind improve the performance of data transfer between the database and the PL/SQL processing.
 Previously only one-dimensional phenomena could leverage the above benefits. Multilevel collections now offer them for an arbitrarily enlarged set of real-world problems.
Enhancements to the Utl_Http Package
Overview
The B2B component of eBusiness depends on automatic communication between business sites across the public internet. The HTTP transport mechanism is used to send the request and to receive the reply. Though partners in a particular B2B relationship could define standards for their protocols from scratch, the de facto standard is emerging to use XML for both request and reply. Of course we can expect increasing standardization in future, extending to cover the specifics of the XML encoding.

Oracle has technology to allow both the sender and the receiver straightforwardly to implement their services backed by an Oracle9i database, and using only PL/SQL on top of productized APIs. The simplest way to code the receiver is to use mod_plsql, either directly via the HTTP listener component of the Oracle9i database or via Oracle9iAS and to write a PL/SQL database procedure which is exposed as the URL representing the request. The XML document expressing the request is decoded, the database is accessed to supply the reply information and is updated appropriately, and the reply is encoded and sent using Htp.Print or similar. This end of the dialogue is beyond the scope of this documentation.

The request is typically sent (or more likely queued and then sent later) in the body of a database trigger which fires on an event like a stock level falling below the defined threshold for reordering. The XML document expressing the request is encoded by accessing current database values and sent, typically using the "POST" method to ensure that an arbitrarily large XML request can be sent piecewise. Authentication information (e.g. username and password) is likely to be required as part of the request. And possibly the request header will need to be explicitly set to reflect an agreed protocol. Then the response is (started to be) fetched and its status code is checked for errors and its header is checked for protocol compliance. Then the arbitrarily large XML document expressing the response itself is fetched piecewise, decoded, and the information is used to update the database. A robust implementation is likely to have a component which automatically sends a generated email to a system administrator in the event of an error. Oracle has features for encoding and decoding XML, and for sending email from the database, but these are beyond the scope of this documentation.

Depending on the design of the workflow, state may need to be represented. For example, a customer might request a price and delivery date for a given quantity of items from several vendors. Each vendor would reply with price and delivery date and with an "offer good to" date. When the customer site sends a request to the selected vendor to place a definite order, it will need to refer to the specific offer. If such a scheme is used within a single organization, for example to communicate between databases at local offices in different countries, then the communication protocol can be designed from scratch, and most likely an offer reference number will be exchanged as part of the XML encoding. However, if the partners in the B2B relationship are completely independent, and especially if the relationship is casual, then the requestor will have to follow whatever protocol the receiver has defined. It might be that the receiver has implemented the state which represents an ongoing dialogue using cookies. In this case the sender will need to handle these programmatically.
Oracle9i Enhancements
 General
 Encoding of Character Data
Business Benefits
 Substantially increased functionality allowing implementation of fully functional B2B applications
B2B scenario illustrating the use of the Utl_Http package
The scenario
This scenario models the typical customer / vendor situation. Picture a community of customers and vendors. Each customer buys items from one or many vendors and each vendor sells items to one or many customers. Customers and vendors maintain inventory data in Oracle databases. The community has appreciated the benefits of eBusiness and has agreed to standardize on a way to express the variety of requests and replies that need to be sent...
 customer #1 (to many vendors):
 This is customer #1
 Our ref #nnnnn
 Can you supply me with item X?
 What is your price for N units?
 By when can you deliver?
 vendor #1:
 This is vendor #1
 Your ref #nnnnn
 We don't stock item X
 vendor #2:
 This is vendor #2
 Your ref #nnnnn
 N units of item X will cost you Y
 We can deliver by D1
 This offer good until D2
 Our offer ref #mmmm
 customer #1 to vendor #2:
 This is customer #1
 Our ref #ppppp
 Your offer ref #mmmm
 Supply me with N units of item X
 vendor #2:
 This is vendor #2
 Your ref #ppppp
 Order for N units of item X confirmed
 Our order ref #qqqqq
They agree to adopt XML via HTTP as the communication format and transport mechanisms since this supports all current messaging requirements and is readily extensible, without requiring changes to the basic message exchange and parsing mechanisms.
They recognise that occasionally the attempt to send a message to a particular site will fail (e.g. because that site is down) and require than an administrator at the sending site be notified of the failure by automatically generated email.
Implementation Concept
A customer database event will trigger the sending of a message. The message will be constructed from current database values. It will be sent to one or several password protected URLs using the Utl_Http API. URL and password data to be retrieved from the database.
The vendor URL will be implemented as a PL/SQL procedure via mod_plsql and Htp.Print, etc. This procedure will parse the message and access vendor database values to compose the reply and will record data about the reply in the database.
The customer will parse the return message and update customer database values accordingly. If an error is detected then the Utl_Smtp API will be used to alert the administrator by email.
Simplified Scenario for the code samples
One customer communicates with one vendor. The sample will be more convincing if the customer site and vendor sites are implemented in different databases on different machines.
connect system/manager@customer_site
create user customer identified by customer;
grant resource, connect to customer;
connect system/manager@vendor_site
create user vendor identified by vendor;
grant resource, connect to vendor;
But it of course will work with both pieces in a single database.
Customer site
Customer inventory is represented in a single table. A trigger on this table fires when the stock level of an item falls below a threshold.
The trigger computes the number of items to be ordered and inserts a row into a table which represents an orders queue. The order number is generated from a sequence.
This queue will be consumed periodically by a procedure. This could be automatically scheduled using the Dbms_Job API, but this code sample requires it to be executed manually.
The procedure calls a child procedure for each item to be ordered. The child procedure assembles the message, getting the appropriate XML tags as package constants and getting the vendor URL and password data from a table.
Be sure to edit this to correctly specify the node where you have created the vendor user
Note: A simple version of the child procedure is provided for comparison. It uses only Utl.Http.Request, which was available in Oracle8i. The full version of the child procedure relies on features introduced in Utl.Http in Oracle9i. All calls to the Oracle9i Utl.Http API are bundled in a grandchild procedure
The return message is parsed and the resulting information is used to update the orders queue.
On error, an email is sent automatically. This procedure relies on the Demo_Mail package code sample.
Be sure to edit the Customizable Section for the smtp host and domain for your environment.
Create the customer site schema objects in the correct dependency order.
Vendor site
The vendor implements the URL as a PL/SQL procedure. via mod_plsql and Htp.Print.
Make sure that the basic mechanism is properly configured by compling and testing a simple Hello mod_plsql URL.
This procedure parses the incoming message and updates the vendor orders table. accordingly. It composes a return message getting the appropriate XML tags as package constants.
For the most dramatic effect
Create the vendor site schema objects in the correct dependency order.
Note: The customer message is sent in this code sample as the value in a name-and-value parameter pair using the "GET" method. This works fine for the concrete data provided. A realistic implementation should cater for the possibility that the message to be sent is arbitrarily long, and so would use the "POST" method to send the message in the body of the HTTP request. The Utl_Http API supports this. However, the programming of the procedure which implements the URL would need to be correspondingly more elaborate.
Test the system
First test the sending of email from the database, thus...
connect customer/customer@customer_site
Execute Send_Error_Mail ( 12345, 'This is a test' );
The complete end-to-end test is to update the curr_stock_level in for a row in the customer's stock_levels table so that it falls below threshold_stock_level and triggers the message exchange. Then check the customer_orders and vendor_orders tables.
Use this test script. If you run it immediately after running the scripts to create the customer site and to create the vendor site then you should see [something like] this...
O V SCU Q D S MSG
------- ---- ---- ---- ------------------------ ---------- --------
1234567 1 1 41 12:51:23::08-Nov-2001 submitted
...in the customer_orders table and [something like] this...
ORDER_REF CUSTOMER_ID SCU QUANTITY ORDER_DAT STATUS
--------- ----------- ---------- ---------- --------- ------
1234567 1 1 41 08-NOV-01 new
...in the vendor_orders table.
To test the exception reporting and the automatic sending of email, fabricate an error condition. A simple way to do this is to update the table of vendor data using a URL like...
http://bllewell-sun.us.oracle.com:7777/pls/vendor/Nonexistent
...and then to run the scripts to create the customer site, create the vendor site and run the test. You should now see [something like] this...
O V SCU Q D S MSG
------- ---- ---- ---- ------------------------ ---------- --------------------------------------------
1234567 1 1 41 01:45:11::08-Nov-2001 failed ORA-29268: HTTP client error 404 - Not Found
...in the customer_orders table and you should receive a corresponding email.
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 24-June-2002
Native Compilation of PL/SQL
Overview
PL/ SQL is often used as a thin wrapper for executing SQL statements, setting bind variables and handling result sets. See code sample-1 In such cases the execution speed of the PL/ SQL code is rarely an issue. It is the execution speed of the SQL that determines the performance. (The efficiency of the context switch between the PL/ SQL and the SQL operating environments might be an issue, but that's a different discussion. See the sections on bulk binding and table functions.)
However, we see an increasing trend to use PL/ SQL for computationally intensive database independent tasks. It is after all a fully functional 3GL. See code sample-2 Here it is the execution speed of the PL/ SQL code that determines the performance.
In pre-Oracle9i versions, compilation of PL/ SQL source code always results in a representation (usually referred to bytecode) which is stored in the database and interpreted at run-time by a virtual machine implemented within ORACLE which in turn runs natively on the given platform. Oracle9i introduces a new approach. PL/ SQL source code may optionally be compiled into native object code which is linked into ORACLE. (Note however that an anonymous PL/ SQL block is never compiled natively.)
The sample-2 program runs about 33% faster when compiled in NATIVE mode than when compiled in interpreted mode while the sample-1 program runs about 3% faster when compiled in NATIVE mode. (Each measurement was for about 12 million iterations).
While for data intensive programs native compilation may give only a marginal performance improvement, we have never seen it give performance degradation.
 One-Time DBA Setup
 How does the user choose between interpreted and native compilation modes?
 Upgrading a whole database to native
Business benefits of Native Compilation
 Increased speed and scalability