1

Introduction and New Features

This chapter introduces you to the Oracle Call Interface, Release 8.0. It gives you background information that you need to develop applications using the OCI. It also introduces special terms that are used in discussing the OCI.

This chapter also discusses the changes in the OCI since release 7.3.

The following topics are covered:

• The Oracle Call Interface
• SQL Statements
• Special OCI/SQL Terms
• Object Support in the OCI
• Parts of the OCI
• Release 8.0 New Features
• Obsolescent and Obsolete OCI Calls
• Compiling and Linking

The Oracle Call Interface

Structured Query Language (SQL) is a nonprocedural language. A program in a nonprocedural language specifies the set of data to be operated on, but does not specify precisely what operations will be performed, or how the operations are to be carried out. The nonprocedural 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.

However, most programming languages, such as C and C++ are procedural. 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 Oracle Call Interface (OCI) allows you to develop applications that combine the nonprocedural data access power of SQL with the procedural capabilities of C. The OCI supports all SQL data definition, data manipulation, query, and transaction control facilities that are available through an Oracle8 server.

You can also take advantage of PL/SQL, Oracle's procedural extension to SQL. Thus, 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 an Oracle8 server.

The OCI is an application programming interface (API) that allows you to manipulate data and schemas in an Oracle database. As Figure 1-1 shows, you compile and link an OCI program in the same way that you compile and link a nondatabase application. There is no need for a separate preprocessing or precompilation step.

Figure 1-1 The OCI Development Process

Note: On some platforms, it may be necessary to include other libraries, in addition to the OCI library, to properly link your OCI programs. Check your Oracle system-specific documentation for further information about extra libraries that may be required.

SQL Statements

One of the main tasks of an OCI application is to process SQL statements. Different types of SQL statements require different processing steps in your program. It is important to take this into account when coding your OCI application.

Oracle8 recognizes eight kinds of SQL statements:

• Data Definition Language
• Control Statements (3 types)
  • Transaction Control
  • Session Control
  • System Control
• Data Manipulation Language (DML)
• Queries

  Note: Queries are often classified as DML statements, but OCI applications process queries differently, so they are considered separately here.

• PL/SQL
• Embedded SQL

Data Definition Language

Data Definition Language (DDL) statements manage schema objects in the database. DDL statements create new tables, drop old tables, and establish other schema objects. They also control access to schema objects. For example:

CREATE TABLE employees
    (name               VARCHAR2(20),
          ssn                  VARCHAR2(12),
          empno              NUMBER(6),
          mgr                  NUMBER(6),
          salary            NUMBER(6))

GRANT UPDATE, INSERT, DELETE ON employees TO donna
REVOKE UPDATE ON employees FROM jamie

DDL statements also allow you to work with objects in the Oracle8 server, as in the following series of statements which creates an object table:

CREATE TYPE person_t AS OBJECT (
        name                VARCHAR2(30),
        ssn                  VARCHAR2(12),
        address          VARCHAR2(50))

CREATE TABLE person_tab OF person_t

Control Statements

OCI applications treat transaction control, session control, and system control statements like DML statements. See the Oracle8 SQL Reference for information about these types of statements.

Data Manipulation Language

Data manipulation language (DML) statements can change data in the database tables. For example, DML statements are used to

• INSERT new rows into a table
• UPDATE column values in existing rows
• DELETE rows from a table
• LOCK a table in the database
• EXPLAIN the execution plan for a SQL statement

DML statements can require an application to supply data to the database using input (bind) variables. See the section "Binding" on page 4-5 for more information about input bind variables.

DML statements also allow you to work with objects in the Oracle8 server, as in the following example, which inserts an instance of type person_t into the object table person_tab:

INSERT INTO person_tab
        VALUES (person_t('Steve May','123-45-6789','146 Winfield Street'))

Queries

Queries are statements that retrieve data from a database. A query can return zero, one, or many rows of data. All queries begin with the SQL keyword SELECT, as in the following example:

SELECT dname FROM dept
        WHERE deptno = 42

Queries access data in tables, and they are often classified with DML statements. However, OCI applications process queries differently, so they are considered separately in this guide.

Queries can require the program to supply data to the database using input (bind) variables, as in the following example:

SELECT name 
FROM employees
WHERE empno = :empnumber

In the above SQL statement, :empnumber is a placeholder for a value that will be supplied by the application.

When processing a query, an OCI application also needs to define output variables to receive the returned results. In the above statement, you would need to define an output variable to receive any name values returned from the query.

See Also: See the section "Binding" on page 5-2 for more information about input bind variables.

See the section "Defining" on page 5-13 for information about defining output variables.

See Chapter 4, "SQL Statement Processing", for detailed information about how SQL statements are processed in an OCI program.

PL/SQL

PL/SQL is Oracle's procedural extension to the SQL language. PL/SQL processes tasks that are more complicated than simple queries and SQL Data manipulation language statements. PL/SQL allows a number of constructs to be grouped into a single block and executed as a unit. Among these are:

• one or more SQL statements
• variable declarations
• assignment statements
• procedural control statements (IF...THEN...ELSE statements and loops)
• exception handling

You can use PL/SQL blocks in your OCI program to

• call Oracle stored procedures and stored functions
• combine procedural control statements with several SQL statements, to be executed as a single unit
• access special PL/SQL features such as records, tables, cursor FOR loops, and exception handling
• use cursor variables
• access and manipulate objects in an Oracle8 server

