24 SQL, PL/SQL, and Java

DML Error Logging

When a DML statement encounters an error, the statement can continue processing while the error code and the associated error message text is logged to an error logging table. This is particularly helpful to long-running, bulk DML statements. After the DML operation completes, you can check the error logging table to correct rows with errors.

New syntax is added to the DML statements to provide the name of the error logging table, a statement tag, and a reject limit. The reject limit determines whether the statement should be aborted. For parallel DML operations, the reject limit is applied for each slave. The only values for the reject limit that are precisely enforced on parallel operations are zero and unlimited.

With data conversion errors, Oracle tries to provide a meaningful value to log for the column. For example, it could log the value of the first operand to the conversion operator that failed. If a value cannot be derived, then NULL is logged for the column.

Stage 1: Create a Cursor

A program interface call creates a cursor. The cursor is created independent of any SQL statement: it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can either occur implicitly or be explicitly declared.

Stage 2: Parse the Statement

During parsing, the SQL statement is passed from the user process to Oracle, and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this stage of statement processing.

Parsing is the process of:

• Translating a SQL statement, verifying it to be a valid statement

• Performing data dictionary lookups to check table and column definitions

• Acquiring parse locks on required objects so that their definitions do not change during the statement's parsing (however, parse locks can be broken to allow conflicting DDL operations)

• Checking privileges to access referenced schema objects

• Determining the optimal execution plan for the statement

• Loading it into a shared SQL area

• Routing all or part of distributed statements to remote nodes that contain referenced data

Oracle parses a SQL statement only if a shared SQL area for an similar SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated, and the statement is parsed.

The parse stage includes processing requirements that need to be done only once no matter how many times the statement is run. Oracle translates each SQL statement only once, rerunning that parsed statement during subsequent references to the statement.

Although parsing a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, some errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can be encountered and reported only during the execution stage.

Query Processing

Queries are different from other types of SQL statements because, if successful, they return data as results. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.

Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries (subqueries) in other SQL statements. For example, each of the following statements requires a query as a part of its execution:

INSERT INTO table SELECT... 

UPDATE table SET x = y WHERE... 

DELETE FROM table WHERE... 

CREATE table AS SELECT... 

In particular, queries:

• Require read consistency

• Can use temporary segments for intermediate processing

• Can require the describe, define, and fetch stages of SQL statement processing.

Stage 3: Describe Results of a Query

The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.

Stage 4: Define Output of a Query

In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary. (See DEFINE on Figure 24-1, "The Stages in Processing a SQL Statement".)

Stage 5: Bind Any Variables

At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to run the statement. Oracle needs values for any variables listed in the statement; in the example, Oracle needs a value for department_id. The process of obtaining these values is called binding variables.

A program must specify the location (memory address) where the value can be found. End users of applications may be unaware that they are specifying bind variables, because the Oracle utility can simply prompt them for a new value.

Because you specify the location (binding by reference), you need not rebind the variable before reexecution. You can change its value and Oracle looks up the value on each execution, using the memory address.

You must also specify a datatype and length for each value (unless they are implied or defaulted) if Oracle needs to perform datatype conversion.

Stage 6: Parallelize the Statement

Oracle can parallelize queries (SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs), and some DDL operations such as index creation, creating a table with a subquery, and operations on partitions. Parallelization causes multiple server processes to perform the work of the SQL statement so it can complete faster.

Stage 7: Run the Statement

At this point, Oracle has all necessary information and resources, so the statement is run. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database until the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction. This ensures data integrity.

For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.

Stage 8: Fetch Rows of a Query

In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result until the last row has been fetched.

Stage 9: Close the Cursor

The final stage of processing a SQL statement is closing the cursor.

Stored Outlines

Stored outlines are abstractions of an execution plan generated by the optimizer at the time the outline was created and are represented primarily as a set of hints. When the outline is subsequently used, these hints are applied at various stages of compilation. Outline data is stored in the OUTLN schema. You can tune execution plans by editing stored outlines.

Editing Stored Outlines

