Oracle: How Do You Define Which Data Type Is Assigned To A Column?

fourteen
User-Divers Datatypes

Object types and other user-defined datatypes allow you to define datatypes that model the structure and behavior of the data in their applications.

This chapter contains the following major sections:

Introduction to User-Divers Datatypes
User-Divers Datatype Categories
Type Inheritance
User-Defined Aggregate Functions
Application Interfaces
Datatype Evolution

Introduction to User-Defined Datatypes

Relational database direction systems (RDBMSs) are the standard tool for managing business data. They provide reliable access to huge amounts of data for millions of businesses around the world every mean solar day.

Oracle is an object-relational database direction organisation (ORDBMS), which means that users tin can ascertain boosted kinds of data--specifying both the construction of the information and the ways of operating on it--and employ these types within the relational model. This arroyo adds value to the data stored in a database. User-defined datatypes arrive easier for application developers to piece of work with complex data such as images, audio, and video. Object types store structured business concern information in its natural grade and permit applications to call back it that mode. For that reason, they work efficiently with applications developed using object-oriented programming techniques.

Complex Data Models

The Oracle server allows yous to ascertain complex business models in SQL and brand them part of your database schema. Applications that manage and share your information need only contain the awarding logic, not the information logic.

Circuitous Data Model Example

For case, your firm might utilise purchase orders to organize its purchasing, accounts payable, shipping, and accounts receivable functions.

A purchase society contains an associated supplier or client and an indefinite number of line items. In addition, applications often need dynamically computed status information nigh purchase orders. For example, you may need the electric current value of the shipped or unshipped line items.

Subsequently sections of this chapter evidence how you can define a schema object, called an object blazon, that serves as a template for all purchase order data in your applications. An object type specifies the elements, called attributes, that brand up a structured data unit, such every bit a purchase order. Some attributes, such as the listing of line items, tin be other structured data units. The object type likewise specifies the operations, called methods, you lot can perform on the information unit, such as determining the full value of a purchase society.

You lot can create purchase orders that match the template and shop them in tabular array columns, just every bit you lot would numbers or dates.

You can likewise store purchase orders in object tables, where each row of the table corresponds to a unmarried purchase gild and the table columns are the purchase club's attributes.

Because the logic of the purchase social club's structure and behavior is in your schema, your applications do not need to know the details and do non have to keep upwards with most changes.

Oracle uses schema data most object types to achieve substantial transmission efficiencies. A customer-side awarding can request a buy order from the server and receive all the relevant data in a single transmission. The application tin can then, without knowing storage locations or implementation details, navigate among related data items without further transmissions from the server.

Multimedia Datatypes

Many efficiencies of database systems arise from their optimized management of basic datatypes like numbers, dates, and characters. Facilities exist for comparing values, determining their distributions, building efficient indexes, and performing other optimizations.

Text, video, audio, graphics, and spatial data are examples of important business entities that do not fit neatly into those bones types. Oracle Enterprise Edition supports modeling and implementation of these complex datatypes.

User-Defined Datatype Categories

There are two categories of user-defined datatypes:

Object types
Collection types

User-defined datatypes use the congenital-in datatypes and other user-defined datatypes equally the building blocks for datatypes that model the structure and behavior of data in applications.

User-defined types are schema objects. Their utilise is subject field to the same kinds of administrative control as other schema objects.

Object Types

Object types are abstractions of the real-world entities--for case, purchase orders--that application programs deal with. An object type is a schema object with 3 kinds of components:

A proper name, which serves to place the object type uniquely within that schema
Attributes, which model the structure and state of the real-world entity. Attributes are built-in types or other user-defined types.
Methods, which are functions or procedures written in PL/SQL or Java and stored in the database, or written in a linguistic communication such equally C and stored externally. Methods implement operations the application tin can perform on the existent-globe entity.

An object blazon is a template. A structured data unit of measurement that matches the template is called an object.

Purchase Gild Example

