1 Orientation to Oracle Programmatic Environments

What Is PL/SQL?

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.

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;

Advantages of PL/SQL

PL/SQL is a portable, high-performance transaction processing language with the following advantages:

• Integration with Oracle Database

• High Performance

• High Productivity

• Scalability

• Manageability

• Object-Oriented Programming Support

• Portability

• Security

• Built-In Packages

PL/SQL Web Development Tools

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:

1. Translates a URL passed by a browser client

2. Calls an Oracle Database stored procedure with the parameters in the URL

3. Returns output (typically HTML) to the client

Overview of Oracle JVM

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.

Overview of Oracle Extensions to JDBC

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".

// 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(); 

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();
  } 
} 

Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString",
    "hr", "hr");

Overview of Oracle SQLJ

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.

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);

Overview of Oracle JPublisher

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.

Overview of Java Stored Procedures

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.

Overview of Oracle Database Web Services

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.

Overview of Writing Procedures and Functions in Java

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

Implementing a Pro*C/C++ Application

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.

Highlights of Pro*C/C++ Features

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.

How You Implement a Pro*COBOL Application

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.

Highlights of Pro*COBOL Features

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.

  
  

  See Also: Pro*COBOL Programmer's Guide for complete details

  
  

Advantages of OCI

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.

Parts of the OCI

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

Procedural and Non-Procedural Elements

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.

Building an OCI Application

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.

Figure 1-1 The OCI Development Process

Description of "Figure 1-1 The OCI Development Process"

Using ODP.NET in a Simple Application

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(); 
  } 
} 

OO4O Automation Server

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).

OO4O Object Model

The Oracle Objects for OLE object model is illustrated in Figure 1-3, "Objects and Their Relations".

Figure 1-3 Objects and Their Relations

Description of "Figure 1-3 Objects and Their Relations "

Dim OraSession as Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

UserSession.BeginTrans(OO4O_TXN_READ_WRITE) 
UserSession.ExecuteSQL("delete emp where empno = 1234") 
UserSession.CommitTrans 

OraDatabase.Parameters("SALARY").value = 200000
updateStmt.Parameters("ENAME").value = "KING"
updateStmt.Refresh

Set agent = qMsg.AQAgent(name)

Set agent = qMsg.AddRecipient(name, address, protocol).

Support for Oracle LOB and Object Datatypes

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.

Figure 1-4 Supported Oracle Datatypes

Description of "Figure 1-4 Supported Oracle Datatypes"

Oracle Data Control

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

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.

Additional Sources of Information

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:

• Oracle Database Application Developer's Guide - Large Objects

• Oracle Streams Advanced Queuing User's Guide and Reference

• Oracle Database PL/SQL Packages and Types Reference

Choosing Whether to Use OCI or a Precompiler

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.

Using Built-In Packages and Libraries

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:

Java Compared to PL/SQL

Both Java and PL/SQL can be used to build applications in the database. Here are some guidelines for their use: