|Oracle9i Application Developer's Guide - Fundamentals
Release 2 (9.2)
Part Number A96590-01
The following sections describe the new features for application development in Oracle9i:
You can perform flashback queries using the
AS OF clause of the
SELECT statement rather than going through the
DBMS_FLASHBACK package. This technique is very flexible, allowing you to perform joins, set operations, subqueries, and views using different date/time or SCN settings for each table in the query. You can also restore or capture past data by using flashback queries inside
CREATE TABLE AS SELECT statements.
When you represent related data items using a PL/SQL record, you can perform insert and update operations using the entire record, instead of specifying each record field separately.
To develop using EJBs or CORBA, you should use the J2EE components that are part of Oracle9i Application Server. EJBs and CORBA are no longer supported within the database. You can still access the database from these components, just from a middle-tier application server. You can still write Java stored procedures and Java methods for object types within the database.
If a data management application experiences problems because it tries to create a constraint when the constraint already exists, you can rename the existing constraint to avoid the conflict. If you track down a constraint with a cryptic system-generated name, you can give it a descriptive name to make it easier to enable and disable later.
These globalization-support types can now be used as attributes of SQL and PL/SQL object types, and in PL/SQL collection types such as varrays and nested tables.
New and enhanced built-in types, such as XMLType and XDBURIType, let you delegate XML parsing, storage, and retrieval to the database. Details are in the XML documentation rather than this book.
UTL_FILE package has a number of new functions for performing popular file operations. You can seek, auto-flush, read and write binary data, delete files, change file permissions, and more. You should begin using the
CREATE DIRECTORY statement (using double quotation marks around any lowercase names), rather than the
UTL_FILE_DIR initialization parameter.
Oracle9i Supplied PL/SQL Packages and Types Reference for details about these enhancements.
You can now override the system default constructor for an object type with your own function.
You can access or change LOB data within
INSTEAD OF triggers, using the
You can now define synonyms for types.
Scrollable cursors let you move forward and backward through the result set in a Pro*C/C++ application.
The Connection Pool feature in Pro*C/C++ helps you optimise the performance of Pro*C/C++ applications.
Many of the cross-references from this book to other books have been made more specific, so that they link to a particular place within another book rather than to the table of contents. Because this is an ongoing project, not all links are improved in this edition. If you are reading a printed copy of this book, you can find the online equivalent at
http://tahiti.oracle.com/, with full search capability.
PL/SQL now supports the complete range of syntax for SQL statements, such as
DELETE, and so on. If you received errors for valid SQL syntax in PL/SQL programs before, those statements should now work.
Because of more consistent error-checking, you might find that some invalid code is now found at compile time instead of producing an error at runtime, or vice versa. You might need to change the source code as part of the migration procedure. See Oracle9i Database Migration for details on the complete migration procedure.
When an application encounters some kinds of storage allocation errors, it can suspend operations and take action such as resolving the problem or notifying an operator. The operation can be resumed when storage is added or freed.
Table data can be queried as it existed at a point in time. This lets applications query, compare, or recover past data without involving the DBA and without an expensive recovery operation. The current table data remains available to other applications throughout.
Rather than repeat a complex subquery, you can give it a name and refer to that name multiple times within the same query. This is convenient for coding, and helps the optimizer find common code that can be optimized.
The new datatype
TIMESTAMP records time values including fractional seconds. New datatypes
TIMESTAMP WITH TIME ZONE and
TIMESTAMP WITH LOCAL TIME ZONE allow you to adjust date and time values to account for time zone differences. You can specify whether the time zone observes daylight savings time, to account for anomalies when clocks shift forward or backward. New datatypes
INTERVAL DAY TO SECOND and
INTERVAL YEAR TO MONTH represent differences between two date and time values, simplifying date arithmetic.
You can operate on LOB types much like other similar types. You can use character functions on
NCLOB types. You can treat
BLOB types as
RAWs. Conversions between LOBs and other types are much simpler, particularly when converting from
LONG to LOB types.
Data can be stored in Unicode format using fixed-width or variable-width character sets. String handling and storage declarations can be specified using byte lengths, or character lengths where the number of bytes is computed for you. You can set up the entire database to use the same length semantics for strings, or specify the settings for individual procedures; this setting is remembered if a procedure is invalidated.
You can now perform bulk SQL operations, such as bulk fetches, using native dynamic SQL (the
EXECUTE IMMEDIATE statement). You can perform bulk insert or update operations that continue despite errors on some rows, then examine the problems after the operation is complete.
UTL_SMTP packages have a number of enhancements, such as letting you access password-protected web pages, sending e-mail with attachments, and so on.
Improve performance by compiling Oracle-supplied and user-written stored procedures into native executables, using typical C development tools. This setting is saved so that the procedure is compiled the same way if it is later invalidated.
The OCCI API lets you write fast, low-level database applications using C++. It is similar to the existing Oracle Call Interface (OCI) API.
In Oracle9i, application developers no longer need to secure a role by embedding passwords inside applications. They can create application roles and specify which PL/SQL package is authorized to enable the roles. These application roles, those enabled by PL/SQL packages, are called secure application roles.
You can create an application context by entering a command like:
Alternatively, you can use Oracle Policy Manager to create an application context.
You can run external procedure agents (the
EXTPROC entry in
tnsnames.ora) under different instances of Oracle or on entirely separate machines. This lets you configure external procedures more robustly, so that if one external procedure crashes, other external procedures can continue running in a different agent process.