Here is an example of how you can define object types chosen EXTERNAL_PERSON, LINEITEM, and PURCHASE_ORDER.

The object types EXTERNAL_PERSON and LINEITEM have attributes of built-in types. The object blazon PURCHASE_ORDER has a more than complex construction, which closely matches the structure of real purchase orders.

The attributes of PURCHASE_ORDER are ID, CONTACT, and LINEITEMS. The aspect CONTACT is an object, and the attribute LINEITEMS is a nested tabular array.

CREATE Blazon external_person Equally OBJECT (   name        VARCHAR2(30),   phone       VARCHAR2(20) );    CREATE Blazon lineitem Equally OBJECT (   item_name   VARCHAR2(30),   quantity    NUMBER,   unit_price  NUMBER(12,2) );  CREATE Type lineitem_table AS Tabular array OF lineitem;    CREATE Blazon purchase_order Equally OBJECT (   id          NUMBER,   contact     external_person,   lineitems   lineitem_table,      Member FUNCTION   get_value   Return NUMBER );

This is a simplified example. It does not show how to specify the trunk of the method GET_VALUE, nor does it bear witness the full complexity of a real purchase lodge.

An object type is a template. Defining it does not outcome in storage allotment. Yous can utilize LINEITEM, EXTERNAL_PERSON, or PURCHASE_ORDER in SQL statements in near of the same places you can utilize types similar NUMBER or VARCHAR2.

For case, you tin can define a relational table to keep track of your contacts:

CREATE Table contacts (   contact     external_person   date        DATE );

The CONTACT table is a relational tabular array with an object type defining one of its columns. Objects that occupy columns of relational tables are called column objects.

Types of Methods

Methods of an object type model the behavior of objects. The methods of an object type broadly fall into these categories:

A Member method is a part or a process that always has an implicit Cocky parameter as its showtime parameter, whose type is the containing object type.
A Static method is a function or a procedure that does not have an implicit SELF parameter. Such methods can be invoked by qualifying the method with the blazon name, as in TYPE_NAME.METHOD(). Static methods are useful for specifying user-divers constructors or cast methods.
Comparison methods are used for comparison instances of objects.

Oracle supports the option of implementing type methods in PL/SQL, Coffee, and C.

In the case, PURCHASE_ORDER has a method named GET_VALUE. Each buy club object has its own GET_VALUE method. For instance, if x and y are PL/SQL variables that hold purchase club objects and w and z are variables that hold numbers, the post-obit two statements tin leave w and z with dissimilar values:

due west = ten.get_value(); z = y.get_value();

After those statements, w has the value of the purchase order referred to by variable x ; z has the value of the purchase lodge referred to by variable y.

The term ten .GET_VALUE () is an invocation of the method GET_VALUE. Method definitions can include parameters, but GET_VALUE does not demand them, because it finds all of its arguments among the attributes of the object to which its invocation is tied. That is, in the first of the sample statements, it computes its value using the attributes of buy order 10. In the 2nd it computes its value using the attributes of buy lodge y. This is called the selfish style of method invocation.

Every object blazon also has one implicitly defined method that is not tied to specific objects, the object type's constructor method.

Object Type Constructor Methods

Every object blazon has a system-divers constructor method; that is, a method that makes a new object according to the object type'southward specification. The name of the constructor method is the name of the object blazon. Its parameters have the names and types of the object blazon'due south attributes. The constructor method is a function. It returns the new object every bit its value.

For example, the expression:

purchase_order(   1000376,   external_person ("John Smith","1-800-555-1212"),   Nix )

represents a purchase guild object with the following attributes:

id         1000376 contact    external_person("John Smith","1-800-555-1212") lineitems  Nada

The expression external_person ("John Smith", "1-800-555-1212") is an invocation of the constructor part for the object type EXTERNAL_PERSON. The object that it returns becomes the contact attribute of the purchase lodge.

Comparison Methods

