Skip Headers
Oracle® Database Concepts
11g Release 1 (11.1)

Part Number B28318-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

25 Supported Application Development Languages

This chapter presents brief overviews of Oracle application development systems.

This chapter includes the following topics:

See Also:

Chapter 24, "SQL"

Introduction to Oracle Application Development Languages

Oracle Database developers have a choice of languages for developing applications—C, C++, Java, COBOL, PL/SQL, Visual Basic, and C#. All language-specific standards are supported. Developers can choose the languages in which they are most proficient or one that is most suitable for a specific task. For example an application might use Java on the server side to create dynamic Web pages, PL/SQL to implement stored procedures in the database, and C++ to implement computationally intensive logic in the middle tier.

Oracle also provides the Pro* series of precompilers, which allow you to embed SQL and PL/SQL in your C, C++, COBOL, or FORTRAN application programs.

Overview of C/C++ Programming Languages

This section includes the following topics:

Overview of Oracle Call Interface (OCI)

Oracle Call Interface (OCI) is an application programming interface (API) that lets you create applications that use the native function calls of the C language to access an Oracle database and control all phases of SQL statement execution. OCI supports the data types, calling conventions, syntax, and semantics of C. OCI can directly access data in Oracle Database tables or can enqueue and dequeue data into or out of Oracle Streams.

OCI provides the following:

  • Instant client, a way to deploy applications in a much reduced disk space.

  • Thread management, connection pooling, globalization functions, and direct path loading of data (SQL*Loader Utility) from a C application.

  • N-tiered authentication.

  • Comprehensive support for application development using Oracle Database objects.

  • Access to external databases.

  • Applications that can service an increasing number of users and requests without additional hardware investments.

OCI lets you manipulate data and schemas in an Oracle database using the C host programming language. It provides a library of standard database access and retrieval functions in the form of a dynamic runtime library (OCI library) that can be linked in an application at runtime.

Query results can be cached in memory in the OCI client result cache. The OCI client can then use cached results for future executions of these queries. Because retrieving results from the result cache is faster than making a database call and rerunning the query, frequently run queries experience a significant performance improvement when their results are cached. The result cache on the OCI client is per process and shared between the different sessions.

See Also:

Oracle Call Interface Programmer's Guide for more information about the OCI client result cache

OCI supports object-relational features of Oracle Database. One important component is a set of calls that allows application programs to use a workspace called the object cache. The object cache is a memory block on the client side that allows programs to store entire objects and to navigate among them without round-trips to the server.

The object cache is completely under the control and management of the application programs using it. Oracle Database has no access to it. The application programs using it must maintain data coherency with the server and protect the workspace against simultaneous conflicting access.

Query results can be cached in memory in the OCI client result cache. The OCI client can then use cached results for future executions of these queries. Because retrieving results from the result cache is faster than making a database call and rerunning the query, frequently run queries experience a significant performance improvement when their results are cached. The result cache on the OCI client is per process and shared between the different sessions.

See Also:

Oracle Call Interface Programmer's Guide for more information about the OCI client result cache

OCI provides functions to:

  • Access objects on the server using SQL

  • Access, manipulate and manage objects in the object cache by traversing pointers or REFs

  • Convert Oracle Database dates, strings, and numbers to C data types

  • Manage the size of the object cache's memory

  • Create transient type descriptions. Transient type descriptions are not stored persistently in the database.

OCI improves concurrency by allowing individual objects to be locked. It improves performance by supporting complex object retrieval.

OCI developers can use the object type translator to generate the C structure data types corresponding to Oracle Database object types.

Overview of Oracle C++ Call Interface (OCCI)

The Oracle C++ Call Interface (OCCI) is a C++ API that lets you use the object-oriented features, native classes, and methods of the C++ programing language to access Oracle Database. OCCI is built on top of OCI and combines its power and performance with the significantly more accessible interface of an object-oriented paradigm.

This section includes the following topics:

OCCI Associative Relational and Object Interfaces

The associative relational API and object classes provide SQL access to the database. Through these interfaces, SQL is run on the server to create, manipulate, and fetch object or relational data. Applications can access any dataype on the server, including: large objects, objects and structured types, arrays, and references.

OCCI Navigational Interface

The navigational interface is a C++ interface that lets you seamlessly access and modify object-relational data in the form of C++ objects without using SQL. The C++ objects are transparently accessed and stored in the database as needed.

With the OCCI navigational interface, you can retrieve an object and navigate through references from that object to other objects. Server objects are materialized as C++ class instances in the application cache. An application can use OCCI object navigational calls to perform the following functions on the server's objects:

  • Create, access, lock, delete, and flush objects

  • Get references to the objects and navigate through them

Overview of the Oracle Type Translator

The Oracle type translator (OTT) is a program that automatically generates C language structure declarations corresponding to object types. It generates C++ class definitions for Oracle Database object types that can be used by OCCI applications for a native C++ object interface. OTT uses the Pro*C/C++ precompiler and the OCI server access package.

Overview of Pro*C/C++ Precompiler

An Oracle precompiler is a programming tool that lets you embed SQL statements in a high-level source program. The precompiler accepts the host program as input, translates the embedded SQL statements into standard Oracle Database run-time library calls, and generates a source program that you can compile, link, and run in the usual way. Oracle precompilers are available (but not on all systems) for C, C++, COBOL, and FORTRAN.

The Oracle Pro*C/C++ Precompiler lets you 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 Database runtime library calls, and is then compiled by the C or C++ compiler.

Pro*C/C++ lets you create highly customized applications. For example, you can create user interfaces that incorporate the latest windowing and mouse technology. You can also create applications that run in the background without the need for user interaction.

Furthermore, Pro*C/C++ helps you fine-tune your applications. It allows close monitoring of resource use, SQL statement execution, and various runtime indicators. With this information, you can change program parameters for maximum performance.

Although precompiling adds a step to the application development process, it saves time. The precompiler, not you, translates each embedded SQL statement into calls to the Oracle Database runtime library (SQLLIB). The Pro*C/C++ precompiler also analyzes host variables, defines mappings of structures into columns, and, with SQLCHECK=FULL, performs semantic analysis of the embedded SQL statements.

The Oracle Pro*C/C++ precompiler also allows programmers to use object data types in C and C++ programs. Pro*C/C++ developers can use the Object Type Translator to map Oracle Database object types and collections into C data types to be used in the Pro*C/C++ application.

Pro*C/C++ developers can also call OCI functions from their programs.

Pro*C/C++ provides compile time type checking of object types and collections and automatic type conversion from database types to C data types. Pro*C/C++ includes an EXEC SQL syntax to create and destroy objects and offers two ways to access objects in the server:

  • SQL statements and PL/SQL functions or procedures embedded in Pro*C/C++ programs

  • A simple interface to the object cache, where objects can be accessed by traversing pointers, then modified and updated on the server

See Also:

Dynamic Creation and Access of Type Descriptions

Oracle provides a C API to enable dynamic creation and access of type descriptions. Additionally, you can create transient type descriptions, type descriptions that are not stored persistently in the database.

The C API enables creation and access of OCIAnyData and OCIAnyDataSet.

  • The OCIAnyData type models a self descriptive (with regard to type) data instance of a given type.

  • The OCIAnyDataSet type models a set of data instances of a given type.