The outline is cloned into the user's schema at the onset of the outline editing session. All subsequent editing operations are performed on that clone until the user is satisfied with the edits and chooses to publicize them. In this way, any editing done by the user does not impact the rest of the user community, which would continue to use the public version of the outline until the edits are explicitly saved.

Interpreted Execution

In versions earlier than Oracle9i, PL/SQL source code was always compiled into a so-called bytecode representation, which is run by a portable virtual computer implemented as part of the Oracle database server, and also in products such as Oracle Forms. Starting with Oracle9i, you can choose between native execution and interpreted execution

Native Execution

For best performance on computationally intensive program units, compile the source code of PL/SQL program units stored in the database directly to object code for the given platform. (This object code is linked into the Oracle database server.)

The PL/SQL engine is the tool you use to define, compile, and run PL/SQL program units. This engine is a special component of many Oracle products, including the Oracle database server.

While many Oracle products have PL/SQL components, this section specifically covers the program units that can be stored in an Oracle database and processed using the Oracle database server PL/SQL engine. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool's documentation.

Figure 24-2 illustrates the PL/SQL engine contained in Oracle database server.

Figure 24-2 The PL/SQL Engine and the Oracle Database Server

Description of "Figure 24-2 The PL/SQL Engine and the Oracle Database Server"

The program unit is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled program unit into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure.

The following Oracle products contain a PL/SQL engine:

• Oracle database server

• Oracle Forms (version 3 and later)

• SQL*Menu (version 5 and later)

• Oracle Reports (version 2 and later)

• Oracle Graphics (version 2 and later)

You can call a stored procedure from another PL/SQL block, which can be either an anonymous block or another stored procedure. For example, you can call a stored procedure from Oracle Forms (version 3 or later).

Also, you can pass anonymous blocks to Oracle from applications developed with these tools:

• Oracle precompilers (including user exits)

• Oracle Call Interfaces (OCIs)

• SQL*Plus

• Oracle Enterprise Manager

Variables and Constants

Variables and constants can be declared within a procedure, function, or package. A variable or constant can be used in a SQL or PL/SQL statement to capture or provide a value when one is needed.

Some interactive tools, such as SQL*Plus, let you define variables in your current session. You can use such variables just as you would variables declared within procedures or packages.

Cursors

Cursors can be declared explicitly within a procedure, function, or package to facilitate record-oriented processing of Oracle data. Cursors also can be declared implicitly (to support other data manipulation actions) by the PL/SQL engine.

Exceptions

PL/SQL lets you explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code. Internal exceptions are caused by illegal operations, such as division by zero, or Oracle errors returned to the PL/SQL code. User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application (for example, debiting an account and leaving a negative balance).

When an exception is raised, the execution of the PL/SQL code stops, and a routine called an exception handler is invoked. Specific exception handlers can be written for any internal or user-defined exception.

Dynamic SQL in PL/SQL

PL/SQL can run dynamic SQL statements whose complete text is not known until runtime. Dynamic SQL statements are stored in character strings that are entered into, or built by, the program at runtime. This enables you to create general purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

You can write stored procedures and anonymous PL/SQL blocks that include dynamic SQL in two ways:

• By embedding dynamic SQL statements in the PL/SQL block

• By using the DBMS_SQL package

Additionally, you can issue DML or DDL statements using dynamic SQL. This helps solve the problem of not being able to statically embed DDL statements in PL/SQL. For example, you can choose to issue a DROP TABLE statement from within a stored procedure by using the EXECUTE IMMEDIATE statement or the PARSE procedure supplied with the DBMS_SQL package.

Benefits of Procedures

Stored procedures provide advantages in the following areas:

• Security with definer's rights procedures

  Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that run with the definer's privileges.

  For example, you can grant users access to a procedure that updates a table but not grant them access to the table itself. When a user invokes the procedure, the procedure runs with the privileges of the procedure's owner. Users who have only the privilege to run the procedure (but not the privileges to query, update, or delete from the underlying tables) can invoke the procedure, but they cannot manipulate table data in any other way.

  See Also:

  "Dependency Tracking for Stored Procedures"

