PL/SQL User's Guide and Reference Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

New Features

This appendix surveys the new features in release 2.3 of PL/SQL. Designed to meet your practical needs, these features will help you build effective, reliable applications.

Support for File I/O

The new package UTL_FILE, which is supplied with the Oracle Server, allows your PL/SQL programs to read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.

When you want to read or write a text file, you call the function fopen, which returns a file handle for use in subsequent procedure calls. For example, the procedure put_line writes a text string and line terminator to an open file. The procedure get_line reads a line of text from an open file into an output buffer.

PL/SQL file I/O is available on both the client and server sides. However, on the server side, file access is restricted to those directories explicitly listed in the accessible directories list, which is part of the Oracle initialization file.

For more information, see Oracle7 Server Application Developer's Guide.

PL/SQL Table Improvements

Now, you can declare PL/SQL tables of records as well as PL/SQL tables of scalars. That means a PL/SQL table can store rows (not just a column) of Oracle data. PL/SQL tables of records make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms. You can even use PL/SQL tables of records to simulate local database tables.

Also, several new attributes give you previously unavailable information about a PL/SQL table. Attributes are characteristics of an object. Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain.

For example, COUNT returns the number of elements that a PL/SQL table contains. COUNT is useful because the size of a PL/SQL table is unconstrained. Suppose you fetch a column of Oracle data into a PL/SQL table. How many elements does the PL/SQL table contain? COUNT gives you the answer.

To apply the attributes to a PL/SQL table, you use dot notation, as the following example shows:

IF ename_tab.COUNT = 50 THEN ...

For more information, see "PL/SQL Tables" [*].

Cursor Variable Improvements

Now, cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as a bind variable to PL/SQL. Moreover, application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side.

The Oracle Server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server via remote procedure calls (RPCs). And, if you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side.

Furthermore, now you can define weak (nonrestrictive) REF CURSOR types. As the following example shows, a strong REF CURSOR type definition specifies a return type, but a weak definition does not:

   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong
   TYPE GenericCurTyp IS REF CURSOR;  -- weak 

Weak REF CURSOR types are more flexible because PL/SQL lets you associate a weakly typed cursor variable with any query.

Also, now you can apply the cursor attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT to a cursor variable. They return useful information about the execution of a multi-row query.

For more information, see "Using Cursor Variables" [*].

New Fast-Integer Datatype

You can use the new datatype PLS_INTEGER to boost performance. Like BINARY_INTEGER values, PLS_INTEGER values require less storage than NUMBER values. But, PLS_INTEGER operations use machine arithmetic, so they are considerably faster than BINARY_ INTEGER operations, which use library arithmetic.

For more information, see "PLS_INTEGER" [*].

Full Support for Subqueries

Formerly, PL/SQL allowed subqueries only in the SET, VALUES, and WHERE clauses. Now, PL/SQL also allows subqueries in the FROM clause. Among other things, this adds flexibility to your cursor definitions.

For an example, see "Using Subqueries" [*].

New Remote Dependency Mode

Formerly, Oracle used only timestamps to manage remote dependencies among PL/SQL library units (packages and stored subprograms). Whenever a library unit was recompiled, the server timestamped it. At run time, dependent subprograms on a client system or on another server were invalidated because their timestamps were no longer current. Often, the resulting recompilations were needless because the specification of the library unit had not been altered.

Needless recompilations can slow network traffic and affect performance. Furthermore, if a client system has no PL/SQL compiler, invalidated applications cannot be recompiled.

Now, Oracle can use timestamps or signatures to manage remote dependencies. (The signature of a subprogram includes its name and the number, datatypes, and modes of its parameters.) When Oracle uses the signature of a remote library unit, dependent subprograms are invalidated only if the signature or specification of the unit was altered. So, dependent subprograms are recompiled only when necessary.

To have Oracle use signatures instead of timestamps, you set the following parameter in the Oracle initialization file:


You can reset the parameter dynamically, as the following Pro*C example shows:


For more information, see Oracle7 Server Application Developer's Guide.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index