Oracle also provides SQL data types (in Oracle's Open Type System) that correspond to these data types.

  • SYS.ANYTYPE corresponds to OCIType

  • SYS.ANYDATA corresponds to OCIAnyData

  • SYS.ANYDATASET corresponds to OCIAnyDataSet

You can create database table columns and SQL queries on such data.

The C API uses the following terms:

  • Transient types - Type descriptions (type metadata) that are not stored persistently in the database.

  • Persistent types - SQL types created using the CREATE TYPE SQL statement. Their type descriptions are stored persistently in the database.

  • Self-descriptive data - Data encapsulating type information along with the actual contents. The ANYDATA type (OCIAnyData) models such data. A data value of any SQL type can be converted to an ANYDATA, which can be converted back to the old data value. An incorrect conversion attempt results in an error.

  • Self-descriptive MultiSet - Encapsulation of a set of data instances (all of the same type), along with their type description.

Overview of PL/SQL

PL/SQL is the Oracle procedural language extension to SQL. It provides a server-side, stored procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure. The PL/SQL compiler and interpreter are embedded in Oracle Developer, providing developers with a consistent and leveraged development model on both the client and the server side. In addition, PL/SQL stored procedures can be called from a number of Oracle Database clients, such as Pro*C or Oracle Call Interface, and from Oracle Reports and Oracle Forms.

PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks and stored procedures.

An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.

A stored procedure is a PL/SQL block that Oracle Database stores in the database and can be called by name from an application. When you create a stored procedure, Oracle Database parses the procedure and stores its parsed representation in the database. Oracle Database also lets you create and store functions (which are similar to procedures) and packages (which are groups of procedures and functions).

This section includes the following topics:

How PL/SQL Runs

PL/SQL can run with either of the following:

Interpreted Execution

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

Native Execution

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

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

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

Figure 25-1 illustrates the PL/SQL engine contained in Oracle Database.

Figure 25-1 The PL/SQL Engine and Oracle Database

Description of Figure 25-1 follows
Description of "Figure 25-1 The PL/SQL Engine and Oracle Database"

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

The following Oracle products contain a PL/SQL engine:

  • Oracle Database

  • Oracle Forms (version 3 and later)

  • SQL*Menu (version 5 and later)

  • Oracle Reports (version 2 and later)

  • Oracle Graphics (version 2 and later)

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

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

  • Oracle precompilers (including user exits)

  • Oracle Call Interfaces (OCIs)

  • SQL*Plus

  • Oracle Enterprise Manager

Language Constructs for PL/SQL

PL/SQL blocks can include the following PL/SQL language constructs:

  • Variables and constants

  • Cursors

  • Exceptions

This section includes the following topics:

Variables and Constants

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

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


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


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

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

Dynamic SQL in PL/SQL

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

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

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

  • By using the DBMS_SQL package

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

See Also:

PL/SQL Program Units

Oracle Database lets you access and manipulate database information using procedural schema objects called PL/SQL program units. Procedures, functions, and packages are all examples of PL/SQL program units.

Stored Procedures and Functions

A procedure or function is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values. Procedures and functions let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.

Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not. For simplicity, procedure as used in the remainder of this chapter means procedure or function.

You can run a procedure or function interactively by:

  • Using an Oracle tool, such as SQL*Plus

  • Calling it explicitly in the code of a database application, such as an Oracle Forms or precompiler application

  • Calling it explicitly in the code of another procedure or trigger

See Also:

  • Pro*C/C++ Programmer's Guide for information about how to call stored C or C++ procedures

  • Pro*COBOL Programmer's Guide for information about how to call stored COBOL procedures

  • Other programmer's guides for information about how to call stored procedures of specific kinds of application

Figure 25-2 illustrates a simple procedure that is stored in the database and called by several different database applications.

Figure 25-2 Stored Procedure

Description of Figure 25-2 follows
Description of "Figure 25-2 Stored Procedure"

The following stored procedure example inserts an employee record into the employees table:

Procedure hire_employees (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, hire_date DATE, salary NUMBER, commission_pct NUMBER, department_id NUMBER)
INSERT INTO employees VALUES (emp_sequence.NEXTVAL, last_name, job_id, manager_id, hire_date, salary, commission_pct, department_id);

All of the database applications in this example call the hire_employees procedure. Alternatively, a privileged user can use Oracle Enterprise Manager or SQL*Plus to run the hire_employees procedure using a statement such as the following:

EXECUTE hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20); 