Methods play a role in comparison objects. Oracle has facilities for comparing two data items of a given built-in blazon (for example, two numbers), and determining whether one is greater than, equal to, or less than the other. Oracle cannot, however, compare two items of an capricious user-defined type without further guidance from the definer. Oracle provides ii ways to ascertain an order relationship among objects of a given object blazon: map methods and gild methods.

Map methods use Oracle'due south ability to compare congenital-in types. Suppose, for instance, that you have defined an object type called RECTANGLE, with attributes Height and WIDTH. Y'all can define a map method area that returns a number, namely the product of the rectangle'south HEIGHT and WIDTH attributes. Oracle can then compare two rectangles by comparison their areas.

Order methods are more general. An gild method uses its own internal logic to compare two objects of a given object type. It returns a value that encodes the order relationship. For case, it could return -1 if the get-go is smaller, 0 if they are equal, and 1 if the first is larger.

Suppose, for example, that you take defined an object type called ADDRESS, with attributes STREET, CITY, STATE, and ZIP. Greater than and less than may have no significant for addresses in your application, but you may need to perform complex computations to determine when ii addresses are equal.

In defining an object type, you can specify either a map method or an society method for it, but non both. If an object type has no comparing method, Oracle cannot make up one's mind a greater than or less than relationship between 2 objects of that blazon. It tin can, even so, attempt to determine whether two objects of the blazon are equal.

Oracle compares 2 objects of a type that lacks a comparing method past comparing corresponding attributes:

If all the attributes are non-null and equal, Oracle reports that the objects are equal.
If in that location is an aspect for which the two objects have unequal not-null values, Oracle reports them unequal.
Otherwise, Oracle reports that the comparison is not available (zip).

Object Tables

An object table is a special kind of tabular array that holds objects and provides a relational view of the attributes of those objects.

For case, the following argument defines an object table for objects of the EXTERNAL_PERSON type divers before:

CREATE Table external_person_table OF external_person;

Oracle allows you to view this table in two ways:

A single cavalcade tabular array in which each entry is an EXTERNAL_PERSON object.
A multicolumn table in which each of the attributes of the object type EXTERNAL_PERSON, namely Proper noun and PHONE, occupies a column

For example, you lot tin execute the post-obit instructions:

INSERT INTO external_person_table VALUES (        "John Smith",        "one-800-555-1212" );  SELECT VALUE(p) FROM external_person_table p         WHERE p.name = "John Smith";

The kickoff educational activity inserts an EXTERNAL_PERSON object into EXTERNAL_PERSON_TABLE as a multicolumn table. The second selects from EXTERNAL_PERSON_TABLE as a single cavalcade table.

Row Objects and Column Objects

Objects that appear in object tables are called row objects. Objects that appear in table columns or equally attributes of other objects are called column objects.

Object Identifiers

Every row object in an object table has an associated logical object identifier (OID). Oracle assigns a unique organisation-generated identifier of length 16 bytes equally the OID for each row object by default.

The OID cavalcade of an object table is a subconscious cavalcade. Although the OID value in itself is not very meaningful to an object-relational application, Oracle uses this value to construct object references to the row objects. Applications need to be concerned with only object references that are used for fetching and navigating objects.

The purpose of the OID for a row object is to uniquely identify it in an object table. To do this Oracle implicitly creates and maintains an index on the OID column of an object tabular array. The organisation-generated unique identifier has many advantages, amidst which are the unambiguous identification of objects in a distributed and replicated surroundings.

Principal-Key Based Object Identifiers

For applications that do not require the functionality provided by globally unique system-generated identifiers, storing 16 actress bytes with each object and maintaining an alphabetize on it may not be efficient. Oracle allows the choice of specifying the main key value of a row object as the object identifier for the row object.

Primary-key based identifiers also take the advantage of enabling a more efficient and easier loading of the object table. By contrast, system-generated object identifiers need to be remapped using some user-specified keys, especially when references to them are also stored persistently.