• Inherited privileges and schema context with invoker's rights procedures

  An invoker's rights procedure inherits privileges and schema context from the procedure that calls it. In other words, an invoker's rights procedure is not tied to a particular user or schema, and each invocation of an invoker's rights procedure operates in the current user's schema with the current user's privileges. Invoker's rights procedures make it easy for application developers to centralize application logic, even when the underlying data is divided among user schemas.

  For example, a user who runs an update procedure on the employees table as a manager can update salary, whereas a user who runs the same procedure as a clerk can be restricted to updating address data.

• Improved performance

  • The amount of information that must be sent over a network is small compared with issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle, because the information is sent only once and thereafter invoked when it is used.

  • A procedure's compiled form is readily available in the database, so no compilation is required at execution time.

  • If the procedure is already present in the shared pool of the system global area (SGA), then retrieval from disk is not required, and execution can begin immediately.

• Memory allocation

  Because stored procedures take advantage the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users. Sharing the same code among many users results in a substantial reduction in Oracle memory requirements for applications.

• Improved productivity

  Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.

  For example, procedures can be written to insert, update, or delete employee records from the employees table. These procedures can then be called by any application without rewriting the SQL statements necessary to accomplish these tasks. If the methods of data management change, only the procedures need to be modified, not all of the applications that use the procedures.

• Integrity

  Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.

  For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in any way, then only the procedure needs to be recompiled. Applications that call the procedure do not necessarily require any modifications.

Procedure Guidelines

Use the following guidelines when designing stored procedures:

• Define procedures to complete a single, focused task. Do not define long procedures with several distinct subtasks, because subtasks common to many procedures can be duplicated unnecessarily in the code of several procedures.

• Do not define procedures that duplicate the functionality already provided by other features of Oracle. For example, do not define procedures to enforce simple data integrity rules that you could easily enforce using declarative integrity constraints.

Anonymous PL/SQL Blocks Compared with Stored Procedures

A stored procedure is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be run without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.

As an alternative to a stored procedure, you can create an anonymous PL/SQL block by sending an unnamed PL/SQL block to the Oracle database server from an Oracle tool or an application. Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but it does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool.

In either case, moving PL/SQL blocks out of a database application and into database procedures stored either in the database or in memory, you avoid unnecessary procedure recompilations by Oracle at runtime, improving the overall performance of the application and Oracle.

Standalone Procedures

Stored procedures not defined within the context of a package are called standalone procedures. Procedures defined within a package are considered a part of the package.

Dependency Tracking for Stored Procedures

A stored procedure depends on the objects referenced in its body. Oracle automatically tracks and manages such dependencies. For example, if you alter the definition of a table referenced by a procedure, then the procedure must be recompiled to validate that it will still work as designed. Usually, Oracle automatically administers such dependency management.

External Procedures

A PL/SQL procedure executing on an Oracle database server can call an external procedure or function that is written in the C programming language and stored in a shared library. The C routine runs in a separate address space from that of the Oracle database server.

Table Functions

Table functions are functions that can produce a set of rows as output. In other words, table functions return a collection type instance (nested table and VARRAY datatypes). You can use a table function in place of a regular table in the FROM clause of a SQL statement.

Oracle allows table functions to pipeline results (act like an Oracle rowsource) out of the functions. This can be achieved by either providing an implementation of the ODCITable interface, or using native PL/SQL instructions.

Pipelining helps to improve the performance of a number of applications, such as Oracle Warehouse Builder (OWB) and cartridges groups.

The ETL (Extraction-Transformation-Load) process in data warehouse building extracts data from an OLTP system. The extracted data passes through a sequence of transformations (written in procedural languages, such as PL/SQL) before it is loaded into a data warehouse.

Oracle also allows parallel execution of table and non-table functions. Parallel execution provides the following extensions:

• Functions can directly accept a set of rows corresponding to a subquery operand.

• A set of input rows can be partitioned among multiple instances of a parallel function. The function developer specifies how the input rows should be partitioned between parallel instances of the function.

Thus, table functions are similar to views. However, instead of defining the transform declaratively in SQL, you define it procedurally in PL/SQL. This is especially valuable for the arbitrarily complex transformations typically required in ETL.