This statement places a new employee record for TSMITH in the employees table.

This section includes the following topics:

Benefits of Procedures

Stored procedures provide advantages in the following areas:

  • Security with definer's rights procedures

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

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

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

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

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

  • Improved performance

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

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

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

  • Memory allocation

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

  • Improved productivity

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

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

  • Integrity

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

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

Procedure Guidelines

Use the following guidelines when designing stored procedures:

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

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

Anonymous PL/SQL Blocks Compared with Stored Procedures

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

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

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

Standalone Procedures

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

See Also:

"PL/SQL Packages" for information about the advantages of packages

Dependency Tracking for Stored Procedures

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

See Also:

Chapter 6, "Schema Object Dependencies" for more information about dependency tracking

External Procedures

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

See Also:

Oracle Database Advanced Application Developer's Guide for more information about external procedures

Table Functions

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

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

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

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

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

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

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

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

PL/SQL Packages

A package is a group of related procedures and functions, along with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.

Oracle supplies many PL/SQL packages with Oracle Database to extend database functionality and provide PL/SQL access to SQL features. For example, the ULT_HTTP supplied package enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges. You can use the supplied packages when creating your applications or for ideas on creating your own stored procedures.

You create a package in two parts: the specification and the body. The package specification declares all public constructs of the package, and the body defines all constructs (public and private) of the package. The package body must be created in the same schema as the package. This separation of the two parts provides the following advantages:

  • You have more flexibility in the development cycle. You can create specifications and reference public procedures without actually creating the package body.

  • You can alter procedure bodies contained within the package body separately from their publicly declared specifications in the package specification. As long as the procedure specification does not change, objects that reference the altered procedures of the package are never marked invalid. That is, they are never marked as needing recompilation.


The package body and package specification always must be in the same schema.

Figure 25-3 illustrates a package that encapsulates a number of procedures used to manage an employee database.

Figure 25-3 A Stored Package

Description of Figure 25-3 follows
Description of "Figure 25-3 A Stored Package"

Database applications explicitly call packaged procedures as necessary. After being granted the privileges for the employees_management package, a user can explicitly run any of the procedures contained in it. For example, Oracle Enterprise Manager or SQL*Plus can issue the following statement to run the hire_employees package procedure:

EXECUTE employees_management.hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20); 

Benefits of Packages

Packages provide advantages in the following areas:

  • Encapsulation of related procedures and variables

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

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

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

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

    See Also:

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

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

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

PL/SQL Collections and Records

Many programming techniques use collection types such as arrays, bags, lists, nested tables, sets, and trees. To support these techniques in database applications, PL/SQL provides the datatypes TABLE and VARRAY, which allow you to declare index-by tables, nested tables, and variable-size arrays.

This section includes the following topics:


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

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


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

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

See Also:

Oracle Database PL/SQL Language Reference for detailed information on using collections and records

PL/SQL Server Pages

PL/SQL Server Pages (PSP) are server-side Web pages (in HTML or XML) with embedded PL/SQL scripts marked with special tags. To produce dynamic Web pages, developers have usually written CGI programs in C or Perl that fetch data and produce the entire Web page within the same program. The development and maintenance of such dynamic pages is costly and time-consuming.

Scripting fulfills the demand for rapid development of dynamic Web pages. Small scripts can be embedded in HTML pages without changing their basic HTML identity. The scripts contain the logic to produce the dynamic portions of HTML pages and are run when the pages are requested by the users.

The separation of HTML content from application logic makes script pages easier to develop, debug, and maintain. The simpler development model, along the fact that scripting languages usually demand less programming skill, enables Web page writers to develop dynamic Web pages.