Object Views Description

An object view is a virtual object table. Its rows are row objects. Oracle materializes object identifiers, which it does non store persistently, from primary keys in the underlying tabular array or view.

REFs

In the relational model, strange keys express many-to-1 relationships. Oracle object types provide a more efficient means of expressing many-to-one relationships when the "one" side of the human relationship is a row object.

Oracle provides a built-in datatype called REF to encapsulate references to row objects of a specified object type. From a modeling perspective, REFs provide the ability to capture an association betwixt two row objects. Oracle uses object identifiers to construct such REFdue south.

Y'all tin can use a REF to examine or update the object it refers to. Y'all can as well utilise a REF to obtain a copy of the object information technology refers to. The only changes y'all tin brand to a REF are to supersede its contents with a reference to a dissimilar object of the same object blazon or to assign it a null value.

Scoped REFs

In declaring a column type, collection element, or object type aspect to be a REF, you can constrain information technology to comprise just references to a specified object table. Such a REF is called a scoped REF. Scoped REFs crave less storage space and allow more efficient access than unscoped REFs.

Dangling REFs

It is possible for the object identified by a REF to get unavailable through either deletion of the object or a change in privileges. Such a REF is chosen dangling. Oracle SQL provides a predicate (called IS DANGLING) to allow testing REFs for this condition.

Dereference REFs

Accessing the object referred to by a REF is chosen dereferencing the REF. Oracle provides the DEREF operator to practise this. Dereferencing a dangling REF results in a zero object.

Oracle provides implicit dereferencing of REFs. For example, consider the following:

CREATE Type person AS OBJECT (   proper noun    VARCHAR2(thirty),   managing director REF person );

If x represents an object of blazon PERSON, then the expression:

ten.director.name

represents a cord containing the Name attribute of the PERSON object referred to past the MANAGER attribute of x. The previous expression is a shortened course of:

y.name, where y = DEREF(x.manager)

Obtain REFs

You can obtain a REF to a row object by selecting the object from its object table and applying the REF operator. For example, y'all can obtain a REF to the buy order with identification number 1000376 as follows:

DECLARE OrderRef REF to purchase_order;  SELECT REF(po) INTO OrderRef                FROM purchase_order_table po                WHERE po.id = 1000376;

Collection Types

Each drove type describes a information unit made up of an indefinite number of elements, all of the aforementioned datatype. The collection types are array types and table types.

Array types and table types are schema objects. The corresponding data units are called VARRAYs and nested tables. When at that place is no danger of defoliation, we oftentimes refer to the drove types as VARRAYsouthward and nested tables.

Collection types accept constructor methods. The name of the constructor method is the name of the type, and its statement is a comma-separated list of the new collection'due south elements. The constructor method is a function. It returns the new collection as its value.

An expression consisting of the type name followed by empty parentheses represents a telephone call to the constructor method to create an empty collection of that type. An empty drove is unlike from a null drove.

VARRAYs

An array is an ordered set of information elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element'southward position in the array.

The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called VARRAYs. You must specify a maximum size when you lot declare the array type.

For example, the following statement declares an assortment blazon:

CREATE Blazon prices Every bit VARRAY(10) OF NUMBER(12,2);

The VARRAYdue south of type PRICES have no more than 10 elements, each of datatype NUMBER(12,2).

Creating an array type does not allocate space. It defines a datatype, which you tin use as:

The datatype of a column of a relational tabular array
An object blazon aspect
A PL/SQL variable, parameter, or office return type.

A VARRAY is normally stored in line; that is, in the same tablespace as the other data in its row. If it is sufficiently large, however, Oracle stores it every bit a Blob.

Nested Tables Clarification

A nested table is an unordered prepare of data elements, all of the same datatype. It has a single cavalcade, and the type of that column is a built-in type or an object type. If an object type, the table tin can also be viewed as a multicolumn table, with a cavalcade for each attribute of the object blazon. If compatibility is set to Oracle9i, Release 1 (ix.0.i) or college, nested tables tin can contain other nested tables.