Benefits of Packages

Packages provide advantages in the following areas:

• Encapsulation of related procedures and variables

  Stored packages allow you to encapsulate or group stored procedures, variables, datatypes, and so on in a single named, stored unit in the database. This provides better organization during the development process. Encapsulation of procedural constructs also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.

• Declaration of public and private procedures, variables, constants, and cursors

  The methods of package definition allow you to specify which variables, cursors, and procedures are public and private. Public means that it is directly accessible to the user of a package. Private means that it is hidden from the user of a package.

  For example, a package can contain 10 procedures. You can define the package so that only three procedures are public and therefore available for execution by a user of the package. The remainder of the procedures are private and can only be accessed by the procedures within the package. Do not confuse public and private package variables with grants to PUBLIC.

  See Also:

  Chapter 20, "Database Security" for more information about grants to PUBLIC

• Better performance

  An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to run the compiled code already in memory.

  A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always through the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.

Collections

A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.

Collections work like the arrays found in most third-generation programming languages. Also, collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

Records

You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, with a user-defined record, you can declare fields of your own.

Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit.

Classes

All object-oriented programming languages support the concept of a class. As with a table definition, a class provides a template for objects that share common characteristics. Each class can contain the following:

• Attributes—static or instance variables that each object of a particular class possesses

• Methods—you can invoke methods defined by the class or inherited by any classes extended from the class

When you create an object from a class, you are creating an instance of that class. The instance contains the fields of an object, which are known as its data, or state.

Figure 24-5 shows an example of an Employee class defined with two attributes: last name (lastName) and employee identifier (ID).

Figure 24-5 Classes and Instances

Description of "Figure 24-5 Classes and Instances"

When you create an instance, the attributes store individual and private information relevant only to the employee. That is, the information contained within an employee instance is known only for that single employee. The example in Figure 24-5 shows two instances of employee—Smith and Jones. Each instance contains information relevant to the individual employee.

Attributes

Attributes within an instance are known as fields. Instance fields are analogous to the fields of a relational table row. The class defines the fields, as well as the type of each field. You can declare fields in Java to be static, public, private, protected, or default access.

• Public, private, protected, or default access fields are created within each instance.

• Static fields are like global variables in that the information is available to all instances of the employee class.

The language specification defines the rules of visibility of data for all fields. Rules of visibility define under what circumstances you can access the data in these fields.

Methods

The class also defines the methods you can invoke on an instance of that class. Methods are written in Java and define the behavior of an object. This bundling of state and behavior is the essence of encapsulation, which is a feature of all object-oriented programming languages. If you define an Employee class, declaring that each employee's id is a private field, other objects can access that private field only if a method returns the field. In this example, an object could retrieve the employee's identifier by invoking the Employee.getId method.

In addition, with encapsulation, you can declare that the Employee.getId method is private, or you can decide not to write an Employee.getId method. Encapsulation helps you write programs that are reusable and not misused. Encapsulation makes public only those features of an object that are declared public; all other fields and methods are private. Private fields and methods can be used for internal object processing.

Multithreading

Multithreading support is often cited as one of the key scalability features of Java. Certainly, the Java language and class libraries make it simpler to write shared server applications in Java than many other languages, but it is still a daunting task in any language to write reliable, scalable shared server code.

As a database server, Oracle efficiently schedules work for thousands of users. The Oracle JVM uses the facilities of the RDBMS server to concurrently schedule Java execution for thousands of users. Although Oracle supports Java language level threads required by the JLS and JCK, using threads within the scope of the database does not increase scalability. Using the embedded scalability of the database eliminates the need for writing shared server Java servers. You should use the database's facilities for scheduling users by writing single-threaded Java applications. The database takes care of the scheduling between each application; thus, you achieve scalability without having to manage threads. You can still write shared server Java applications, but multiple Java threads does not increase your server's performance.

One difficulty multithreading imposes on Java is the interaction of threads and automated storage management, or garbage collection. The garbage collector executing in a generic JVM has no knowledge of which Java language threads are executing or how the underlying operating system schedules them.