There are two kinds of embedded scripts in HTML pages: client-side scripts and server-side scripts. Client-side scripts are returned as part of the HTML page and are run in the browser. They are mainly used for client-side navigation of HTML pages or data validation. Server-side scripts, while also embedded in the HTML pages, are run on the server side. They fetch and manipulate data and produce HTML content that is returned as part of the page. PSP scripts are server-side scripts.

A PL/SQL gateway receives HTTP requests from an HTTP client, invokes a PL/SQL stored procedure as specified in the URL, and returns the HTTP output to the client. A PL/SQL Server Page is processed by a PSP compiler, which compiles the page into a PL/SQL stored procedure. When the procedure is run by the gateway, it generates the Web page with dynamic content. PSP is built on one of two existing PL/SQL gateways:

  • PL/SQL cartridge of Oracle Application Server

  • WebDB

See Also:

Oracle Database Advanced Application Developer's Guide for more information about PL/SQL Server Pages

Overview of Java

Java is an object-oriented programming language efficient for application-level programs. It includes the following features:

This section contains the following topics:

Java and Object-Oriented Programming Terminology

This section covers some basic terminology of Java application development in the Oracle Database environment.

This section includes the following topics:


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

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

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

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

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

Figure 25-4 Classes and Instances

Description of Figure 25-4 follows
Description of "Figure 25-4 Classes and Instances"

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


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

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

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

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


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

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

Class Hierarchy

Java defines classes within a large hierarchy of classes. At the top of the hierarchy is the Object class. All classes in Java inherit from the Object class at some level, as you walk up through the inheritance chain of superclasses. When it is said that Class B inherits from Class A, each instance of Class B contains all the fields defined in class B, as well as all the fields defined in Class A. For example, in Figure 25-5, the FullTimeEmployee class contains the id and lastName fields defined in the Employee class, because it inherits from the Employee class. In addition, the FullTimeEmployee class adds another field, bonus, which is contained only within FullTimeEmployee.

You can invoke any method on an instance of Class B that was defined in either Class A or B. In our employee example, the FullTimeEmployee instance can invoke methods defined only within its own class, or methods defined within the Employee class.

Figure 25-5 Using Inheritance to Localize Behavior and State

Description of Figure 25-5 follows
Description of "Figure 25-5 Using Inheritance to Localize Behavior and State"

Instances of Class B are substitutable for instances of Class A, which makes inheritance another powerful construct of object-oriented languages for improving code reuse. You can create new classes that define behavior and state where it makes sense in the hierarchy, yet make use of pre-existing functionality in class libraries.


Java supports only single inheritance; that is, each class has one and only one class from which it inherits. If you must inherit from more than one source, Java provides the equivalent of multiple inheritance, without the complications and confusion that usually accompany it, through interfaces. Interfaces are similar to classes; however, interfaces define method signatures, not implementations. The methods are implemented in classes declared to implement an interface. Multiple inheritance occurs when a single class simultaneously supports many interfaces.


Assume in our Employee example that the different types of employees must be able to respond with their compensation to date. Compensation is computed differently for different kinds of employees.

  • FullTimeEmployees are eligible for a bonus

  • NonExemptEmployees get overtime pay

In traditional procedural languages, you would write a long switch statement, with the different possible cases defined.