For case, in the buy gild example, the post-obit statement declares the tabular array blazon used for the nested tables of line items:

CREATE TYPE lineitem_table Every bit TABLE OF lineitem;

A table type definition does not allocate infinite. It defines a type, which you tin utilize as:

The datatype of a column of a relational table
An object type attribute
A PL/SQL variable, parameter, or function return type

When a table type appears as the blazon of a column in a relational tabular array or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single tabular array, which it associates with the enclosing relational or object table. For example, the following statement defines an object tabular array for the object type PURCHASE_ORDER:

CREATE Table purchase_order_table OF purchase_order    NESTED TABLE lineitems Store AS lineitems_table;

The second line specifies LINEITEMS_TABLE as the storage table for the LINEITEMS attributes of all of the PURCHASE_ORDER objects in PURCHASE_ORDER_TABLE.

A convenient way to access the elements of a nested tabular array individually is to use a nested cursor.

Type Inheritance

An object type can be created as a subtype of an existing object type. A single inheritance model is supported: the subtype can exist derived from only i parent type. A type inherits all the attributes and methods of its direct supertype. It tin add together new attributes and methods, and it can override whatever of the inherited methods.

Figure xiv-i illustrates two subtypes, Student_t and Employee_t, created under Person_t.

Figure fourteen-i A Type Hierarchy

Text description of scn81148.gif follows
Text description of the analogy scn81148.gif

Furthermore, a subtype tin itself exist refined past defining some other subtype nether it, thus building up type hierarchies. In the diagram above, PartTimeStudent_t is derived from subtype Student_t.

Last and NOT Concluding Types

A blazon declaration must take the Non Last keyword, if you lot want it to have subtypes. The default is that the type is Last; that is, no subtypes tin can be created for the type. This allows for backward compatibility.

Case of Creating a Non Last Object Type

CREATE TYPE Person_t Every bit OBJECT ( ssn NUMBER,   name VARCHAR2(30),   address VARCHAR2(100)) NOT FINAL;

Person_t is declared to be a Non Concluding blazon. This enables definition of subtypes of Person_t.

FINAL types can exist altered to be Non Terminal. In addition, Non Last types with no subtypes can exist contradistinct to be FINAL.

NOT INSTANTIABLE Types and Methods

A type tin be declared to be NOT INSTANTIABLE. This implies that there is no constructor (default or user-defined) for the type. Thus, it volition not be possible to construct instances of this blazon. The typical usage would be ascertain instantiable subtypes for such a type, every bit follows:

CREATE TYPE Address_t Equally OBJECT(...) NOT INSTANTIABLE NOT Last; CREATE TYPE USAddress_t UNDER Address_t(...); CREATE TYPE IntlAddress_t Nether Address_t(...);

A method of a type can be declared to be NOT INSTANTIABLE. Declaring a method as NOT INSTANTIABLE means that the type is not providing an implementation for that method. Furthermore, a type that contains whatsoever non-instantiable methods must necessarily be declared NOT INSTANTIABLE.

For example:

CREATE TYPE T AS OBJECT (   10 NUMBER,   Not INSTANTIABLE MEMBER Role func1() RETURN NUMBER  ) NOT INSTANTIABLE;

A subtype of a NOT INSTANTIABLE type can override whatsoever of the non-instantiable methods of the supertype and provide physical implementations. If there are any not-instantiable methods remaining, the subtype must besides necessarily be declared Not INSTANTIABLE.

A non-instantiable subtype tin exist defined under an instantiable supertype. Declaring a non-instantiable type to be FINAL is non allowed.

User-Defined Aggregate Functions

Oracle8i supports a stock-still set of aggregate functions such as MAX, MIN, and SUM. Oracle9i, Release 1 (9.0.ane), adds to this a machinery for users to implement new aggregate functions with user-defined aggregation logic.

