4 Object Support in Oracle Programming Environments

Associative Access in OCI Programs

Traditionally, 3GL programs manipulate data stored in a relational database by executing SQL statements and PL/SQL procedures. Data is usually manipulated on the server without incurring the cost of transporting the data to the client(s). OCI supports this associative access to objects by providing an API for executing SQL statements that manipulate object data. Specifically, OCI enables you to:

• Execute SQL statements that manipulate object data and object type schema information

• Pass object instances, object references (REFs), and collections as input variables in SQL statements

• Return object instances, REFs, and collections as output of SQL statement fetches

• Describe the properties of SQL statements that return object instances, REFs, and collections

• Describe and execute PL/SQL procedures or functions with object parameters or results

• Synchronize object and relational functionality through enhanced commit and rollback functions

See "Associative Access in Pro*C/C++".

Navigational Access in OCI Programs

In the object-oriented programming paradigm, applications model their real-world entities as a set of inter-related objects that form graphs of objects. The relationships between objects are implemented as references. An application processes objects by starting at some initial set of objects, using the references in these initial objects to traverse the remaining objects, and performing computations on each object. OCI provides an API for this style of access to objects, known as navigational access. Specifically, OCI enables you to:

• Cache objects in memory on the client machine

• Dereference an object reference and pin the corresponding object in the object cache. The pinned object is transparently mapped in the host language representation.

• Notify the cache when the pinned object is no longer needed

• Fetch a graph of related objects from the database into the client cache in one call

• Lock objects

• Create, update, and delete objects in the cache

• Flush changes made to objects in the client cache to the database

See "Navigational Access in Pro*C/C++".

Object Cache

To support high-performance navigational access of objects, OCI runtime provides an object cache for caching objects in memory. The object cache supports references (REFs) to database objects in the object cache, the database objects can be identified (that is, pinned) through their references. Applications do not need to allocate or free memory when database objects are loaded into the cache, because the object cache provides transparent and efficient memory management for database objects.

Also, when database objects are loaded into the cache, they are transparently mapped into the host language representation. For example, in the C programming language, the database object is mapped to its corresponding C structure. The object cache maintains the association between the object copy in the cache and the corresponding database object. Upon transaction commit, changes made to the object copy in the cache are propagated automatically to the database.

The object cache maintains a fast look-up table for mapping REFs to objects. When an application dereferences a REF and the corresponding object is not yet cached in the object cache, the object cache automatically sends a request to the server to fetch the object from the database and load it into the object cache. Subsequent dereferences of the same REF are faster because they become local cache access and do not incur network round-trips. To notify the object cache that an application is accessing an object in the cache, the application pins the object; when it is finished with the object, it unpins it. The object cache maintains a pin count for each object in the cache. The count is incremented upon a pin call and decremented upon an unpin call. When the pin count goes to zero, it means the object is no longer needed by the application. The object cache uses a least-recently used (LRU) algorithm to manage the size of the cache. When the cache reaches the maximum size, the LRU algorithm frees candidate objects with a pin count of zero.

Building an OCI Program That Manipulates Objects

When you build an OCI program that manipulates objects, you must complete the following general steps:

1. Define the object types that correspond to the application objects.

2. Execute the SQL DDL statements to populate the database with the necessary object types.

3. Represent the object types in the host language format.

   For example, to manipulate instances of the object types in a C program, you must represent these types in the C host language format. You can do this by representing the object types as C structs. You can use a tool provided by Oracle called the Object Type Translator (OTT) to generate the C mapping of the object types. The OTT puts the equivalent C structs in header (*.h) files. You include these *.h files in the *.c files containing the C functions that implement the application.

4. Construct the application executable by compiling and linking the application's *.c files with the OCI library.

   See Also:

   Oracle Call Interface Programmer's Guide for tips and techniques for using OCI program effectively with objects

Defining User-Defined Constructors in C

When defining a user-defined constructor in C, you must specify SELF (and you may optionally specify SELF TDO) in the PARAMETERS clause. On entering the C function, the attributes of the C structure that the object maps to are all initialized to NULL. The value returned by the function is mapped to an instance of the user-defined type. Example 4-1 shows how to define a user-defined constructor in C.

CREATE LIBRARY person_lib TRUSTED AS STATIC
/

CREATE TYPE person AS OBJECT
  (  name VARCHAR2(30),
     CONSTRUCTOR FUNCTION person(SELF IN OUT NOCOPY person, name VARCHAR2) 
         RETURN SELF AS RESULT);
/

CREATE TYPE BODY person IS
    CONSTRUCTOR FUNCTION person(SELF IN OUT NOCOPY person, name VARCHAR2) 
         RETURN SELF AS RESULT
    IS EXTERNAL NAME "cons_person_typ" LIBRARY person_lib WITH CONTEXT
    PARAMETERS(context, SELF, name OCIString, name INDICATOR sb4); 
END;/

The SELF parameter is mapped like an IN parameter, so in the case of a NOT FINAL type, it is mapped to (dvoid *), not (dvoid **).

The return value's TDO must match the TDO of SELF and is therefore implicit. The return value can never be null, so the return indicator is implicit as well.

Associative Access in Pro*C/C++

For background information on associative access, see "Associative Access in OCI Programs".

Pro*C/C++ offers the following capabilities for associative access to objects:

• Support for transient copies of objects allocated in the object cache

• Support for transient copies of objects referenced as input host variables in embedded SQL INSERT, UPDATE, and DELETE statements, or in the WHERE clause of a SELECT statement

• Support for transient copies of objects referenced as output host variables in embedded SQL SELECT and FETCH statements

• Support for ANSI dynamic SQL statements that reference object types through the DESCRIBE statement, to get the object's type and schema information

Navigational Access in Pro*C/C++

For background information on navigational access, see "Navigational Access in OCI Programs".

Pro*C/C++ offers the following capabilities to support a more object-oriented interface to objects:

• Support for dereferencing, pinning, and optionally locking an object in the object cache using an embedded SQL OBJECT DEREF statement

• Allowing a Pro*C/C++ user to inform the object cache when an object has been updated or deleted, or when it is no longer needed, using embedded SQL OBJECT UPDATE, OBJECT DELETE, and OBJECT RELEASE statements

• Support for creating new referenceable objects in the object cache using an embedded SQL OBJECT CREATE statement

• Support for flushing changes made in the object cache to the server with an embedded SQL OBJECT FLUSH statement

Converting Between Oracle Types and C Types

The C representation for objects that is generated by the Oracle Type Translator (OTT) uses OCI types whose internal details are hidden, such as OCIString and OCINumber for scalar attributes. Collection types and object references are similarly represented using OCITable, OCIArray, and OCIRef types. While using these opaque types insulates you from changes to their internal formats, using such types in a C or C++ application is cumbersome. Pro*C/C++ provides the following ease-of-use enhancements to simplify use of OCI types in C and C++ applications:

• Object attributes can be retrieved and implicitly converted to C types with the embedded SQL OBJECT GET statement.

• Object attributes can be set and converted from C types with the embedded SQL OBJECT SET statement.

• Collections can be mapped to a host array with the embedded SQL COLLECTION GET statement. Furthermore, if the collection is comprised of scalar types, then the OCI types can be implicitly converted to a compatible C type.

• Host arrays can be used to update the elements of a collection with the embedded SQL COLLECTION SET statement. As with the COLLECTION GET statement, if the collection is comprised of scalar types, C types are implicitly converted to OCI types.

Oracle Type Translator (OTT)

The Oracle Type Translator (OTT) is a program that automatically generates C language structure declarations corresponding to object types. OTT makes it easier to use the Pro*C precompiler and the OCI server access package.

OCCI Associative Relational and Object Interfaces

The associative relational API and object classes provide SQL access to the database. Through these interfaces, SQL is executed on the server to create, manipulate, and fetch object or relational data. Applications can access any data type on the server, including the following:

• Large objects

• Objects/Structured types

• Arrays

• References

The 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

  See Also:

  Oracle C++ Call Interface Programmer's Guide for a complete account of how to build applications with the Oracle C++ API

Representing Objects in Visual Basic (OraObject)

The OraObject interface is a representation of an Oracle embedded object or a value instance. It contains a collection interface (OraAttributes) for accessing and manipulating (updating and inserting) individual attributes of a value instance. Individual attributes of an OraAttributes collection interface can be accessed by using a subscript or the name of the attribute.

The following Visual Basic example illustrates how to access attributes of the Address object in the person_tab table:

Representing REFs in Visual Basic (OraRef)

The OraRef interface represents an Oracle object reference (REF) as well as referenceable objects in client applications. The object attributes are accessed in the same manner as attributes of an object represented by the OraObject interface. OraRef is derived from an OraObject interface by means of the containment mechanism in COM. REF objects are updated and deleted independent of the context they originated from, such as dynasets. The OraRef interface also encapsulates the functionality for navigating through graphs of objects utilizing the Complex Object Retrieval Capability (COR) in OCI.

Representing VARRAYs and Nested Tables in Visual Basic (OraCollection)

The OraCollection interface provides methods for accessing and manipulating Oracle collection types, namely variable-length arrays (VARRAYs) and nested tables in OO4O. Elements contained in a collection are accessed by subscripts.

The following Visual Basic example illustrates how to access attributes of the EnameList object from the department table:

JDBC Access to Oracle Object Data

JDBC (Java Database Connectivity) is a set of Java interfaces to the Oracle server. Oracle provides tight integration between objects and JDBC. You can map SQL types to Java classes with considerable flexibility.

Oracle JDBC:

• Allows access to objects and collection types (defined in the database) in Java programs through dynamic SQL.

• Translates types defined in the database into Java classes through default or customizable mappings.

Version 2.0 of the JDBC specification supports object-relational constructs such as user-defined (object) types. JDBC materializes Oracle objects as instances of particular Java classes. Using JDBC to access Oracle objects involves creating the Java classes for the Oracle objects and populating these classes. You can either:

• Let JDBC materialize the object as a STRUCT. In this case, JDBC creates the classes for the attributes and populates them for you.

• Manually specify the mappings between Oracle objects and Java classes; that is, customize your Java classes for object data. The driver then populates the customized Java classes that you specify, which imposes a set of constraints on the Java classes. To satisfy these constraints, you can choose to define your classes according to either the SQLData interface or the ORAData interface.

  See Also:

  For complete information about JDBC, see the Oracle Database JDBC Developer's Guide and Reference.

SQLJ Access to Oracle Object Data

SQLJ provides access to server objects using SQL statements embedded in the Java code:

• You can use user-defined types in Java programs.

• You can use JPublisher to map Oracle object and collection types into Java classes to be used in the application.

• The object types and collections in the SQL statements are checked at compile time.

  See Also:

  For complete information about SQLJ, see the Oracle Database Java Developer's Guide.

Choosing a Data Mapping Strategy

Oracle SQLJ supports either strongly typed or weakly typed Java representations of object types, reference types (REFs), and collection types (varrays and nested tables) to be used in iterators or host expressions.

Strongly typed representations use a custom Java class that corresponds to a particular object type, REF type, or collection type and must implement the interface oracle.sql.ORAData. The Oracle JPublisher utility can automatically generate such custom Java classes.

Weakly typed representations use the class oracle.sql.STRUCT (for objects), oracle.sql.REF (for references), or oracle.sql.ARRAY (for collections).

JPublisher

Oracle JPublisher is a utility that generates Java classes to represent the following user-defined database entities in your Java program:

• Database object types

• Database reference (REF) types

• Database collection types (varrays or nested tables)

• PL/SQL packages

JPublisher enables you to specify and customize the mapping of database object types, reference types, and collection types (varrays or nested tables) to Java classes, in a strongly typed paradigm.

Java Object Storage

JPublisher enables you to construct Java classes that map to existing SQL types. You can then access the SQL types from a Java application using JDBC.

You can also go in the other direction. That is, you can create SQL types that map to existing Java classes. This capability enables you to provide persistent storage for Java objects. Such SQL types are called SQL types of Language Java, or SQLJ object types. They can be used as the type of an object, an attribute, a column, or a row in an object table. You can navigationally access objects of such types—Java objects—through either object references or foreign keys, and you can query and manipulate such objects from SQL.

You create SQLJ types with a CREATE TYPE statement as you do other user-defined SQL types. For SQLJ types, two special elements are added to the CREATE TYPE statement:

• An EXTERNAL NAME phrase, used to identify the Java counterpart for each SQLJ attribute and method and the Java class corresponding to the SQLJ type itself

• A USING clause, to specify how the SQLJ type is to be represented to the server. The USING clause specifies the interface used to retrieve a SQLJ type and the kind of storage.

For example:

-- Mapping SQL Types to Java Classes example, not sample schema
CREATE TYPE full_address AS OBJECT (a NUMBER);
/

CREATE OR REPLACE TYPE person_t AS OBJECT
  EXTERNAL NAME 'Person' LANGUAGE JAVA
  USING SQLData (
    ss_no NUMBER (9) EXTERNAL NAME 'socialSecurityNo',
    name varchar(100) EXTERNAL NAME 'name',
    address full_address EXTERNAL NAME 'addrs',
    birth_date date EXTERNAL NAME 'birthDate',
    MEMBER FUNCTION age  RETURN NUMBER EXTERNAL NAME 'age () return int',
    MEMBER FUNCTION addressf RETURN full_address
      EXTERNAL NAME 'get_address () return long_address',
    STATIC function createf RETURN person_t EXTERNAL NAME 'create () 
         return Person',
    STATIC function createf (name VARCHAR2, addrs full_address, bDate DATE)
      RETURN person_t EXTERNAL NAME 'create (java.lang.String, Long_address,
      oracle.sql.date) return Person',
    ORDER member FUNCTION compare (in_person person_t) RETURN NUMBER
      EXTERNAL NAME 'isSame (Person) return int')
/

SQLJ types use the corresponding Java class as the body of the type; you do not specify a type body in SQL to contain implementations of the type's methods as you do with ordinary object types.

Defining User-Defined Constructors in Java

When you implement a user-defined constructor in Java, the string supplied as the implementing routine must correspond to a static function. For the return type of the function, specify the Java type mapped to the SQL type.

Example 4-3 is an example of a type declaration that involves a user-defined constructor implemented in Java.

-- Defining a User-Defined Constructor in Java example, not sample schema
CREATE TYPE person1_typ AS OBJECT 
 EXTERNAL NAME 'pkg1.J_Person' LANGUAGE JAVA 
 USING SQLData( 
  name VARCHAR2(30), 
  age NUMBER,
  CONSTRUCTOR FUNCTION person1_typ(SELF IN OUT NOCOPY person1_typ, name VARCHAR2,
                                   age NUMBER) RETURN SELF AS RESULT
  AS LANGUAGE JAVA 
    NAME 'pkg1.J_Person.J_Person(java.lang.String, int) return J_Person')
/

DROP TYPE person1_typ FORCE;
DROP TYPE person_t FORCE;
DROP TYPE full_address FORCE;
DROP TYPE person FORCE;
DROP LIBRARY person_lib;
SPOOL OFF
COMMIT;

JDeveloper

Oracle JDeveloper is a full-featured, cross-platform, integrated development environment for creating multitier Java applications that is well integrated with Oracle Application Server and Database.

Oracle JDeveloper enables you to develop, debug, and deploy Java client applications, dynamic HTML applications, web and application server components, JavaBean components, and database stored procedures based on industry-standard models.

JDeveloper is also the integrated development environment for ADF and TopLink.

Import/Export of Object Types

Export and Import utilities move data into and out of Oracle databases. They also back up or archive data and aid migration to different releases of the Oracle RDBMS.

Export and Import support object types. Export writes object type definitions and all of the associated data to the dump file. Import then re-creates these items from the dump file.

When you import object tables, by default, OIDs are preserved.

SQL*Loader

The SQL*Loader utility moves data from external files into tables in an Oracle database. The files may contain data consisting of basic scalar data types, such as INTEGER, CHAR, or DATE, as well as complex user-defined data types such as row and column objects (including objects that have object, collection, or REF attributes), collections, and LOBs. Currently, SQL*Loader supports single-level collections only: you cannot yet use SQL*Loader to load multilevel collections, that is, collections whose elements are, or contain, other collections. SQL*Loader uses control files, which contain SQL*Loader data definition language (DDL) statements, to describe the format, content, and location of the datafiles.

SQL*Loader provides two approaches to loading data:

• Conventional path loading, which uses the SQL INSERT statement and a bind array buffer to load data into database tables

• Direct path loading, which uses the Direct Path Load API to write data blocks directly to the database on behalf of the SQL*Loader client.

  Direct path loading does not use a SQL interface and thus avoids the overhead of processing the associated SQL statements. Consequently, direct path loading generally provides much better performance than conventional path loading.

Either approach can be used to load data of supported object and collection data types.