• Non-Oracle model—A single user maps to a single Java language level thread; the same single garbage collector manages all garbage from all users. Different techniques typically deal with allocation and collection of objects of varying lifetimes and sizes. The result in a heavily shared server application is, at best, dependent upon operating system support for native threads, which can be unreliable and limited in scalability. High levels of scalability for such implementations have not been convincingly demonstrated.

• Oracle JVM model—Even when thousands of users connect to the server and run the same Java code, each user experiences it as if he is executing his own Java code on his own Java Virtual Machine. The responsibility of the Oracle JVM is to make use of operating system processes and threads, using the scalable approach of the Oracle RDBMS. As a result of this approach, the JVM's garbage collector is more reliable and efficient because it never collects garbage from more than one user at any time.

Automated Storage Management

Garbage collection is a major feature of Java's automated storage management, eliminating the need for Java developers to allocate and free memory explicitly. Consequently, this eliminates a large source of memory leaks that commonly plague C and C++ programs. There is a price for such a benefit: garbage collection contributes to the overhead of program execution speed and footprint. Although many papers have been written qualifying and quantifying the trade-off, the overall cost is reasonable, considering the alternatives.

Garbage collection imposes a challenge to the JVM developer seeking to supply a highly scalable and fast Java platform. The Oracle JVM meets these challenges in the following ways:

• The Oracle JVM uses the Oracle scheduling facilities, which can manage multiple users efficiently.

• Garbage collection is performs consistently for multiple users because garbage collection is focused on a single user within a single session. The Oracle JVM enjoys a huge advantage because the burden and complexity of the memory manager's job does not increase as the number of users increases. The memory manager performs the allocation and collection of objects within a single session—which typically translates to the activity of a single user.

• The Oracle JVM uses different garbage collection techniques depending on the type of memory used. These techniques provide high efficiency and low overhead.

Footprint

The footprint of an executing Java program is affected by many factors:

• Size of the program itself—how many classes and methods and how much code they contain.

• Complexity of the program—the amount of core class libraries that the Oracle JVM uses as the program runs, as opposed to the program itself.

• Amount of state the JVM uses—how many objects the JVM allocates, how large they are, and how many must be retained across calls.

• Ability of the garbage collector and memory manager to deal with the demands of the executing program, which is often non-deterministic. The speed with which objects are allocated and the way they are held on to by other objects influences the importance of this factor.

From a scalability perspective, the key to supporting many concurrent clients is a minimum user session footprint. The Oracle JVM keeps the user session footprint to a minimum by placing all read-only data for users, such as Java bytecodes, in shared memory. Appropriate garbage collection algorithms are applied against call and session memories to maintain a small footprint for the user's session. The Oracle JVM uses three types of garbage collection algorithms to maintain the user's session memory:

• Generational scavenging for short-lived objects

• Mark and lazy sweep collection for objects that exist for the life of a single call

• Copying collector for long-lived objects—objects that live across calls within a session

Performance

Oracle JVM performance is enhanced by implementing a native compiler. Java runs platform-independent bytecodes on top of a JVM, which in turn interacts with the specific hardware platform. Any time you add levels within software, your performance is degraded. Because Java requires going through an intermediary to interpret platform-independent bytecodes, a degree of inefficiency exists for Java applications that does not exists within a platform-dependent language, such as C. To address this issue, several JVM suppliers create native compilers. Native compilers translate Java bytecodes into platform-dependent native code, which eliminates the interpreter step and improves performance.

The following table describes two methods for native compilation.

Oracle uses static compilation to deliver its core Java class libraries: the ORB and JDBC code in natively compiled form. It is applicable across all the platforms Oracle supports, whereas a JIT approach requires low-level, processor-dependent code to be written and maintained for each platform. You can use this native compilation technology with your own Java code.

Dynamic Class Loading

Another strong feature of Java is dynamic class loading. The class loader loads classes from the disk (and places them in the JVM-specific memory structures necessary for interpretation) only as they are used during program execution. The class loader locates the classes in the CLASSPATH and loads them during program execution. This approach, which works well for applets, poses the following problems in a server environment:

Java Stored Procedures

A Java stored procedure is a program you write in Java to run in the server, exactly as a PL/SQL stored procedure. You invoke it directly with products like SQL*Plus, or indirectly with a trigger. You can access it from any Oracle Net client—OCI, precompiler, or JDBC.

In addition, you can use Java to develop powerful programs independently of PL/SQL. Oracle provides a fully-compliant implementation of the Java programming language and JVM.

PL/SQL Integration and Oracle Functionality

You can invoke existing PL/SQL programs from Java and invoke Java programs from PL/SQL. This solution protects and leverages your existing investment while opening up the advantages and opportunities of Java-based Internet computing.

JDBC

Java database connectivity (JDBC) is an application programming interface (API) for Java developers to access SQL data. It is available on client and server, so you can deploy the same code in either place.

Oracle's JDBC allows access to objects and collection types defined in the database from Java programs through dynamic SQL. Dynamic SQL means that the embedded SQL statement to be run is not known before the application is run, and requires input to build the statement. It provides for translation of types defined in the database into Java classes through default or customizable mappings, and it also enables you to monitor, trace, and correlate resource consumption of Java and J2EE applications down to the database operation level.

Core Java class libraries provide only one JDBC API. JDBC is designed, however, to allow vendors to supply drivers that offer the necessary specialization for a particular database. Oracle delivers the following three distinct JDBC drivers.

SQLJ

SQLJ allows developers to use object datatypes in Java programs. Developers can use JPublisher to map Oracle object and collection types into Java classes to be used in the application.

SQLJ provides access to server objects using SQL statements embedded in the Java code. SQLJ provides compile-time type checking of object types and collections in the SQL statements. The syntax is based on an ANSI standard (SQLJ Consortium).

You can specify Java classes as SQL user-defined object types. You can define columns or rows of this SQLJ type. You can also query and manipulate the objects of this type as if they were SQL primitive types. Additionally, you can do the following:

• Make the static fields of a class visible in SQL

• Allow the user to call a Java constructor

• Maintain the dependency between the Java class and its corresponding type

JPublisher

Java Publisher (JPublisher) is a utility, written entirely in Java, that generates Java classes to represent the following user-defined database entities in your Java program:

• SQL object types

• Object reference types ("REF types")

• SQL collection types (VARRAY types or nested table types)

• PL/SQL packages

JPublisher lets you to specify and customize the mapping of these entities to Java classes in a strongly typed paradigm.

Java Messaging Service

Java Messaging Service (JMS) is a messaging standard developed by Sun Microsystems along with Oracle, IBM, and other vendors. It defines a set of interfaces for JMS applications and specifies the behavior implemented by JMS providers. JMS provides a standard-based API to enable asynchronous exchange of business events within the enterprise, as well as with customers and partners. JMS facilitates reliable communication between loosely coupled components in a distributed environment, significantly simplifying the effort required for enterprise integration. The combination of Java technology with enterprise messaging enables development of portable applications.

Oracle Java Messaging Service is a Java API for Oracle Streams, based on the JMS standard. Multiple client applications can send and receive messages of any type through a central JMS provider (Oracle Streams). The JMS client consists of the Java application as well as a messaging client runtime library that implements the JMS interface and communicates with Oracle Streams.

Java Messaging Oracle JMS supports the standard JMS interfaces and has extensions to support other Streams features that are not a part of the standard. It can be used to enqueue and dequeue messages in the queue available with Oracle Streams. Oracle JMS includes the standard JMS features:

• Point-to-point communication using queues

• Publish-subscribe communication using topics

• Synchronous and asynchronous message exchange

• Subject-based routing

Oracle Streams also provides extensions to the standard JMS features:

• Point-to-multipoint communication using a recipient list for specifying the applications to receive the messages

• Administrative API to create the queue tables, queues and subjects

• Automatic propagation of messages between queues on different databases, enabling the application to define remote subscribers

• Transacted session support, allowing both JMS and SQL operations in one transaction

• Message retention after message is consumed

• Exception handling

• Delay specification before a message is visible