The following PL/SQL example issues a SQL statement to retrieve values from a table of employees, given a particular employee number. This example also demonstrates the use of placeholders in PL/SQL statements.

BEGIN
        SELECT ename, sal, comm INTO :emp_name, :salary, :commission
        FROM emp
        WHERE ename = :emp_number;
END;

Keep in mind that the placeholders in this statement are not PL/SQL variables. They represent input values passed to Oracle when the statement is processed. These placeholders need to be bound to C language variables in your program.

See Also: See the PL/SQL User's Guide and Reference for information about coding PL/SQL blocks.

See the section "Binding Placeholders in PL/SQL" on page 5-5 for information about working with placeholders in PL/SQL.

Embedded SQL

The OCI processes SQL statements as text strings, which an application passes to Oracle on execution. The Oracle precompilers (Pro*C/C++, Pro*COBOL, Pro*FORTRAN) allow programmers to embed SQL statements directly into their application code. A separate precompilation step is then necessary to generate an executable application.

It is possible to mix OCI calls and embedded SQL in a precompiler program. Refer to the Pro*COBOL Precompiler Programmer's Guide for more information.

Special OCI/SQL Terms

This guide uses special terms to refer to the different parts of a SQL statement. For example, a SQL statement such as

SELECT customer, address
		FROM customers
		WHERE bus_type = 'SOFTWARE'
		AND sales_volume = :sales

contains the following parts:

• a SQL command - SELECT
• two select-list items - customer and address
• a table name in the FROM clause - customers
• two column names in the WHERE clause - bus_type and sales_volume
• a literal input value in the WHERE clause - `SOFTWARE'
• a placeholder for an input variable in the second part of the WHERE clause - :sales

When you develop your OCI application, you call routines that specify to the Oracle8 server the address (location) of input and output variables in your program. In this guide, specifying the address of a placeholder variable for data input is called a bind operation. Specifying the address of a variable to receive select-list items is called a define operation.

For PL/SQL, both input and output specifications are called bind operations.

These terms and operations are described in detail in Chapter 4.

Object Support in the OCI

With Release 8.0, the Oracle server has facilities for working with object types and objects. An object type is a user-defined data structure representing an abstraction of a real-world entity. For example, the database might contain a definition of a person object. That object might have attributes-first_name, last_name, and age-which represent a person's identifying characteristics.

The object type definition serves as the basis for creating objects, which represent instances of the object type. Using the object type as a structural definition, a person object could be created with the attributes `John', `Bonivento', and `30'.

Object types may also contain methods-programmatic functions that represent the behavior of that object type.

See Also: For a more detailed explanation of object types and objects, see Oracle8 Concepts.

The Oracle8 OCI includes functions that extend the capabilities of the OCI to handle objects in an Oracle8 server. Specifically, the following capabilities have been added to the OCI:

• support for execution of SQL statements that manipulate object data and schema information
• support for passing object references and instances as input variables in SQL statements.
• support for declaring object references and instances as variables to receive the output of SQL statements
• support for fetching object references and instances from a database
• support for describing the properties of SQL statements that return object instances and references
• support for describing PL/SQL procedures or functions with object parameters or results
• commit and rollback calls have been extended to synchronize object and relational functionality

Additional OCI calls are provided to support manipulation of objects after they have been accessed by way of SQL statements.

Note: For a more detailed description of enhancements and new features, please refer to Appendix F, "Oracle8 OCI New Features".

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 Oracle8 server
• OCI datatype mapping and manipulation functions, for manipulating data attributes of Oracle8 types
• OCI external procedure functions, which are used for writing C callbacks from PL/SQL

These terms are used throughout this guide.

Release 8.0 New Features

The Oracle8 OCI provides a wide range of new features and functions. All calls available in Release 7.3 are still supported, but they are not able to take full advantage of new Oracle8 features.

Note: For a more detailed description of enhancements and new features, please refer to Appendix F, "Oracle8 OCI New Features".

Release 8.0 has the following new features and performance advantages:

• increased client-side processing and reduced server-side requirements
• implicit prefetching of SELECT statement result set rows
• API access to both objects and relational data
• reduction of the number of network round trips
• the ability to handle LOB columns
• a set of API calls for performing operations on LOBs and FILEs
• improved national language support (NLS) capabilities
• a migration path for existing OCI applications, and some ability to mix old and new calls within a single application
• improved support for multithreaded environments
• additional functionality to provide navigational access to objects in an Oracle8 server

Each of these features is discussed in greater detail in later chapters of this guide.

Release 8.0 of the OCI contains an entirely new set of API calls that replace those used in earlier releases. Additionally, new calls are included to provide functionality not available in earlier releases.

See Also: See the section "Obsolescent OCI Routines" on page A-2 for information about new calls that supersede existing routines.

See Chapters 13, 14, 15, and 16 for complete listings of all OCI calls.

Obsolescent and Obsolete OCI Calls

Refer to Appendix A for lists of OCI calls that are now considered to be obsolescent or obsolete.

Compiling and Linking

Oracle Corporation supports most popular third-party compilers. The details of linking an OCI program vary from system to system. See your Oracle system-specific documentation and the installation guide for more information about compiling and linking an OCI application for your specific platform.

---