Why Take User-Defined Aggregate Functions?

User-Defined Amass functions (UDAGs) refer to aggregate functions with user-specified aggregation semantics. Users tin can create a new aggregate function and provide the aggregation logic through a prepare of routines. In one case created, the user-defined aggregate function can be used in SQL DML statements in a way like to built-in aggregates. The Oracle server evaluates the UDAG by invoking the user-provided aggregation routines appropriately.

Databases are increasingly being used to store circuitous data such equally prototype, spatial, audio, video, etc. The circuitous information is typically stored in the database using object types, opaque types and/or LOBsouth. User-defined aggregates are primarily useful in specifying assemblage over such new domains of data.

Furthermore, UDAGs can be used to create new aggregate functions over traditional scalar data types for fiscal or scientific applications. Since, information technology is not possible to provide native support for all forms of aggregates, information technology is desirable to provide application developers with a flexible mechanism to add new aggregate functions.

Creation and Employ of UDAGs

The post-obit is the process for implementing user-defined aggregates:

Implement the ODCIAggregate interface routines as methods of an object type.

Create a UDAG, using the CREATE Part command and specify the implementation type created in Pace 1:

CREATE Part              MyUDAG              ... AGGREGATE USING              MyUDAGRoutines;

Apply the UDAG in SQL DML statements the aforementioned manner y'all use born aggregates:

SELECT col1,              MyUDAG(col2) FROM tab Group Past col1;

How Practise Aggregate Functions Work?

An aggregate function conceptually takes a set of values as input and returns a single value. The sets of values for aggregation are typically identified using a GROUP BY clause. For case:

SELECT AVG(T.Sales) FROM AnnualSales T GROUP BY T.State

The evaluation of an aggregate function can exist decomposed into 3 primitive operations. Considering the above example of AVG(), they are:

Initialize : initialize the computation

runningSum = 0; runningCount = 0;

Iterate : process new input value

runningSum += inputval; runningCount++;

Terminate : compute the consequence

return (runningSum/runningCount);

The variables runningSum and runningCount, in the higher up example, determine the state of the aggregation. Thus, the aggregation context can be viewed as an object that contains runningSum and runningCount attributes. The Initialize method initializes the aggregation context, Iterate updates it and End method uses the context to return the resultant aggregate value.

In addition, we crave one more archaic operation to merge ii assemblage contexts and create a new context. This functioning is needed to combine the results of aggregation over subsets and obtain the aggregate over the unabridged set. This situation can arise during both serial and parallel evaluations of the aggregate.

Merge: combine the two aggregation contexts and return a unmarried context

runningSum = runningSum1 + runningSum2; runningCount = runningCount1 + runningCount2;

Oracle9i, Release ane (9.0.1), allows the user to register new aggregate functions by providing specific (new) implementations for the above primitive operations.

Application Interfaces

Oracle provides several facilities for using user-defined datatypes in application programs:

SQL

Oracle SQL data definition language provides the following support for user-defined datatypes:

Defining object types, nested tables, and arrays
Specifying privileges
Specifying table columns of user-defined types
Creating object tables

Oracle SQL data manipulation language provides the following support for user-defined datatypes:

Querying and updating objects and collections
Manipulating REFs

PL/SQL

PL/SQL is a procedural language that extends SQL. It offers features such as packages, data encapsulation, information hiding, overloading, and exception treatment. Most stored procedures are written in PL/SQL.

PL/SQL allows use from within functions and procedures of the SQL features that back up user-defined types. The parameters and variables of PL/SQL functions and procedures can exist of user-defined types.

PL/SQL provides all the capabilities necessary to implement the methods associated with object types. These methods (functions and procedures) reside on the server every bit office of a user's schema.

Pro*C/C++

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

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

SQL statements and PL/SQL functions or procedures embedded in Pro*C programs
A simple interface to the object cache, where objects can be accessed by traversing pointers, then modified and updated on the server