switch (employee.type) {
case: Employee
return employee.salaryToDate;
case: FullTimeEmployee
return employee.salaryToDate + employee.bonusToDate;

If you add a new kind of employee, then you must update your switch statement. If you modify your data structure, then you must modify all switch statements that use it.

In an object-oriented language such as Java, you implement a method, compensationToDate, for each subclass of Employee class that requires any special treatment beyond what is already defined in Employee class. For example, you could implement the compensationToDate method of NonExemptEmployee, as follows:

private float compensationToDate() {
return super.compensationToDate() + this.overtimeToDate();

Implement FullTimeEmployee's method as follows:

private float compensationToDate() {
return super.compensationToDate() + this.bonusToDate();

The common usage of the method name compensationToDate lets you invoke the identical method on different classes and receive different results, without knowing the type of employee you are using. You do not have to write a special method to handle FullTimeEmployees and PartTimeEmployees. This ability for the different objects to respond to the identical message in different ways is known as polymorphism.

In addition, you could create an entirely new class that does not inherit from Employee at all—Contractor—and implement a compensationToDate method in it. A program that calculates total payroll to date would iterate over all people on payroll, regardless of whether they were full-time, part-time, or contractors, and add up the values returned from invoking the compensationToDate method on each. You can safely make changes to the individual compensationToDate methods with the knowledge that callers of the methods will work correctly. For example, you can safely add new fields to existing classes.

Overview of the Java Virtual Machine (JVM)

As with other high-level computer languages, Java source compiles to low-level instructions. In Java, these instructions are known as bytecodes (because their size is uniformly one byte of storage). Most other languages—such as C—compile to computer-specific instructions, such as instructions specific to an Intel or HP processor. Java source compiles to a standard, platform-independent set of bytecodes, which interacts with a Java Virtual Machine (JVM). The JVM is a separate program that is optimized for the specific platform on which you run your Java code.

Figure 25-6 illustrates how Java can maintain platform independence. Java source is compiled into bytecodes, which are platform independent. Each platform has installed a JVM that is specific to its operating system. The Java bytecodes from your source get interpreted through the JVM into appropriate platform dependent actions.

Figure 25-6 Java Component Structure

Description of Figure 25-6 follows
Description of "Figure 25-6 Java Component Structure"

When you develop a Java program, you use predefined core class libraries written in the Java language. The Java core class libraries are logically divided into packages that provide commonly-used functionality, such as basic language support (java.lang), I/O (, and network access ( Together, the JVM and core class libraries provide a platform on which Java programmers can develop with the confidence that any hardware and operating system that supports Java will execute their program. This concept is what drives the "write once, run anywhere" idea of Java.

Figure 25-7 illustrates how Oracle's Java applications sit on top of the Java core class libraries, which in turn sit on top of the JVM. Because Oracle's Java support system is located within the database, the JVM interacts with Oracle Database libraries, instead of directly with the operating system.

Figure 25-7 Java Component Structure

Description of Figure 25-7 follows
Description of "Figure 25-7 Java Component Structure"

Sun Microsystems furnishes publicly available specifications for both the Java language and the JVM. The Java Language Specification (JLS) defines things such as syntax and semantics; the JVM specification defines the necessary low-level behavior for the computer that runs the bytecodes. In addition, Sun Microsystems provides a compatibility test suite for JVM implementors to determine if they have complied with the specifications. This test suite is known as the Java Compatibility Kit (JCK). Oracle's JVM implementation complies fully with JCK. Part of the overall Java strategy is that an openly specified standard, together with a simple way to verify compliance with that standard, allows vendors to offer uniform support for Java across all platforms.

Why Use Java in Oracle Database?

You can write and load Java applications within the database, because it is a safe language. Java prevents anyone from tampering with the operating system that the Java code resides in. Some languages, such as C, can introduce security problems within the database; Java, because of its design, is a safe language to allow within the database.

Although Java presents many advantages to developers, providing an implementation of a JVM that supports Java server applications in a scalable manner is a challenge. This section discusses some of these challenges.


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

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

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

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

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

Automated Storage Management

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

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

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

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

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


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

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

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

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

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

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

  • Generational scavenging for short-lived objects

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

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


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

Table 25-1 describes two methods for native compilation.

Table 25-1 Native Compilation Methods

Method Description

Just-In-Time (JIT) Compilation

JIT compilers quickly compile Java bytecodes to native (platform-specific) computer code during runtime. This does not produce an executable to be run on the platform; instead, it provides platform-dependent code from Java bytecodes that is run directly after it is translated. This should be used for Java code that is run frequently, which will be run at speeds closer to languages such as C.

Static Compilation

Static compilation translates Java bytecodes to platform-independent C code before runtime. Then a standard C compiler compiles the C code into an executable for the target platform. This approach is more suitable for Java applications that are modified infrequently. This approach takes advantage of the mature and efficient platform-specific compilation technology found in modern C compilers.

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

Dynamic Class Loading

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

Table 25-2 Problems Associated with Dynamic Class Loading

Problem Description Solution


The class loading operation places a severe penalty on first-time execution. A simple program can cause the Oracle JVM to load many core classes to support its needs. A programmer cannot easily predict or determine the number of classes loaded.

The Oracle JVM loads classes dynamically, just as with any other Java Virtual Machine. The same one-time class loading speed hit is encountered. However, because the classes are loaded into shared memory, no other users of those classes will cause the classes to load again—they will simply use the same pre-loaded classes.


A benefit of dynamic class loading is that it supports program updating. For example, you would update classes on a server, and clients who download the program and load it dynamically see the update whenever they next use the program. Server programs tend to emphasize reliability. As a developer, you must know that every client runs a specific program configuration. You do not want clients to inadvertently load some classes that you did not intend them to load.

Oracle Database separates the upload and resolve operation from the class loading operation at runtime. You upload Java code you developed to the server using the loadjava utility. Instead of using CLASSPATH, you specify a resolver at installation time. The resolver is analogous to CLASSPATH, but lets you specify the schemas in which the classes reside. This separation of resolution from class loading means you always know what program users run.

Oracle's Java Application Strategy

One appeal of Java is its ubiquity and the growing number of programmers capable of developing applications using it. Oracle furnishes enterprise application developers with an end-to-end Java solution for creating, deploying, and managing Java applications. The total solution consists of client-side and server-side programmatic interfaces, tools to support Java development, and a Java Virtual Machine integrated with Oracle Database. All these products are compatible with Java standards.

In addition to the Oracle JVM, the Java programming environment consists of the following:

  • Java stored procedures as the Java equivalent and companion for PL/SQL. Java stored procedures are tightly integrated with PL/SQL. You can call a Java stored procedure from a PL/SQL package; you can call PL/SQL procedures from a Java stored procedure.

  • SQL data can be accessed through the JDBC programming interface.

  • Tools and scripts used in assisting in development, class loading, and class management.

This section includes the following topics:

Java Stored Procedures

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

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

See Also:

Oracle Database Java Developer's Guide explains how to write stored procedures in Java, how to access them from PL/SQL, and how to access PL/SQL functionality from Java.

PL/SQL Integration and Oracle Database Functionality

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


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

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

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

Table 25-3 JDBC Drivers

Driver Description

JDBC Thin Driver

You can use the JDBC Thin driver to write 100% pure Java applications and applets that access Oracle SQL data. The JDBC Thin driver is especially well-suited to Web browser-based applications and applets, because you can dynamically download it from a Web page just like any other Java applet.

JDBC Oracle Call Interface Driver

The JDBC Oracle Call Interface (OCI) driver accesses Oracle-specific native code (that is, non-Java) libraries on the client or middle tier, providing a richer set of functionality and some performance boost compared to the JDBC Thin driver, at the cost of significantly larger size and client-side installation.

JDBC Server-side Internal Driver

Oracle Database uses the server-side internal driver when Java code runs on the server. It allows Java applications running in the server's JVM to access locally defined data (that is, on the same computer and in the same process) with JDBC. It provides a further performance boost because of its ability to use underlying Oracle RDBMS libraries directly, without the overhead of an intervening network connection between your Java code and SQL data. By supporting the same Java-SQL interface on the server, Oracle Database does not require you to rework code when deploying it.


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

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

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

  • Make the static fields of a class visible in SQL

  • Allow the user to call a Java constructor

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


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

  • SQL object types

  • Object reference types ("REF types")

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

  • PL/SQL packages

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

Java Messaging Service

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

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

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

  • Point-to-point communication using queues

  • Publish-subscribe communication using topics

  • Synchronous and asynchronous message exchange

  • Subject-based routing

Oracle Streams also provides extensions to the standard JMS features:

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

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

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

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

  • Message retention after message is consumed

  • Exception handling

  • Delay specification before a message is visible

Overview of Microsoft Programming Languages

Oracle offers a variety of data access methods from COM-based programming languages, such as Visual Basic and Active Server Pages. These include Oracle Objects for OLE (OO40) and the Oracle Provider for OLE DB. The latter can be used with Microsoft's ActiveX Data Objects (ADO). Server-side programming to COM Automation servers, such as Microsoft Office, is available through the COM Automation Feature. More traditional ODBC access is available through Oracle's ODBC Driver. C/C++ applications can also use the Oracle Call Interface (OCI). These data access drivers have been engineered to provide superior performance with Oracle Database and expose the database's advanced features which may not be available in third-party drivers.

Oracle also provides optimum .NET data access support through the Oracle Data Provider for .NET, allowing .NET to access advanced Oracle features. Oracle also supports OLE DB .NET and ODBC .NET.

This section includes the following topics:

Open Database Connectivity

Open database connectivity (ODBC), is a database access protocol that lets you connect to a database and then prepare and run SQL statements against the database. In conjunction with an ODBC driver, an application can access any data source including data stored in spreadsheets, like Excel. Because ODBC is a widely accepted standard API, applications can be written to comply to the ODBC standard. The ODBC driver performs all mappings between the ODBC standard and the particular database the application is accessing. Using a data source-specific driver, an ODBC compliant program can access any data source without any more development effort.

Oracle provides the ODBC interface so that applications of any type that are ODBC compliant can access Oracle Database using the ODBC driver provided by Oracle. For example, an application written in Visual Basic can use ODBC to access Oracle Database.

Overview of Oracle Objects for OLE

Oracle Objects for OLE (OO4O) allows easy access to data stored in Oracle Databases 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.

OO4O consists of the following software layers:

OO4O Automation Server

The OO4O Automation Server is a set of COM Automation objects for connecting to Oracle Database servers, executing SQL statements and PL/SQL blocks, and accessing the results.

OO4O provides key features for accessing Oracle Databases 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.

Oracle Data Control

The Oracle Data Control (ODC) is an ActiveX Control 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 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 runs and manages the results of database queries.

The Oracle Objects for OLE C++ Class Library

The 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 writing COM client code for accessing the OO4O interfaces.

See Also:

Oracle Objects for OLE C++ Class Library help from the Start menu

Oracle Data Provider for .NET

Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for Oracle Database. ODP.NET uses Oracle native APIs for fast and reliable access to Oracle Database data and features from any .NET application. ODP.NET also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library.

Following the .NET Framework, ODP.NET uses the ADO.NET model, which allows native providers to expose provider-specific features and data types.

Using ODP.NET, developers can write programs in Visual Basic .NET, C#, and other .NET languages.

Overview of Legacy Languages

This section contains the following topics:

Overview of Pro*COBOL Precompiler

The Pro*COBOL Precompiler is a programming tool that lets you embed SQL statements in a host COBOL program. Pro*COBOL reads the source file as input and outputs a COBOL source file that replaces the embedded SQL statements with Oracle runtime library calls, and is then compiled by the COBOL compiler.

Like the Pro*C/C++ Precompiler, Pro*COBOL lets you create highly customized applications. For example, you can create user interfaces that incorporate the latest windowing and mouse technology. You can also create applications that run in the background without the need for user interaction.

Furthermore, with Pro*COBOL you can fine-tune your applications. It enables close monitoring of resource usage, SQL statement execution, and various run-time indicators. With this information, you can adjust program parameters for maximum performance.

Overview of Pro*FORTRAN Precompiler

The Oracle Pro*FORTRAN Precompiler lets you embed SQL in a host FORTRAN program.

Pro*FORTRAN is not supported on Windows.