Changes in This Release for Oracle Database Development Guide

Application Continuity

Application Continuity attempts to mask outages from end users and applications by recovering requests following recoverable outages, unplanned and planned. Application Continuity performs this recovery beneath the application so that the outage appears to the application as a delayed execution.

Application Continuity masks recoverable outages—those in which requests would succeed if they were re-issued. Examples include system failures, network disconnects, foreground failures, and storage failures.

Application Continuity for Java is available with Oracle Database, JDBC Thin driver, and the Oracle Database connection pools: UCP (Universal Connection Pool) and WebLogic Server Active GridLink.

Application Continuity is transparent for Java EE and Java SE applications that use Oracle JDBC, use Oracle Database connection pools (UCP or WLS Active GridLink), and do not have external actions. For applications with external actions (for example, using autonomous transactions or using UTL_HTTP to issue an SOA call), Application Continuity is still transparent if the application's correctness is preserved when these external actions are replayed after a failure.

For details, see Chapter 26, "Ensuring Application Continuity."

Transaction Guard

Before Release 12.1.0.1, it was difficult for a database application to recover after an outage, because the commit message that Oracle Database (the server) returned to the application (the client) was not persistent. If the connection between Oracle Database and the application broke or if the database session became unavailable, then the application received a disconnection error message, but that message did not enable the application to answer these questions:

• Was the in-flight transaction (the transaction that was running when the connection broke) committed?

• If the in-flight transaction included the invocation of a stored subprogram:

  • Did the subprogram end normally, executing all expected commits and session state changes?

  • Was the subprogram aborted?

  • Is the subprogram still running in Oracle Database, disconnected from the application?

The application could try to determine if the in-flight transaction was committed by using exception code to query the outcome at every possible commit point. Each query had to be specific to the transaction whose outcome it sought. This approach was both impractical (especially after the application was in production) and fallible, because:

• The transaction could be committed immediately after the query.

• If the transaction included the invocation of a stored subprogram:

  • Oracle Database could still be running the subprogram, which could commit database changes after the query returned its results to the application.

  • If the subprogram was written in PL/SQL or Java, then the application could not determine if the subprogram had ended normally or aborted.

    An aborted subprogram could have committed database changes without making subsequent session state changes.

After an outage, if an application user resubmitted an in-flight transaction that had been committed, then duplicate transactions resulted.

As of Release 12.1.0.1, the Oracle Database feature Transaction Guard ensures that each transaction executes at most once. Its PL/SQL interface, the DBMS_APP_CONT.GET_LTXID_OUTCOME procedure, enables an application to determine the outcome of the in-flight transaction after an outage and then recover any work that was lost due to the outage. For details, see Chapter 25, "Using Transaction Guard."

Temporal Validity Support

Temporal Validity support in Oracle Database enables you to associate a valid time dimension with a table and to have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid.

Scenarios where Temporal Validity support can be useful include:

• Information Lifecycle Management (ILM) and any other application where it is important to know when certain data became valid (from the application's perspective) and when it became invalid (if ever)

• Data correction where incorrect data must be retained and marked with the period when it was considered valid, and where the correct data must be visible as currently valid

For more information, see Section 1.9.4, "Temporal Validity Support."

Can Grant Roles to PL/SQL Packages and Standalone Subprograms

Before Release 12.1.0.1, a definer's rights (DR) unit always ran with the privileges of the definer and an invoker's rights (IR) unit always ran with the privileges of the invoker. If you wanted to create a PL/SQL unit that all users could invoke, even if their privileges were lower than yours, then it had to be a DR unit. The DR unit always ran with all your privileges, regardless of which user invoked it.

As of Release 12.1.0.1, you can grant roles to individual PL/SQL packages and standalone subprograms. Instead of a DR unit, you can create an IR unit and then grant it roles. The IR unit runs with the privileges of both the invoker and the roles, but without any additional privileges that you have.

For more information, see Section 11.8, "Invoking Stored PL/SQL Subprograms."

SET_NULL_COLUMN_VALUES_TO_EXPR Procedure

Before Release 12.1.0.1, when using edition-based redefinition (EBR), transforming the application data from its pre-upgrade representation (in the old edition) to its post-upgrade representation (in the new edition) required an UPDATE operation on every row—a very expensive and time-consuming operation.

As of Release 12.1.0.1, you can sometimes invoke the procedure DBMS_EDITIONS_UTILITIES.SET_NULL_COLUMN_VALUES_TO_EXPR to use a metadata operation to transform the application data. For more information, see Section 24.3.5, "Transforming Data from Pre- to Post-Upgrade Representation."

Objects, Not Types, Are Editioned or Noneditioned

Before Release 12.1.0.1, a schema object was editionable if its type was editionable in the database and its owner was editions-enabled. An editions-enabled user could not own a noneditioned object of an editionable type.

If a noneditioned object (such as a table) referred to an editioned object (such as a user-defined type in an editions-enabled schema), then an error occurred. The workaround was to create the object to be referenced in a schema that was not editions-enabled.

As of Release 12.1.0.1:

• A schema object is editionable if its type is editionable in the schema that owns the object and the object has the EDITIONABLE property. An editions-enabled user can own a noneditioned object of a type that is editionable in the database if the type is noneditionable in the schema or the object has the NONEDITIONABLE property.

• Ordinarily, if a noneditioned object refers to an editioned object, then the editioned object is invisible during name resolution, and a "no such object" error occurs.

• A noneditioned object that can specify an edition to search for editioned objects during name resolution—an evaluation edition— can depend on editioned objects.

For more information, see Section 24.1.1, "Editioned and Noneditioned Objects."

Implicit Statement Results

Before Release 12.1.0.1, a PL/SQL stored subprogram returned result sets from SQL queries explicitly, through OUT REF CURSOR parameters, and the client program that invoked the subprogram had to bind to those parameters explicitly to receive the result sets.

As of Release 12.1.0.1, a PL/SQL stored subprogram can return query results to its client implicitly, using the PL/SQL package DBMS_SQL instead of OUT REF CURSOR parameters. This technique makes it easy to migrate applications that rely on the implicit return of query results from stored subprograms from third-party databases to Oracle Database. For more information, see Section 11.4.5.3, "Returning Query Results Implicitly."

ACCESSIBLE BY Clause

You might implement a database application as several PL/SQL packages—one package that provides the application programming interface (API) and helper packages to do the work. Ideally, only the API is accessible to clients.

Also, you might create a utility package to provide services to only some other PL/SQL units in the same schema. Ideally, the utility package is accessible only to the intended PL/SQL units.

Before Release 12.1.0.1, PL/SQL could not prevent clients from using items exposed in helper packages. To isolate these items, you had to use relational database management system (RDBMS) security features. Some application deployment schemes made RDBMS security features hard to use.

As of Release 12.1.0.1, each of these statements has an optional ACCESSIBLE BY clause that lets you specify a "white list" of PL/SQL units that can access the PL/SQL unit that you are creating or altering:

• CREATE FUNCTION, described in Oracle Database PL/SQL Language Reference

• CREATE PACKAGE, described in Oracle Database PL/SQL Language Reference

• CREATE PROCEDURE, described in Oracle Database PL/SQL Language Reference

• CREATE TYPE, described in Oracle Database PL/SQL Language Reference

• ALTER TYPE, described in Oracle Database PL/SQL Language Reference

The ACCESSIBLE BY clause supplements the standard Oracle Database security mechanisms. It cannot authorize an otherwise illegal reference.

Changes to the ACCESSIBLE BY clause cause coarse-grained invalidation (see Table 23-2).

PL/SQL Functions and Their Parameters Can Have PL/SQL Data Types

Before Release 12.1.0.1, a SQL expression could not invoke a PL/SQL function that had a formal parameter or return type that was not a SQL data type. There was one exception to this rule: A formal parameter could have a PL/SQL data type if the corresponding actual parameter was implicitly converted to the data type of the formal parameter.

As of Release 12.1.0.1, this restriction is removed. For remaining restrictions, see Section 11.9.3, "When PL/SQL Functions Can Appear in SQL Expressions."

PL/SQL Functions That Run Faster in SQL

As of Release 12.1.0.1, two kinds of PL/SQL functions might run faster in SQL:

• PL/SQL functions that are declared and defined in the WITH clauses of SQL SELECT statements, described in Oracle Database SQL Language Reference

• PL/SQL functions that are defined with the UDF pragma, described in Oracle Database PL/SQL Language Reference.

Oracle ODBC Driver

New and changed features for Release 12.1.0.1 are described in New Features for Oracle ODBC Driver Release 12.1.0.1.0.

Client Auto-Tuning

Client auto-tuning is a feature that transparently optimizes the configuration parameters of OCI client session features of middle-tier applications to gain higher application performance without the need to reprogram your OCI application.

Oracle provides an oraaccess.xml file, a client-side configuration file, that you can use to configure selected OCI parameters as deployment time settings (some of which are accepted programatically in various OCI API calls, such as statement caching and statement prefetch), thereby allowing OCI behavior to be changed during deployment without modifying the source code that calls OCI.

These settings are provided as connect-string-based deployment settings in the client oraaccess.xml file that overrides manual settings of the user configuration of OCI features.

For complete information about client auto-tuning see Section 2.9, "OCI Client Statement Cache Auto-Tuning."

Title

The title of this guide changed from Oracle Database Advanced Application Developer's Guide to Oracle Database Development Guide. This title change reflects an expansion and adjustment of the focus of the guide. For example, the new Part I, "Database Development Fundamentals" introduces important contents and techniques for database developers (both DBAs and database application developers), referring to other chapters and documents for detailed information.

New Chapters

• Chapter 21, "Using the Oracle ODBC Driver," has the same content as the Oracle ODBC Driver help file, except that the chapter has no glossary.

• Chapter 25, "Using Transaction Guard," explains the major new feature Transaction Guard. Transaction Guard ensures transaction idempotence—that is, at-most-once execution of a transaction—and uses the logical transaction ID of the transaction to determine the outcome of the last transaction open.

• Chapter 26, "Ensuring Application Continuity," explains the major new feature Application Continuity. Application Continuity enables the replay, in a transparent and rapid manner, of a transaction against the database after a recoverable error that makes the database session unavailable.

Chapter 25 and Chapter 26 explain how Transaction Guard and Application Continuity are related, and refer to related documentation for implementation details.

Removed Chapter

The chapter "Developing PL/SQL Server Pages (PSP)" was removed, because PSP is obsolete technology. Instead of PSP, use Oracle Application Express.

Moved Appendix

The appendix "Multithreaded extproc Agent" moved to Oracle Call Interface Programmer's Guide, because it is most relevant to the audience of that document (developers of database applications written in C or C++).