This chapter contains these topics:
As an application developer, you have many choices when writing a program to interact with an Oracle database.
Client/Server Model
In a traditional client/server program, the code of your application runs on a machine other than the database server. Database calls are transmitted from this client machine to the database server. Data is transmitted from the client to the server for insert and update operations and returned from the server to the client for query operations. The data is processed on the client machine. Client/server programs are typically written by using precompilers, whereas SQL statements are embedded within the code of another language such as C, C++, or COBOL.
Server-Side Coding
You can develop application logic that resides entirely inside the database by using triggers that are executed automatically when changes occur in the database or stored procedures that are called explicitly. Off-loading the work from your application lets you reuse code that performs verification and cleanup and control database operations from a variety of clients. For example, by making stored procedures callable through a Web server, you can construct a Web-based user interface that performs the same functions as a client/server application.
Two-Tier Versus Three-Tier Models
Client/server computing is often referred to as a two-tier model: your application communicates directly with the database server. In the three-tier model, a separate application server processes the requests. The application server might be a basic Web server, or might perform advanced functions like caching and load-balancing. Increasing the processing power of this middle tier lets you lessen the resources needed by client systems, resulting in a thin client configuration in which the client machine might need only a Web browser or other means of sending requests over the TCP/IP or HTTP protocols.
User Interface
The interface that your application displays to end users depends on the technology behind the application as well as the needs of the users themselves. Experienced users can enter SQL commands that are passed on to the database. Novice users can be shown a graphical user interface that uses the graphics libraries of the client system (such as Windows or X-Windows). Any of these traditional user interfaces can also be provided in a Web browser by means of HTML and Java.
Stateful Versus Stateless User Interfaces
In traditional client/server applications, the application can keep a record of user actions and use this information over the course of one or more sessions. For example, past choices can be presented in a menu so that they do not have to be entered again. When the application is able to save information in this way, the application is considered stateful.
Web or thin-client applications that are stateless are easier to develop. Stateless applications gather all the required information, process it using the database, and then start over with the next user. This is a popular way to process single-screen requests such as customer registration.
There are many ways to add stateful behavior to Web applications that are stateless by default. For example, an entry form on one Web page can pass information to subsequent Web pages, allowing you to construct a wizard-like interface that remembers the user's choices through several different steps. Cookies can be used to store small items of information on the client machine, and retrieve them when the user returns to a Web site. Servlets can be used to keep a database session open and store variables between requests from the same client.
This section contains the following topics:
PL/SQL is Oracle's procedural extension to SQL, the standard database access language. It is an advanced 4GL (fourth-generation programming language), which means that it is an application-specific language. PL/SQL and SQL have built-in treatment of the relational database domain.
In PL/SQL, you can manipulate data with SQL statements and control program flow with procedural constructs such as loops. You can also do the following:
Declare constants and variables
Define procedures and functions
Use collections and object types
Trap runtime errors
Applications written in any of the Oracle programmatic interfaces can call PL/SQL stored procedures and send blocks of PL/SQL code to Oracle Database for execution. 3GL applications can access PL/SQL scalar and composite datatypes through host variables and implicit datatype conversion. A 3GL language is easier than assembler language for a human to understand and includes features such as named variables. Unlike 4GL, it is not specific to an application domain.
Example 1-1 provides an example of a simple PL/SQL procedure. The procedure debit_account
withdraws money from a bank account. It accepts an account number and an amount of money as parameters. It uses the account number to retrieve the account balance from the database, then computes the new balance. If this new balance is less than zero, then the procedure jumps to an error routine; otherwise, it updates the bank account.
Example 1-1 Simple PL/SQL Example
PROCEDURE debit_account (p_acct_id INTEGER, p_debit_amount REAL) IS v_old_balance REAL; v_new_balance REAL; e_overdrawn EXCEPTION; BEGIN SELECT bal INTO v_old_balance FROM accts WHERE acct_no = p_acct_id; v_new_balance := v_old_balance - p_debit_amount; IF v_new_balance < 0 THEN RAISE e_overdrawn; ELSE UPDATE accts SET bal = v_new_balance WHERE acct_no = p_acct_id; END IF; COMMIT; EXCEPTION WHEN e_overdrawn THEN -- handle the error END debit_account;
PL/SQL is a portable, high-performance transaction processing language with the following advantages:
PL/SQL enables you use all of the Oracle Database SQL data manipulation, cursor control, and transaction control statements. PL/SQL also supports the SQL functions, operators, and pseudocolumns. So, you can manipulate data in Oracle Database flexibly and safely.
PL/SQL supports all SQL datatypes. Combined with the direct access that SQL provides, these shared datatypes integrate PL/SQL with the Oracle Database data dictionary.
PL/SQL supports Dynamic SQL, which is a programming technique that enables you to build and process SQL statements "on the fly" at run time. It gives PL/SQL flexibility comparable to scripting languages such as Perl, Korn shell, and Tcl.
The %TYPE
and %ROWTYPE
attributes enable your code to adapt as table definitions change. For example, the %TYPE
attribute declares a variable based on the type of a database column. If the column datatype changes, then the variable uses the correct type at runtime. This provides data independence and reduces maintenance costs.
If your application is database intensive, then you can use PL/SQL blocks to group SQL statements before sending them to Oracle Database for execution. This coding strategy can drastically reduce the communication overhead between your application and Oracle Database.
PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. A single call can start a compute-intensive stored procedure, reducing network traffic and improving round-trip response times. Executable code is automatically cached and shared among users, lowering memory requirements and invocation overhead.
PL/SQL adds procedural capabilities such as Oracle Forms and Oracle Reports. For example, you can use an entire PL/SQL block in an Oracle Forms trigger instead of multiple trigger steps, macros, or user exits.
PL/SQL is the same in all environments. As soon as you master PL/SQL with one Oracle tool, you can transfer your knowledge to others, and so multiply the productivity gains. For example, scripts written with one tool can be used by other tools.
PL/SQL stored procedures increase scalability by centralizing application processing on the server. Automatic dependency tracking helps you develop scalable applications.
The shared memory facilities of the shared server enable Oracle Database to support many thousands of concurrent users on a single node. For more scalability, you can use the Oracle Connection Manager to multiplex network connections.
After being validated, you can use a PL/SQL stored procedure in any number of applications. If its definition changes, then only the procedure is affected, not the applications that call it. This simplifies maintenance and enhancement. Also, maintaining a procedure on the Oracle Database is easier than maintaining copies on various client machines.
You can use PL/SQL object types and collections for object-oriented programming.
An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods, which you can implement in PL/SQL.
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.
A collection is an ordered group of elements, all of the same type (for example, the grades for a class of students). Each element has a unique subscript that determines its position in the collection. PL/SQL offers two kinds of collections: nested tables and varrays (variable-size arrays).
Collections work like the set, queue, stack, and hash table data structures found in most third-generation programming languages. Collections can store instances of an object type and can also be attributes of an object type. 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. You can define collection types in a PL/SQL package, then use the same types across many applications.
Applications written in PL/SQL can run on any operating system and hardware platform on which Oracle Database runs. You can write portable program libraries and reuse them in different environments.
PL/SQL stored procedures enable you to divide application logic between the client and the server, which prevents client applications from manipulating sensitive Oracle Database data. Database triggers written in PL/SQL can prevent applications from making specified updates and can audit user queries.
You can restrict access to Oracle Database data by allowing users to manipulate it only through stored procedures that have a restricted set of privileges. For example, you can grant users access to a procedure that updates a table but not grant them access to the table itself.
A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.
The following packages are especially useful in application development for Oracle Database:
DBMS_PIPE
is used to communicate between sessions.
DBMS_ALERT
is used to broadcast alerts to users.
DBMS_LOCK
and DBMS_TRANSACTION
are used for lock and transaction management.
DBMS_AQ
is used for Advanced Queuing.
DBMS_LOB
is used to manipulate large objects.
DBMS_ROWID
is used for employing ROWID
values.
UTL_RAW
is the RAW facility.
UTL_REF
is for work with REF
values.
The following packages are useful for server manageability:
DBMS_SESSION
is for session management by DBAs.
DBMS_SPACE
and DBMS_SHARED_POOL
provide space information and reserve shared pool resources.
DBMS_JOB
is used to schedule jobs in the server.
Oracle Database provides built-in tools and technologies that enable you to deploy PL/SQL applications over the Web. Thus, PL/SQL serves as an alternative to Web application frameworks such as CGI.
The PL/SQL Web Toolkit is a set of PL/SQL packages that you can use to develop stored procedures that can be invoked by a Web client. The PL/SQL Gateway enables an HTTP client to invoke a PL/SQL stored procedure through mod_plsql, which is a plug-in to Oracle HTTP Server. This module performs the following actions:
Translates a URL passed by a browser client
Calls an Oracle Database stored procedure with the parameters in the URL
Returns output (typically HTML) to the client
See Also: Chapter 11, "Developing Applications with the PL/SQL Web Toolkit" to learn how to use PL/SQL in Web development |
This section provides an overview of built-in database features that support Java applications. The database includes the core JDK libraries such as java.lang
, java.io
, and so on. The database supports client-side Java standards such as JDBC and SQLJ, and provides server-side JDBC and SQLJ drivers that allow data-intensive Java code to run within the database.
This section contains the following topics:
See Also:
|
Oracle JVM, the Java Virtual Machine provided with the Oracle Database, is compliant with the J2SE version 1.4.x specification and supports the database session architecture.
Any database session can activate a dedicated JVM. All sessions share the same JVM code and statics; however, private states for any given session are held, and subsequently garbage collected, in an individual session space.
This design provides the following benefits:
Java applications have the same session isolation and data integrity as SQL operations.
There is no need to run Java in a separate process for data integrity.
Oracle JVM is a robust JVM with a small memory footprint.
The JVM has the same linear Symmetric Multiprocessing (SMP) scalability as the database and can support thousands of concurrent Java sessions.
Oracle JVM works consistently with every platform supported by Oracle Database. Java applications that you develop with Oracle JVM can easily be ported to any supported platform.
Oracle JVM includes a deployment-time native compiler that enables Java code to be compiled once, stored in executable form, shared among users, and invoked more quickly and efficiently.
Security features of the database are also available with Oracle JVM. Java classes must be loaded in a database schema (by using Oracle JDeveloper, a third-party IDE, SQL*Plus, or the loadjava utility) before they can be invoked. Java class invocation is secured and controlled through database authentication and authorization, Java 2 security, and invoker's or definer's rights.
JDBC (Java Database Connectivity) is an API (Applications Programming Interface) that allows Java to send SQL statements to an object-relational database such as Oracle Database.
The JDBC standard defines four types of JDBC drivers:
Type 1. A JDBC-ODBC bridge. Software must be installed on client systems.
Type 2. Native methods (calls C or C++) and Java methods. Software must be installed on the client.
Type 3. Pure Java. The client uses sockets to call middleware on the server.
Type 4. The most pure Java solution. Talks directly to the database by using Java sockets.
JDBC is based on the X/Open SQL Call Level Interface, and complies with the SQL92 Entry Level standard.
You can use JDBC to do dynamic SQL. In dynamic SQL, the embedded SQL statement to be executed is not known before the application is run and requires input to build the statement.
The drivers that are implemented by Oracle have extensions to the capabilities in the JDBC standard that was defined by Sun Microsystems. Oracle's implementations of JDBC drivers are described in the following sections. Oracle Database support of and extensions to various levels of the JDBC standard are described in "Oracle Database Extensions to JDBC Standards".
The JDBC thin driver is a Type 4 (100% pure Java) driver that uses Java sockets to connect directly to a database server. It has its own implementation of a Two-Task Common (TTC), a lightweight implementation of TCP/IP from Oracle Net. It is written entirely in Java and is therefore platform-independent.
The thin driver does not require Oracle software on the client side. It does need a TCP/IP listener on the server side. Use this driver in Java applets that are downloaded into a Web browser or in applications for which you do not want to install Oracle client software. The thin driver is self-contained, but it opens a Java socket, and thus can only run in a browser that supports sockets.
The OCI driver is a Type 2 JDBC driver. It makes calls to the OCI (Oracle Call Interface) written in C to interact with Oracle Database, thus using native and Java methods.
The OCI driver allows access to more features than the thin driver, such as Transparent Application Fail-Over, advanced security, and advanced LOB manipulation.
The OCI driver provides the highest compatibility between different Oracle Database versions. It also supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.
Because it uses native methods (a combination of Java and C) the OCI driver is platform-specific. It requires a client installation of version Oracle8i or later including Oracle Net, OCI libraries, CORE libraries, and all other dependent files. The OCI driver usually executes faster than the thin driver.
The OCI driver is not appropriate for Java applets, because it uses a C library that is platform-specific and cannot be downloaded into a Web browser. It is usable in J2EE components running in middle-tier application servers, such as Oracle Application Server. Oracle Application Server provides middleware services and tools that support access between applications and browsers.
The JDBC server-side internal driver is a Type 2 driver that runs inside the database server, reducing the number of round-trips needed to access large amounts of data. The driver, the Java server VM, the database, the Java native compiler which speeds execution by as much as 10 times, and the SQL engine all run within the same address space.
This driver provides server-side support for any Java program used in the database: SQLJ stored procedures, functions, triggers, and Java stored procedures. You can also call PL/SQL stored procedures, functions, and triggers.
The server driver fully supports the same features and extensions as the client-side drivers.
Oracle Database includes the following extensions to the JDBC 1.22 standard:
Support for Oracle datatypes
Performance enhancement by row prefetching
Performance enhancement by execution batching
Specification of query column types to save round-trips
Control of DatabaseMetaData
calls
Oracle Database supports all APIs from the JDBC 2.0 standard, including the core APIs, optional packages, and numerous extensions. Some of the highlights include datasources, JTA, and distributed transactions.
Oracle Database supports the following features from the JDBC 3.0 standard:
Support for JDK 1.4.
Toggling between local and global transactions.
Transaction savepoints.
Reuse of prepared statements by connection pools.
The following example shows the recommended technique for looking up a data source using JNDI in JDBC 2.0:
// import the JDBC packages
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;
...
InitialContext ictx = new InitialContext();
DataSource ds = (DataSource)ictx.lookup("jdbc/OracleDS");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT last_name FROM employees");
while ( rs.next() ) {
out.println( rs.getString("ename") + "<br>");
}
conn.close();
The following source code registers an Oracle JDBC thin driver, connects to the database, creates a Statement
object, executes a query, and processes the result set.
The SELECT
statement retrieves and lists the contents of the last_name
column of the hr.employees
table.
import java.sql.*
import java.math.*
import java.io.*
import java.awt.*
class JdbcTest {
public static void main (String args []) throws SQLException {
// Load Oracle driver
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
// Connect to the local database
Connection conn =
DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl",
"hr", "hr");
// Query the employee names
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("SELECT last_name FROM employees");
// Print the name out
while (rset.next ())
System.out.println (rset.getString (1));
// Close the result set, statement, and the connection
rset.close();
stmt.close();
conn.close();
}
}
One Oracle Database extension to the JDBC drivers is a form of the getConnection()
method that uses a Properties
object. The Properties
object lets you specify user, password, and database information as well as row prefetching and execution batching.
To use the OCI driver in this code, replace the Connection
statement with the following, where MyHostString
is an entry in the tnsnames
.ora
file:
Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8
:@MyHostString",
"hr", "hr");
If you are creating an applet, then the getConnection()
and registerDriver()
strings will be different.
JDBC code and SQLJ code (see "Overview of Oracle SQLJ") interoperate, allowing dynamic SQL statements in JDBC to be used with both static and dynamic SQL statements in SQLJ. A SQLJ iterator class corresponds to the JDBC result set.
SQLJ is an ANSI SQL-1999 standard for embedding SQL statements in Java source code. SQLJ provides a simpler alternative to JDBC for both client-side and server-side SQL data access from Java.
A SQLJ source file contains Java source with embedded SQL statements. Oracle SQLJ supports dynamic as well as static SQL. Support for dynamic SQL is an Oracle extension to the SQLJ standard.
Note: The term "SQLJ," when used in this manual, refers to the Oracle SQLJ implementation, including Oracle SQLJ extensions. |
Oracle Database provides a translator and a run time driver to support SQLJ. The SQLJ translator is 100% pure Java and is portable to any JVM that is compliant with JDK version 1.1 or higher.
The Oracle SQLJ translator performs the following tasks:
Translates SQLJ source to Java code with calls to the SQLJ run time driver. The SQLJ translator converts the source code to pure Java source code and can check the syntax and semantics of static SQL statements against a database schema and verify the type compatibility of host variables with SQL types.
Compiles the generated Java code with the Java compiler.
(Optional) Creates profiles for the target database. SQLJ generates "profile" files with customization specific to Oracle Database.
Oracle Database supports SQLJ stored procedures, functions, and triggers which execute in the Oracle JVM. SQLJ is integrated with JDeveloper. Source-level debugging support for SQLJ is available in JDeveloper.
The following is an example of a simple SQLJ executable statement, which returns one value because employee_id
is unique in the employee
table:
String name;
#sql { SELECT first_name INTO :
name FROM employees WHERE employee_id=112 };
System.out.println("Name is " + name + ", employee number = " + employee_id);
Each host variable (or qualified name or complex Java host expression) included in a SQL expression is preceded by a colon (:
). Other SQLJ statements declare Java types. For example, you can declare an iterator (a construct related to a database cursor) for queries that retrieve many values, as follows:
#sql iterator EmpIter (String EmpNam, int EmpNumb);
See Also: For more examples and details on Oracle SQLJ syntax:
|
Oracle SQLJ extensions to Java allow rapid development and easy maintenance of applications that perform database operations through embedded SQL.
In particular, Oracle SQLJ does the following:
Provides a concise, legible mechanism for database access from static SQL. Most SQL in applications is static. SQLJ provides more concise and less error-prone static SQL constructs than JDBC does.
Provides an SQL Checker module for verification of syntax and semantics at translate time.
Provides flexible deployment configurations, which makes it possible to implement SQLJ on the client, server, or middle tier.
Supports a software standard. SQLJ is an effort of a group of vendors and will be supported by all of them. Applications can access multiple database vendors.
Provides source code portability. Executables can be used with all of the vendor DBMSs if the code does not rely on any vendor-specific features.
Enforces a uniform programming style for the clients and the servers.
Integrates the SQLJ translator with Oracle JDeveloper, a graphical IDE that provides SQLJ translation, Java compilation, profile customizing, and debugging at the source code level, all in one step.
Includes Oracle type extensions. Datatypes supported include: LOB datatypes, ROWID
, REF CURSOR
, VARRAY
, nested table, user-defined object types, RAW
, and NUMBER
.
JDBC provides a complete dynamic SQL interface from Java to databases. It gives developers full control over database operations. SQLJ simplifies Java database programming to improve development productivity.
JDBC provides fine-grained control of the execution of dynamic SQL from Java, whereas SQLJ provides a higher-level binding to SQL operations in a specific database schema. Following are some differences between JDBC and SQLJ:
SQLJ source code is more concise than equivalent JDBC source code.
SQLJ uses database connections to type-check static SQL code. JDBC, being a completely dynamic API, does not.
SQLJ provides strong typing of query outputs and return parameters and allows type-checking on calls. JDBC passes values to and from SQL without compile-time type checking.
SQLJ programs allow direct embedding of Java bind expressions within SQL statements. JDBC requires a separate get or set statement for each bind variable and specifies the binding by position number.
SQLJ provides simplified rules for calling SQL stored procedures and functions. For example, the following JDBC excerpt requires a generic call to a stored procedure or function, in this case fun
, to have the following syntax. (This example shows SQL92 and Oracle JDBC syntaxes. Both are allowed.)
prepStmt.prepareCall("{call fun(?,?)}"); //stored procedure SQL92 prepStmt.prepareCall("{? = call fun(?,?)}"); //stored function SQL92 prepStmt.prepareCall("begin fun(:1,:2);end;"); //stored procedure Oracle prepStmt.prepareCall("begin :1 := fun(:2,:3);end;");//stored func Oracle
Following is the SQLJ equivalent:
#sql {call fun(param_list) }; //Stored procedure // Declare x ... #sql x = {VALUES(fun(param_list)) }; // Stored function // where VALUES is the SQL construct
The following benefits are common to SQLJ and JDBC:
SQLJ applications can be stored and executed in the server by using the following techniques:
Translate, compile, and customize the SQLJ source code on a client and load the generated classes and resources into the server with the loadjava
utility. The classes are typically stored in a Java archive (.jar
) file.
Load the SQLJ source code into the server, also using loadjava
, where it is translated and compiled by the server's embedded translator.
See Also: Oracle Database JPublisher User's Guide for more information on using stored procedures with Oracle SQLJ |
Oracle JPublisher is a code generator that automates the process of creating database-centric Java classes by hand. Oracle JPublisher is a client-side utility and is built into the database system. You can run Oracle JPublisher from the command line or directly from the Oracle JDeveloper IDE.
Oracle JPublisher inspects PL/SQL packages and database object types such as SQL object types, VARRAY
types, and nested table types, and then generates a Java class that is a wrapper around the PL/SQL package with corresponding fields and methods.
The generated Java class can be incorporated and used by Java clients or J2EE components to exchange and transfer object type instances to and from the database transparently.
Java stored procedures enable you to implement programs that run in the database server and which are independent of programs that run in the middle tier. Structuring applications in this way reduces complexity and increases reuse, security, performance, and scalability.
For example, you can create a Java stored procedure that performs operations that require data persistence and a separate program to perform presentation or business logic operations.
Java stored procedures interface with SQL by using a similar execution model as PL/SQL.
Web services represent a distributed computing paradigm for Java application development that is an alternative to earlier Java protocols such as JDBC. It allows application-to-application interaction by means of the XML and Web protocols. For example, an electronics parts vendor can provide a Web-based programmatic interface to its suppliers for inventory management. The vendor can call a Web service as part of a program and automatically order new stock based on the data returned.
The key technologies used in Web services are:
Web Services Description Language (WSDL), which is a standard format for creating an XML document. WSDL describes what a web service can do, where it resides, and how to invoke it. Specifically, it describes the operations and parameters, including parameter types, provided by a Web service. In addition, a WSDL document describes the location, the transport protocol, and the invocation style for the Web service.
Simple Object Access Protocol (SOAP) messaging, which is an XML-based message protocol used by Web services. SOAP does not prescribe a specific transport mechanism such as HTTP, FTP, SMTP, or JMS; however, most Web services accept messages that use HTTP or HTTPS.
Universal Description, Discovery, and Integration (UDDI) business registry, which is a directory that lists Web services on the internet. The UDDI registry is often compared to a telephone directory, listing unique identifiers (white pages), business categories (yellow pages), and instructions for binding to a service protocol (green pages).
Web services can use a variety of techniques and protocols. For example:
Dispatching can occur in a synchronous (typical) or asynchronous manner.
You can perform invocation in an RPC-style operation in which arguments are sent and a response returned, or in a message style such as a one-way SOAP document exchange.
You can use different encoding rules: literal or encoded.
You can invoke a Web service statically, in which case you may know everything about it beforehand, or dynamically, in which case you can discover its operations and transport endpoints on the fly.
Oracle Database can function as either a Web service provider or as a Web service consumer. When used as a provider, the database enables sharing and disconnected access to stored procedures, data, metadata, and other database resources such as the queuing and messaging systems.
As a Web service provider, Oracle Database provides a disconnected and heterogeneous environment that:
Exposes stored procedures independently of the language in which the procedures are written
Exposes SQL Queries and XQuery
Functions and procedures are named blocks that encapsulate a sequence of statements. They are like building blocks that you can use to construct modular, maintainable applications. You write these named blocks and then define them by using the loadjava
command or SQL CREATE
FUNCTION
, CREATE
PROCEDURE
, or CREATE
PACKAGE
statements. These Java methods can accept arguments and are callable from:
SQL CALL
statements
Embedded SQL CALL
statements
PL/SQL blocks, subprograms, and packages
DML statements (INSERT
, UPDATE
, DELETE
, and SELECT
)
Oracle development tools such as OCI, Pro*C/C++, and Oracle Developer
Oracle Java interfaces such as JDBC, SQLJ statements, CORBA, and Enterprise Java Beans
Method calls from object types
A database trigger is a stored procedure that Oracle Database invokes ("fires") automatically when certain events occur, for example, when a DML operation modifies a certain table. Triggers enforce business rules, prevent incorrect values from being stored, and reduce the need to perform checking and cleanup operations in each application.
Stored procedures and triggers are compiled once, are easy to use and maintain, and require less memory and computing overhead.
Network bottlenecks are avoided, and response time is improved. Distributed applications are easier to build and use.
Computation-bound procedures run faster in the server.
Data access can be controlled by letting users have only stored procedures and triggers that execute with their definer's privileges instead of invoker's rights.
PL/SQL and Java stored procedures can call each other.
Java in the server follows the Java language specification and can use the SQLJ standard, so that databases other than Oracle Database are also supported.
Stored procedures and triggers can be reused in different applications as well as different geographic sites.
The Pro*C/C++ precompiler is a software tool that allows the programmer to embed SQL statements in a C or C++ source file. Pro*C/C++ reads the source file as input and outputs a C or C++ source file that replaces the embedded SQL statements with Oracle runtime library calls and is then compiled by the C or C++ compiler.
When there are errors found during the precompilation or the subsequent compilation, modify your precompiler input file and re-run the two steps.
The following is a simple code fragment from a C source file that queries the table employees
in the schema hr
:
... #define UNAME_LEN 10 ... int emp_number; /* Define a host structure for the output values of a SELECT statement. */ /* No declare section needed if precompiler option MODE=ORACLE */ struct { VARCHAR last_name[UNAME_LEN]; float salary; float commission_pct; } emprec; /* Define an indicator structure to correspond to the host output structure. */ struct { short emp_name_ind; short sal_ind; short comm_ind; } emprec_ind; ... /* Select columns last_name, salary, and commission_pct given the user's input /* for employee_id. */ EXEC SQL SELECT last_name, salary, commission_pct INTO :emprec INDICATOR :emprec_ind FROM employees WHERE employee_id = :emp_number; ...
The embedded SELECT
statement is only slightly different from an interactive (SQL*Plus) SELECT
statement. Every embedded SQL statement begins with EXEC SQL. The colon (:), precedes every host (C) variable. The returned values of data and indicators (set when the data value is NULL
or character columns have been truncated) can be stored in structs (such as in the preceding code fragment), in arrays, or in arrays of structs. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, because of the unique employee number. You use the actual names of columns and tables in embedded SQL.
Use the default precompiler option values, or you can enter values which give you control over the use of resources, how errors are reported, the formatting of output, and how cursors (which correspond to a particular connection or SQL statement) are managed. Cursors are used when there are multiple result set values.
Enter the options either in a configuration file, on the command line, or in-line inside your source code with a special statement that begins with EXEC ORACLE
. If there are no errors found, you can then compile, link, and execute the output source file, like any other C program that you write.
Use the precompiler to create server database access from clients that can be on many different platforms. Pro*C/C++ allows you the freedom to design your own user interfaces and to add database access to existing applications.
Before writing your embedded SQL statements, you may want to test interactive versions of the SQL in SQL*Plus. You then make only minor changes to start testing your embedded SQL application.
The following is a short subset of the capabilities of Pro*C/C++. For complete details, refer to the Pro*C/C++ Precompiler Programmer's Guide.
You can write your application in either C or C++.
You can write multithreaded programs if your platform supports a threads package. Concurrent connections are supported in either single-threaded or multithreaded applications.
You can improve performance by embedding PL/SQL blocks. These blocks can call functions or procedures in Java or PL/SQL that are written by you or provided in Oracle Database packages.
Using precompiler options, you can check the syntax and semantics of your SQL or PL/SQL statements during precompilation, as well as at runtime.
You can call stored PL/SQL and Java subprograms. Modules written in COBOL or in C can be called from Pro*C/C++. External C procedures in shared libraries are callable by your program.
You can conditionally precompile sections of your code so that they can execute in different environments.
You can use arrays, or structures, or arrays of structures as host and indicator variables in your code to improve performance.
You can deal with errors and warnings so that data integrity is guaranteed. As a programmer, you control how errors are handled.
Your program can convert between internal datatypes and C language datatypes.
The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI), lower-level C and C++ interfaces, are available for use in your precompiler source.
Pro*C/C++ supports dynamic SQL, a technique that allows users to input variable values and statement syntax.
Pro*C/C++ can use special SQL statements to manipulate tables containing user-defined object types. An Object Type Translator (OTT) will map the object types and named collection types in your database to structures and headers that you will then include in your source.
Two kinds of collection types, nested tables and VARRAY
, are supported with a set of SQL statements that allow a high degree of control over data.
Large Objects are accessed by another set of SQL statements.
A new ANSI SQL standard for dynamic SQL is supported for new applications, so that you can execute SQL statements with a varying number of host variables. An older technique for dynamic SQL is still usable by pre-existing applications.
Globalization support lets you use multibyte characters and UCS2 Unicode data.
Using scrollable cursors, you can move backward and forward through a result set. For example, you can fetch the last row of the result set, or jump forward or backward to an absolute or relative position within the result set.
A connection pool is a group of physical connections to a database that can be shared by several named connections. Enabling the connection pool option can help to optimize the performance of Pro*C/C++ application. The connection pool option is not enabled by default.
The Pro*COBOL precompiler is a software tool that allows the programmer to embed SQL statements in a COBOL source code file. Pro*COBOL reads the source file as input and outputs a COBOL source file that replaces the embedded SQL statements with Oracle Database runtime library calls, and is then compiled by the COBOL compiler.
When there are errors found during the precompilation or the subsequent compilation, modify your precompiler input file and re-run the two steps.
Here is a simple code fragment from a source file that queries the table employees
in the schema hr
:
... WORKING-STORAGE SECTION. * * DEFINE HOST INPUT AND OUTPUT HOST AND INDICATOR VARIABLES. * NO DECLARE SECTION NEEDED IF MODE=ORACLE. * 01 EMP-REC-VARS. 05 EMP-NAME PIC X(10) VARYING. 05 EMP-NUMBER PIC S9(4) COMP VALUE ZERO. 05 SALARY PIC S9(5)V99 COMP-3 VALUE ZERO. 05 COMMISSION PIC S9(5)V99 COMP-3 VALUE ZERO. 05 COMM-IND PIC S9(4) COMP VALUE ZERO. ... PROCEDURE DIVISION. ... EXEC SQL SELECT last_name, salary, commission_pct INTO :EMP-NAME, :SALARY, :COMMISSION:COMM-IND FROM employees WHERE employee_id = :EMP-NUMBER END-EXEC. ...
The embedded SELECT
statement is only slightly different from an interactive (SQL*Plus) SELECT
statement. Every embedded SQL statement begins with EXEC SQL. The colon (:) precedes every host (COBOL) variable. The SQL statement is terminated by END-EXEC. The returned values of data and indicators (set when the data value is NULL
or character columns have been truncated) can be stored in group items (such as in the preceding code fragment), in tables, or in tables of group items. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, given the unique employee number. You use the actual names of columns and tables in embedded SQL.
Use the default precompiler option values, or enter values that give you control over the use of resources, how errors are reported, the formatting of output, and how cursors are managed (cursors correspond to a particular connection or SQL statement).
Enter the options in a configuration file, on the command line, or in-line inside your source code with a special statement that begins with EXEC ORACLE. If there are no errors found, you can then compile, link, and execute the output source file, like any other COBOL program that you write.
Use the precompiler to create server database access from clients that can be on many different platforms. Pro*COBOL allows you the freedom to design your own user interfaces and to add database access to existing COBOL applications.
The embedded SQL statements available conform to an ANSI standard, so that you can access data from many databases in a program, including remote servers networked through Oracle Net.
Before writing your embedded SQL statements, you may want to test interactive versions of the SQL in SQL*Plus. You then make only minor changes to start testing your embedded SQL application.
The following is a short subset of the capabilities of Pro*COBOL.
You can call stored PL/SQL or Java subprograms. You can improve performance by embedding PL/SQL blocks. These blocks can call PL/SQL functions or procedures written by you or provided in Oracle Database packages.
Precompiler options allow you to define how cursors, errors, syntax-checking, file formats, and so on, are handled.
Using precompiler options, you can check the syntax and semantics of your SQL or PL/SQL statements during precompilation, as well as at runtime.
You can conditionally precompile sections of your code so that they can execute in different environments.
Use tables, or group items, or tables of group items as host and indicator variables in your code to improve performance.
You can program how errors and warnings are handled, so that data integrity is guaranteed.
Pro*COBOL supports dynamic SQL, a technique that allows users to input variable values and statement syntax.
The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI) are application programming interfaces (APIs) that allow you to create applications that use native procedures or function calls of a third-generation language to access Oracle Database and control all phases of SQL statement execution. These APIs provide:
Improved performance and scalability through the efficient use of system memory and network connectivity
Consistent interfaces for dynamic session and transaction management in a two-tier client/server or multitier environment
N-tiered authentication
Comprehensive support for application development using Oracle objects
Access to external databases
Ability to develop applications that service an increasing number of users and requests without additional hardware investments
OCI lets you manipulate data and schemas in a database using a host programming language, such as C. OCCI is an object-oriented interface suitable for use with C++. These APIs provide a library of standard database access and retrieval functions in the form of a dynamic runtime library (OCILIB) that can be linked in an application at runtime. This eliminates the need to embed SQL or PL/SQL within 3GL programs.
See Also: For more information about OCI and OCCI calls: |
OCI provides significant advantages over other methods of accessing Oracle Database:
More fine-grained control over all aspects of the application design.
High degree of control over program execution.
Use of familiar 3GL programming techniques and application development tools such as browsers and debuggers.
Support of dynamic SQL, method 4.
Availability on the broadest range of platforms of all the Oracle programmatic interfaces.
Dynamic bind and define using callbacks.
Describe functionality to expose layers of server metadata.
Asynchronous event notification for registered client applications.
Enhanced array data manipulation language (DML) capability for array INSERTs
, UPDATEs
, and DELETEs
.
Ability to associate a commit request with an execute to reduce round-trips.
Optimization for queries using transparent prefetch buffers to reduce round-trips.
Thread safety, so you do not have to implement mutual exclusion (mutex) locks on OCI handles.
The server connection in nonblocking mode means that control returns to the OCI code when a call is still executing or could not complete.
The OCI encompasses four main sets of functionality:
OCI relational functions, for managing database access and processing SQL statements
OCI navigational functions, for manipulating objects retrieved from an Oracle Database
OCI datatype mapping and manipulation functions, for manipulating data attributes of Oracle types
OCI external procedure functions, for writing C callbacks from PL/SQL
The Oracle Call Interface (OCI) enables you to develop applications that combine the non-procedural data access power of SQL with the procedural capabilities of most programming languages, including C and C++. Note the following characteristics of procedural and non-procedural languages:
In a non-procedural language program, the set of data to be operated on is specified, but what operations will be performed and how the operations are to be carried out is not specified. The non-procedural nature of SQL makes it an easy language to learn and to use to perform database transactions. It is also the standard language used to access and manipulate data in modern relational and object-relational database systems.
In a procedural language program, the execution of most statements depends on previous or subsequent statements and on control structures, such as loops or conditional branches, which are not available in SQL. The procedural nature of these languages makes them more complex than SQL, but it also makes them very flexible and powerful.
The combination of both non-procedural and procedural language elements in an OCI program provides easy access to Oracle Database in a structured programming environment.
The OCI supports all SQL data definition, data manipulation, query, and transaction control facilities that are available through Oracle Database. For example, an OCI program can run a query against Oracle Database. The queries can require the program to supply data to the database using input (bind) variables, as follows:
SELECT name FROM employees WHERE empno = :
empnumber
In the preceding SQL statement,:empnumber
is a placeholder for a value that will be supplied by the application.
You can alternatively use PL/SQL, Oracle's procedural extension to SQL. The applications you develop can be more powerful and flexible than applications written in SQL alone. The OCI also provides facilities for accessing and manipulating objects in Oracle Database.
As Figure 1-1 shows, you compile and link an OCI program in the same way that you compile and link a non-database application. There is no need for a separate preprocessing or precompilation step.
Note: To properly link your OCI programs, it may be necessary on some platforms to include other libraries, in addition to the OCI library. Check your Oracle platform-specific documentation for further information about extra libraries that may be required. |
Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for Oracle Database.
ODP.NET uses APIs native to Oracle Database to offer fast and reliable access from any .NET application to database features and data. It also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library.
For programmers using Oracle Provider for OLE DB, ADO (ActiveX Data Objects) provides an automation layer that exposes an easy programming model. ADO.NET provides a similar programming model, but without the automation layer, for better performance. More importantly, the ADO.NET model allows native providers such as ODP.NET to expose specific features and datatypes specific to Oracle Database.
The following is a simple C# application that connects to Oracle Database and displays its version number before disconnecting.
using System; using Oracle.DataAccess.Client; class Example { OracleConnection con; void Connect() { con = new OracleConnection(); con.ConnectionString = "User Id=hr;Password=hr;Data Source=oracle"; con.Open(); Console.WriteLine("Connected to Oracle" + con.ServerVersion); } void Close() { con.Close(); con.Dispose(); } static void Main() { Example example = new Example(); example.Connect(); example.Close(); } }
Note: Additional samples are provided in directoryORACLE_BASE \ ORACLE_HOME \ODP.NET\Samples .
|
Oracle Objects for OLE (OO4O) is a product designed to allow easy access to data stored in Oracle Database with any programming or scripting language that supports the Microsoft COM Automation and ActiveX technology. This includes Visual Basic, Visual C++, Visual Basic For Applications (VBA), IIS Active Server Pages (VBScript and JavaScript), and others.
See the OO4O online help for detailed information about using OO4O.
Oracle Objects for OLE consists of the following software layers:
OO4O "In-Process" Automation Server
Oracle Data Control
Oracle Objects for OLE C++ Class Library
Figure 1-2, "Software Layers" illustrates the OO4O software components.
The OO4O Automation Server is a set of COM Automation objects for connecting to Oracle Database, executing SQL statements and PL/SQL blocks, and accessing the results.
Unlike other COM-based database connectivity APIs, such as Microsoft ADO, the OO4O Automation Server has been developed and evolved specifically for use with Oracle Database.
It provides an optimized API for accessing features that are unique to Oracle Database and are otherwise cumbersome or inefficient to use from ODBC or OLE database-specific components.
OO4O provides key features for accessing Oracle Database efficiently and easily in environments ranging from the typical two-tier client/server applications, such as those developed in Visual Basic or Excel, to application servers deployed in multitiered application server environments such as Web server applications in Microsoft Internet Information Server (IIS) or Microsoft Transaction Server (MTS).
Features include:
Support for execution of PL/SQL and Java stored procedures, and PL/SQL anonymous blocks. This includes support for Oracle datatypes used as parameters to stored procedures, including PL/SQL cursors. Refer to "Support for Oracle LOB and Object Datatypes".
Support for scrollable and updatable cursors for easy and efficient access to result sets of queries.
Thread-safe objects and Connection Pool Management Facility for developing efficient Web server applications.
Full support for Oracle object-relational and LOB datatypes.
Full support for Advanced Queuing.
Support for array inserts and updates.
Support for Microsoft Transaction Server (MTS).
The Oracle Objects for OLE object model is illustrated in Figure 1-3, "Objects and Their Relations".
An OraSession object manages collections of OraDatabase, OraConnection, and OraDynaset objects used within an application.
Typically, a single OraSession object is created for each application, but you can create named OraSession objects for shared use within and between applications.
The OraSession object is the top-most object for an application. It is the only object created by the CreateObject VB/VBA API and not by an Oracle Objects for OLE method. The following code fragment shows how to create an OraSession object:
Dim OraSession as Object Set OraSession = CreateObject("OracleInProcServer.XOraSession")
OraServer represents a physical network connection to Oracle Database.
The OraServer
interface is introduced to expose the connection-multiplexing feature provided in the Oracle Call Interface. After an OraServer
object is created, multiple user sessions (OraDatabase
) can be attached to it by invoking the OpenDatabase
method. This feature is particularly useful for application components, such as Internet Information Server (IIS), that use Oracle Objects for OLE in n-tier distributed environments.
The use of connection multiplexing when accessing Oracle Database with a large number of user sessions active can help reduce server processing and resource requirements while improving server scalability.
OraServer is used to share a single connection across multiple OraDatabase objects (multiplexing), whereas each OraDatabase obtained from an OraSession has its own physical connection.
An OraDatabase
interface adds additional methods for controlling transactions and creating interfaces representing of Oracle object types. Attributes of schema objects can be retrieved using the Describe
method of the OraDatabase
interface.
In releases prior to Oracle8i, an OraDatabase
object is created by invoking the OpenDatabase
method of an OraSession
interface. The Oracle Net alias, user name, and password are passed as arguments to this method. In Oracle8i and later, invocation of this method results in implicit creation of an OraServer
object.
An OraDatabase
object can also be created using the OpenDatabase
method of the OraServer
interface.
Transaction control methods are available at the OraDatabase
(user session) level. Transactions may be started as Read-Write
(default), Serializable
, or Read-only
. Transaction control methods include:
BeginTrans
CommitTrans
RollbackTrans
For example:
UserSession.BeginTrans(OO4O_TXN_READ_WRITE) UserSession.ExecuteSQL("delete emp where empno = 1234") UserSession.CommitTrans
An OraDynaset
object permits browsing and updating of data created from a SQL SELECT
statement.
The OraDynaset
object can be thought of as a cursor, although in actuality several real cursors may be used to implement the semantics of OraDynaset
. An OraDynaset
object automatically maintains a local cache of data fetched from the server and transparently implements scrollable cursors within the browse data. Large queries may require significant local disk space; application developers are encouraged to refine queries to limit disk usage.
An OraField
object represents a single column or data item within a row of a dynaset.
If the current row is being updated, then the OraField
object represents the currently updated value, although the value may not yet have been committed to the database.
Assignment to the Value
property of a field is permitted only if a record is being edited (using Edit
) or a new record is being added (using AddNew
). Other attempts to assign data to a field's Value
property results in an error.
An OraMetaData
object is a collection of OraMDAttribute
objects that represent the description information about a particular schema object in the database.
The OraMetaData
object can be visualized as a table with three columns:
Metadata Attribute Name
Metadata Attribute Value
Flag
specifying whether the Value
is another OraMetaData
object
The OraMDAttribute
objects contained in the OraMetaData
object can be accessed by subscripting using ordinal integers or by using the name of the property. Referencing a subscript that is not in the collection results in the return of a NULL
OraMDAttribute
object.
An OraParameter
object represents a bind variable in a SQL statement or PL/SQL block.
OraParameter
objects are created, accessed, and removed indirectly through the OraParameters
collection of an OraDatabase
object. Each parameter has an identifying name and an associated value. You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the object descriptions), by using the parameter name as a placeholder in the SQL or PL/SQL statement. Such use of parameters can simplify dynamic queries and increase program performance.
An OraParamArray
object represents an array-type bind variable in a SQL statement or PL/SQL block, as opposed to a scalar-type bind variable represented by the OraParameter
object.
OraParamArray
objects are created, accessed, and removed indirectly through the OraParameters
collection of an OraDatabase
object. Each OraParamArray
object has an identifying name and an associated value.
An OraSQLStmt
object represents a single SQL statement. Use the CreateSQL
method to create an OraSQLStmt
object from an OraDatabase
object.
During create and refresh, OraSQLStmt
objects automatically bind all relevant, enabled input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can improve the performance of SQL statement execution without re-parsing the SQL statement.
The OraSQLStmt
object can be used later to execute the same query using a different value for the :SALARY
placeholder. This is done as follows (updateStmt
is the OraSQLStmt
object here):
OraDatabase.Parameters("SALARY").value = 200000 updateStmt.Parameters("ENAME").value = "KING" updateStmt.Refresh
An OraAQ
object is instantiated by invoking the CreateAQ
method of the OraDatabase
interface. It represents a queue that is present in the database.
Oracle Objects for OLE provides interfaces for accessing Oracle Advanced Queuing (AQ) feature. It makes AQ accessible from popular COM-based development environments such as Visual Basic. For a detailed description of Oracle Advanced Queuing, refer to Oracle Streams Advanced Queuing User's Guide and Reference.
The OraAQMsg
object encapsulates the message to be enqueued or dequeued. The message can be of any user-defined or raw type.
For a detailed description of Oracle Advanced Queuing, refer to Oracle Streams Advanced Queuing User's Guide and Reference.
The OraAQAgent
object represents a message recipient and is only valid for queues that allow multiple consumers. It is a child of OraAQMsg
.
An OraAQAgent
object can be instantiated by invoking the AQAgent
method. For example:
Set agent = qMsg.AQAgent(name)
An OraAQAgent
object can also be instantiated by invoking the AddRecipient
method. For example:
Set agent = qMsg.AddRecipient(name, address, protocol).
Oracle Objects for OLE provides full support for accessing and manipulating instances of object datatypes and LOBs in Oracle Database. Figure 1-4, "Supported Oracle Datatypes" illustrates the datatypes supported by OO4O.
Instances of these types can be fetched from the database or passed as input or output variables to SQL statements and PL/SQL blocks, including stored procedures and functions. All instances are mapped to COM Automation Interfaces that provide methods for dynamic attribute access and manipulation.
The OraBlob
and OraClob
interfaces in Oracle Objects for OLE provide methods for performing operations on large database objects of datatype BLOB
, CLOB
, and NCLOB
. BLOB
, CLOB
, and NCLOB
datatypes are also referred to here as LOB datatypes.
LOB data is accessed using Read
and the CopyToFile
methods.
LOB data is modified using Write
, Append
, Erase
, Trim
, Copy
, CopyFromFile
, and CopyFromBFile
methods. Before modifying the content of a LOB column in a row, a row lock must be obtained. If the LOB column is a field of an OraDynaset
, object, then the lock is obtained by invoking the Edit
method.
Oracle Data Control (ODC) is an ActiveX Control that is designed to simplify the exchange of data between Oracle Database and visual controls such edit, text, list, and grid controls in Visual Basic and other development tools that support custom controls.
ODC acts as an agent to handle the flow of information from Oracle Database and a visual data-aware control, such as a grid control, that is bound to it. The data control manages various user interface (UI) tasks such as displaying and editing data. It also executes and manages the results of database queries.
Oracle Data Control is compatible with the Microsoft data control included with Visual Basic. If you are familiar with the Visual Basic data control, learning to use Oracle Data Control is quick and easy. Communication between data-aware controls and a Data Control is governed by a protocol that has been specified by Microsoft.
Oracle Objects for OLE C++ Class Library is a collection of C++ classes that provide programmatic access to the Oracle Object Server. Although the class library is implemented using OLE Automation, neither the OLE development kit nor any OLE development knowledge is necessary to use it. This library helps C++ developers avoid the chore of writing COM client code for accessing the OO4O interfaces.
For detailed information about Oracle Objects for OLE refer to the online help provided with the OO4O product:
Oracle Objects for OLE Help
Oracle Objects for OLE C++ Class Library Help
To view examples of how to use Oracle Objects for OLE, refer to the samples located in the ORACLE_HOME\OO4O
directory of the Oracle Database installation. Additional OO4O examples can be found in the following Oracle publications:
To choose a programming environment for a new development project:
Review the preceding overviews and the manuals for each environment.
Read the platform-specific manual that explains which compilers are approved for use with your platforms.
If a particular language does not provide a feature you need, remember that PL/SQL and Java stored procedures can both be called from code written in any of the languages in this chapter. Stored procedures include triggers and object type methods.
External procedures written in C can be called from OCI, Java, PL/SQL or SQL. The external procedure itself can call back into the database using either SQL, OCI, or Pro*C (but not C++).
The following examples illustrate easy choices:
Pro*COBOL does not support object types or collection types, while Pro*C/C++ does.
SQLJ does not support dynamic SQL the way that JDBC does.
Precompiler applications typically contain less code than equivalent OCI applications, which can help productivity.
Some situations require detailed control of the database and are suited for OCI applications (either pure OCI or a precompiler application with embedded OCI calls):
OCI provides more detailed control over multiplexing and migrating sessions.
OCI provides dynamic bind and define using callbacks that can be used for any arbitrary structure, including lists.
OCI has many calls to handle metadata.
OCI allows asynchronous event notifications to be received by a client application. It provides a means for clients to generate notifications for propagation to other clients.
OCI allows DML statements to use arrays to complete as many iterations as possible before returning any error messages.
OCI calls for special purposes include Advanced Queuing, globalization support, Data Cartridges, and support of the date and time datatypes.
OCI calls can be embedded in a Pro*C/C++ application.
Both Java and PL/SQL have built-in packages and libraries.
PL/SQL and Java interoperate in the server. You can execute a PL/SQL package from Java or wrap a PL/SQL class with a Java wrapper so that it can be called from distributed CORBA and EJB clients. The following table shows PL/SQL packages and their Java equivalents:
Table 1-1 PL/SQL and Java Equivalent Software
PL/SQL Package | Java Equivalent |
---|---|
|
Call package with SQLJ or JDBC. |
|
JDBC has this functionality. |
|
Schedule a job that has a Java Stored procedure. |
|
Call with SQLJ or JDBC. |
|
Use JavaMail. |
|
Use subclass |
|
Call with SQLJ or JDBC. |
|
Use JDBC to execute an |
|
Call with SQLJ or JDBC. |
|
Use JDBC. |
|
Use JDBC to execute an |
|
Call with SQLJ or JDBC. |
|
Grant the |
Both Java and PL/SQL can be used to build applications in the database. Here are some guidelines for their use:
PL/SQL uses the same datatypes as SQL. SQL datatypes are thus easier to use and SQL operations are faster than with Java, especially when a large amount of data is involved, when mostly database access is done, or when bulk operations are used.
PL/SQL is an extension to SQL offering data encapsulation, information hiding, overloading, and exception-handling.
Some advanced PL/SQL capabilities are not available for Java in Oracle9i. Examples are autonomous transactions and the dblink facility for remote databases. Code development is usually faster in PL/SQL than in Java.
Java has inheritance, polymorphism, and component models for developing distributed systems. PL/SQL has inheritance and type evolution, the ability to change methods and attributes of a type while preserving subtypes and table data that use the type.
Java has a richer type system than PL/SQL and is an object-oriented language. Java can use CORBA (which can have many different computer languages in its clients) and EJB. PL/SQL packages can be called from CORBA or EJB clients.
You can run XML tools, the Internet File System, or JavaMail from Java.
Many Java-based development tools are available throughout the industry.