Dynamic Cosmos and Access of Type Descriptions

Oracle9i, Release 1 (nine.0.i), provide a C API to enable dynamic cosmos and access of blazon descriptions. Additionally, yous tin can create transient type descriptions, blazon descriptions that are not stored persistently in the DBMS.

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

The OCIAnyData type models a self descriptive (with regard to type) data example of a given type.
The OCIAnyDataSet type models a fix of information instances of a given type.

Oracle9i, Release one (ix.0.ane), besides provides SQL data types (in Oracle's Open Type System) that represent to these information 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 new C API uses the following terms:

Transient types - Blazon descriptions (type metadata) that are not stored persistently in the Database.
Persistent types - SQL types created using the CREATE Blazon SQL statement. Their type descriptions are stored persistently in the Database.
Self-descriptive information - 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 information value. An incorrect conversion attempt results in an exception.
Self-descriptive MultiSet - Encapsulation of a set of data instances (all of the same type), along with their blazon description.

OCI

The Oracle telephone call interface (OCI) is a set up of C language interfaces to the Oracle server. It provides programmers great flexibility in using the server's capabilities.

An important component of OCI is a ready of calls to allow application programs to use a workspace called the object cache. The object enshroud is a memory block on the client side that allows programs to shop unabridged objects and to navigate among them without round trips to the server.

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

OCI provides functions to:

Admission objects on the server using SQL
Admission, manipulate and manage objects in the object cache past traversing pointers or REFs
Convert Oracle dates, strings and numbers to C data types
Manage the size of the object cache's retentivity
Create transient type descriptions. Transient type descriptions are non stored persistently in the DBMS. Compatibility must be set up to Oracle9i, Release 1 (ix.0.ane) or higher.

OCI improves concurrency past assuasive individual objects to be locked. It improves operation past supporting complex object retrieval.

OCI developers can employ the object type translator to generate the C datatypes corresponding to a Oracle object types.

OTT

The Oracle blazon translator (OTT) is a program that automatically generates C language construction declarations corresponding to object types. OTT facilitates using the Pro*C precompiler and the OCI server admission packet.

JPublisher

Java Publisher (JPublisher) is a programme that automatically generates Java course definitions corresponding to user-defined types in the database. Coffee Publisher facilitates using SQLJ and the JDBC server access package.

JDBC

Java Database Connectivity (JDBC) is a set of Coffee interfaces to the Oracle server. Oracle'due south JDBC:

Allows access to objects and drove types divers in the database from Java programs through dynamic SQL
Provides for translation of types defined in the database into Java classes through default or customizable mappings

SQLJ

SQLJ allows developers to utilise user-defined 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 admission 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).

SQLJ Object Types

Oracle9i, Release i (9.0.ane), lets yous specify Java classes as SQL user-defined object types. Y'all can define columns or rows of this SQLJ type. You can also query and dispense 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 grade and its corresponding type

Datatype Evolution

A user-divers datatype can be referenced past any of the following schema objects:

Table or subtable
Type or subtype
Program unit (PL/SQL block): process, function, bundle, trigger
Indextype
View (including object view)
Functional index
Operator

When any of these objects references a blazon, either directly or indirectly through another type or subtype, it becomes a dependent object on that type. Whenever a type is modified, all dependent plan units, views, operators and indextypes are marked invalid. The next time each of these invalid objects is referenced, information technology is revalidated, using the new type definition. If it is recompiled successfully, then it becomes valid and tin be used again.

When a type has either type or table dependents, altering a type definition becomes more than complicated because existing persistent data relies on the current blazon definition.

Oracle9i, Release 1 (9.0.1), adds options to the Change TYPE and ALTER Table statements so that y'all tin can modify an object type and propagate the type change to its dependent types and tables. ALTER TYPE lets you lot add or driblet methods and attributes from existing types and optionally propagate the changes to dependent types, tables, and even the table data. You tin can also modify certain attributes of a type.