The latest functionality in the Development Platform: SQL, PL/SQL, Objects, OCCI

Sandeepan Banerjee, Oracle Corporation

Introduction

 

All database operations are performed using SQL. SQL is a simple, yet powerful, database access language. SQL is a nonprocedural language; users describe in SQL what they want done, and the SQL compiler automatically generates a procedure to navigate the database and perform the desired task.

Since its inception, ANSI/ISO has refined SQL as the standard language for relational and object-relational database management systems. Oracle SQL includes many extensions to the ANSI/ISO standard SQL language, and Oracle tools and applications provide additional statements. The Oracle tools SQL*Plus, Oracle Enterprise Manager etc. allow you to execute SQL statements against an Oracle database.

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and execute PL/SQL program units such as procedures, functions, and packages. The PL/SQL engine, which processes PL/SQL program units, is a special component of many Oracle products, including the Oracle server.

Taken together, an overwhelming proportion of corporate applications all over the world utilize Oracle’s SQL and PL/SQL.  Over the years, many of these applications have evolved from a departmental orientation to a more global one, and this has placed important demands of performance, scalability, globalization etc. on the underlying engines.  As applications automate and enable more business functions, data models have become more complex, and many users have felt that the SQL and PL/SQL need to support complex data models. Finally, in recent years, client-server architectures have evolved into more loosely coupled web-based architectures. Oracle has integrated XML and Java with the SQL engine, as well as pioneered technologies like PL/SQL Server pages, to enable developers to leverage existing data and resources to service web-based applications.

In Oracle9i, then, our thrust has been to greatly enhance the capabilities of SQL and PL/SQL in certain key areas.

·         SQL, PL/SQL, XML and Java are integrated as never before

·         SQL and PL/SQL have a number of globalization features that enable 24x7 operations across time-zones, languages, character sets and locales

·         New complex data modeling capabilities in the server enable applications to retain the same data models across all their architectural tiers, without the need to ‘map’ objects to tables and vice versa

·         A number of important SQL-standards related features are introduced.

·         Native compilation of PL/SQL code greatly improves the execution of server-resident application logic; a number of other important performance improvements to PL/SQL are introduced.

 

These, taken with across-the-board functionality and performance improvements in all areas, will be the subjects of this document. However, before diving specifically into the Oracle9i enhancements, let us recapitulate the salient aspects of SQL and PL/SQL.

 

Oracle’s SQL Engine: The Basics

 

All operations performed on the information in an Oracle database are executed using SQL statements. A SQL statement is a specific instance of a valid SQL statement. A statement consists partially of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.

A SQL statement can be thought of as a simple but powerful computer program or instruction. The statement must be the equivalent of a complete SQL sentence, as in:

SELECT ename, deptno FROM emp;

 

There are several different kinds of SQL statements – viz.:

·         Data manipulation language (DML) statements

·         Data definition language (DDL) statements

·         Transaction control statements

·         Session control statements

·         System control statements

·         Embedded SQL statements

 

Taken together, the above categories create a powerful data management capability in Oracle.  The Oracle database server contains a very powerful engine for processing SQL statements, with very many different features to deal efficiently with all the different combinations of SQL statements used by applications. For instance, Oracle automatically notices when applications send similar SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared--that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Because shared SQL areas are shared memory areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory usage on the database server, thereby increasing system throughput.  To take another example of optimization, Oracle can parallelize queries (SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs), and some DDL operations such as index creation, creating a table with a subquery, and operations on partitions. Parallelization causes multiple server processes to perform the work of the SQL statement so it can complete faster.

 

Oracle’s PL/SQL Engine: The Basics

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL enables you to mix SQL statements with procedural constructs that can perform flow control. With PL/SQL, you can define and execute PL/SQL program units such as procedures, functions, and packages.

For example, for a traditional Employee table, it is possible to define a procedure HIRE_EMP to place a new Employee record in the table:

Procedure HIRE_EMP (name VARCHAR2, job VARCHAR2, mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER, deptno NUMBER)

BEGIN
.
.
INSERT INTO emp VALUES (emp_sequence.NEXTVAL, name, job, mgr hiredate, sal, comm, deptno);
.
.
END;

 

The procedure can be called from Oracle SQL*Plus or Oracle Enterprise Manager as:

 

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

 

PL/SQL can improve database performance in several ways:

·         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, 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), retrieval from disk is not required, and execution can begin immediately.

·         Because PL/SQL takes advantage of the shared memory capabilities of Oracle, only a single copy of the PL/SQL code 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 memory requirements for applications.

   

In addition, by designing applications around a common set of PL/SQL procedures or functions, you can avoid redundant coding and increase your productivity.  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, only the procedure needs to be recompiled. Applications that call the procedure do not necessarily require any modifications.

When an application calls a procedure stored in the database, Oracle loads the compiled program unit (i.e. the parsed representation of the procedure) 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. Queries, which have no procedural aspect, are handled directly by the SQL engine.

PL/SQL can execute 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, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.

Over the years, Oracle has enhanced PL/SQL with myriad features to the point where today it is a finely honed engine that addresses the most data intensive operations for an enormous variety of applications around the world.

 

What’s New in Oracle9i for SQL and PL/SQL?

 

A number of trends have come together in recent years to cause a widely felt evolution in application development environments. New channels – the browser-based intranet application, the portal, the exchange, or the wireless gateway -- have been opened-up to the end-user.  More and more applications have to contend with the demands of a global, multi-lingual, 24x7 base of users. Content is richer, data more complex than ever before. Compressed development cycles put pressure on those environments that try to integrate a variety of niche solutions.

 

As developers struggle to upgrade their skills to cope with these challenges and also exploit the new opportunities, they look to build upon what they already know. Oracle’s SQL and PL/SQL technologies are widely available and understood. As discussed above, a huge proportion of enterprise applications are built using these technologies. The most popular business-to-business (B2B) and business-to-consumer (B2C) applications use Oracle back-end servers.  Oracle’s software serves up content for the most demanding content-management (CM) applications.  However, whatever be the nature of final application, success depends on a functional, integrated, easy-to-use, robust development platform. In Oracle9i, we have added a number of capabilities to ensure that Oracle remains the best development platform for both Internet and intranet applications.

In Oracle9i, a number of enhancements have been made to SQL and PL/SQL:

·         Oracle9i creates an integrated SQL, XML and Java platform – SQL  and PL/SQL have been integrated with XML and Java as never before.

·         SQL and PL/SQL have been enhanced with global e-Business capabilities

·         Oracle9i marks the completeness of Oracle’s Object-Relational vision

·         Architectural enhancements make PL/SQL significantly faster

·         A number of enhancements have been made to SQL for better standards-compliance.

Let us look at each of the above in greater detail.

 

Integrating SQL, PL/SQL, XML and Java

 

XML in Oracle9i

 

XML is not only a popular suite of standards, but it is also fundamental emerging technology that will enable the building of the next generation of loosely coupled, semantically rich applications which make efficient use of network resources.  Integrating XML with the SQL and PL/SQL engines was an important design goal of Oracle9i.  Several important new capabilities related to XML make their debut in Oracle9i. These can be grouped into:

·         Server native XML features

·         XML development kit enhancements

·         XML-based services (queuing, messaging)

In this section, we will focus on the first item, viz. those features added to the database server to enable it to store and retrieve XML. The other capabilities are discussed in detail in the Oracle9i Development Platform: XML whitepaper.

XMLType

Oracle9i introduces a native XMLType data type to enable storage of XML documents. Data stored in XMLType columns (or object tables) is queryable from SQL, with XPath support. A number of useful functions are supplied with XMLType to enable common XML node processing operations. XMLType can also be invoked from PL/SQL procedures and functions.

In addition, Oracle9i also introduces native XML generation and aggregation in the form of SQL operators and PL/SQL packages. These server-based XML greatly increase the throughput of operations on XML.

Finally, a set of new native URI-Ref datatypes has also been also introduced in Oracle9i.  These enable references to XML document fragments inside or outside a database. URI-Ref datatypes help you build complex XML data- and document-models inside the Oracle server.

Let us look at a simple example of using XMLType.

 

CREATE TABLE warehouses( warehouse_id NUMBER(3),

                         warehouse_spec SYS.XMLTYPE,

                         warehouse_name VARCHAR2(35),

                         location_id NUMBER(4));

 

INSERT INTO warehouses VALUES (5,

sys.xmltype.createxml('<?xml version="1.0"?>

                       <Warehouse>

                       <Building>Owned</Building>

                       <Area>25000</Area>

                       <Docks>2</Docks>

                       <DockType>Rear load</DockType>

                       <WaterAccess>Y</WaterAccess>

                       <RailAccess>N</RailAccess>

                       <Parking>Street</Parking>

                       <VClearance>10 ft</VClearance>

                       </Warehouse>'),

                       'Toronto',1800);

 

Once created, the XMLType data can be easily queried from SQL as:

SELECT w.warehouse_spec.extract ('/Warehouse/Building/text()').getStringVal()

"Building" FROM warehouses w

 

Note that XPath-like syntax can be used with a SQL query to traverse the nodes of a XML document stored in a XMLType, and a certain node’s value returned from the query. The above query returns:

 

Building 

-----------------

Owned

 

It is interesting to note the XMLType is an abstraction that can be used to specify different storage options for XML documents.  Currently, Oracle9i internally stores XMLType documents in character LOBs (CLOBs). In the future, the abstraction may be extended to support a number of other storage options, such as exploded object-relational storage, name-value pair storage and so on.  CLOB storage is most valuable when XML documents are to be retrievable faithful to the original (preserving whitespace, order of nodes, encoding and so on.) CLOB storage can leverage both functional indexes on nodes, as well as Text indexes to retrieve XML data efficiently. Exploded relational storage, however, might be suitable when there are large numbers of structured elements in the XML data, and queries issued against these structured attributes can exploit B-Tree indexes.

To this end, when creating a table containing a XMLType, you can specify a storage clause to indicate the kind of storage you prefer. Developers can thus focus on programming to the XMLType interface, and leave the storage optimization to DBAs.

 

CREATE TABLE warehouses( warehouse_id NUMBER(3),

                         warehouse_spec SYS.XMLTYPE,

                         warehouse_name VARCHAR2(35),

                         location_id NUMBER(4))

XMLType COLUMN warehouse_spec STORE AS CLOB

        (TABLESPACE lob_seg_ts STORAGE (INITIAL 4096 NEXT 4096) CHUNK 4096 NOCACHE LOGGING);

 

A number of useful functions are supplied with XMLType to process XML. We have already seen use of the extract() function in the query above to extract a certain node from a XMLType document. Some of the other important member functions of XMLType are shown below in Table1.

 

XMLType Function	Description
getClobVal	Return the contents of the XMLType as a CLOB value
getStringVal	Return the value of a XML Node as a string
getNumVal	Returns the value of a XML Node as a number
extract	Extracts a portion of the document using a XPath-like syntax, returning a XMLType
existsNode	Checks if there are any resultant Nodes in the XPath expression
isFragment	Is the XMLType really a fragment?

 

Table 1:  Member Functions of XMLType

 

 

You can build functional indexes on existsNode and extract functions to improve performance.

CREATE INDEX wh_idx ON warehouses (warehouse_spec.extract(‘/Warehouse/Building’). getStringVal());

 

Indexes can also be created on the entire stored CLOB content:

CREATE INDEX wh_spec_idx ON warehouses((warehouse_spec).getStringVal());

 

These indexes can help speed queries of the type:

SELECT * FROM warehouse w WHERE

w.warehouse_spec.extract ('/Warehouse/Building').getStringVal() LIKE 'Owned';

  

XMLType brings the XML and SQL worlds together by enabling SQL operations on XML content.  It can also be used to perform XML operations on SQL content, as we see below.

Generating and Aggregating XML

In Oracle8i Release 3, Oracle introduced the XML-SQL Utility (XSU) and the related XSQL Servlet to generate XML from SQL queries. The XSU ran as a Java client-program to the database. In Oracle9i, a similar generation capability is introduced natively in SQL and PL/SQL, increasing the XML generation throughput by many orders of magnitude. The XSU will, of course, continue to be available as part of the Oracle9i XDK to help developers access pre-9i databases. Let us look at the new ‘native’ capabilities in Oracle9i.

SYS_XMLGEN and SYS_XMLAGG SQL Operators

The new SYS_XMLGEN SQL operator takes a scalar value, object type or XMLType instance to be converted to an XML document, and utilizing an optional XMLGenFormatType specifying formatting options for the result, returns an XMLType. The operator can be used to create and query XML instances within SQL queries:

 

SELECT SYS_XMLGEN(employee_id) FROM employees WHERE last_name LIKE 'Scott%';

 

This returns the following XML fragment;

<?xml version=''1.0''?>

  <employee_id>60</employee_id>

 

SYS_XMLAGG, on the other hand, aggregates all the input data and produces a single XML document. It can be used to concatenate XML, as in:

 

SELECT SYS_XMLAGG(SYS_XMLGEN(last_name) SYS.XMLGENFORMATTYPE.createFormat ('EmployeeGroup')).getClobVal() FROM employees  GROUP BY department_id;

 

The result of the operator is:

<EmployeeGroup>

<last_name>Scott</last_name>

 <last_name>Mary<last_name>

</EmployeeGroup >

<EmployeeGroup >

 <last_name>Jack</last_name>

  <last_name>John>/last_name>

</EmployeeGroup >

 

So we see that SYS_XMLGEN operates on rows, generating XML documents, while SYS_XMLAGG operates on groups of rows, aggregating several XML documents into one

DBMS_XMLGEN

You can also take advantage of native XML generation and aggregation from PL/SQL.  The DBMS_XMLGEN package can creates a XML document from any SQL query, returning the result as a CLOB. It also provides an interesting  "fetch" interface that can be used to control the maximum rows returned, rows to skip --useful for pagination in web applications. DBMS_XMLGEN is very flexible and provides options for changing tag names for returned results to suit the application consuming generated XML. Here is an example of using DBMS_XMLGEN:

CREATE TABLE temp_clob_tab(result CLOB);

  DECLARE

  qryCtx DBMS_XMLGEN.ctxHandle;

    result CLOB;

  BEGIN

    qryCtx := dbms_xmlgen.newContext

    ('SELECT * from employees;');

 DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');

  DBMS_XMLGEN.setMaxRows(qryCtx, 5);

  LOOP

   result := DBMS_XMLGEN.getXML(qryCtx);

  EXIT WHEN

  DBMS_XMLGEN.getNumRowsProcessed((qryCtx)=0);

 INSERT INTO temp_clob_tab VALUES(result);

  END LOOP;

END;

/

 

The returned XML results look like:

 

<?xml version=''1.0''?>

<ROWSET>

 <EMPLOYEE>

 <EMPLOYEE_ID>30</EMPLOYEE_ID>

 <LAST_NAME>SCOTT</LAST_NAME>

 <SALARY>20000<SALARY>

 </EMPLOYEE>

  <EMPLOYEE>

<EMPLOYEE_ID>31</EMPLOYEE_ID>

  <LAST_NAME>MARY</LAST_NAME>

  <AGE>25</AGE>

 </EMPLOYEE>

</ROWSET>

 

DBMS_XMLGEN can also be used to generate more complex XML, using SQL object types to represent nested structures. More information about such use can be found in the Oracle9i Application Developers Guide for XML.

URI_References

XML documents in the server often refer to other XML documents (or fragments) inside or outside the server. As such, a mechanism is needed to intelligently specify such references, and efficiently operate on them.

A URI_Reference is a generalization of the URL concept. It consists of two parts – a URL part (such as http://www.oracle.com/xml/doc1#//), and a fragment part (such as warehouse_spec/custno).

URI_References come in two flavors. A DBURIType  is a intra-database reference – it can ‘point to’ objects inside a database, e.g.:

/SCOTT/EMPLOYEES/ROW[ADDRESS/STATE=’CA’]/CITY

DBURITypes can address different granularities – such as a schema, a table, a row, a column, an object type attribute within a column and so on.

A HTTPURIType, on the other hand, can be used to access documents or fragments outside the database. 

We also provide a base type called URIType, which is an abstract type that can store instances of either of the above, useful when your references can be either local or remote. Here is an example of using URITypes.

 

CREATE TABLE tax_deductible_tab (

                 uri_col        UriType,

                 max_deduction  NUMBER(7,2),

                 description    VARCHAR2);

 

INSERT INTO tax_deductible_tab VALUES (

 UriFactory.getURL('/SCOTT/Warehouses/ROW[Area=2500'),

 2500.00, 'Scott's Warehouse Deductible');

 

INSERT INTO tax_deductible_tab VALUES (

 UriFactory.getURL ('http://proxy.oracle.com/webaccts/pos/scott/po1'),

 1000.00, 'Scott's Web Deductible');

 

As you can see, URITypes provide a uniform mechanism to model references whether inside or outside the database. This is of great benefit in building loose-ly coupled XML applications (common in many B2B or B2C applications, as well as in many corporate portals) where the targets of references are often weakly typed and where the target content can move in and out of different kinds of repositories.  URITypes can be accessed seamlessly from SQL and PL/SQL:

 

SELECT e.ur_col.getClob()

FROM   tax_deductible_tab e;

 

--PL/SQL

DECLARE

  V_uri UriType;

BEGIN

    SELECT uri_col into v_uri FROM tax_deductible_tab

    WHERE Description LIKE 'Scott%';

    printDataOut(v_uri.getClob());

END;

 

URITypes lead to improved mapping of XML documents to columns, and also help efficient indexing, navigation, querying of XML content.

 

SQL AND JavaÔ Integration

 

Oracle9i introduces new support for seamless storage, or persistence, of Java data into Oracle, based on the ANSI SQLJ (Part II) standard mappings.  This allows your applications to share the same data model across all tiers: Java objects running in application servers are maintained in the database server, and these object abstractions can be shared with XML or C++. Developers can spend their time writing business logic, rather than producing translation or mapping code.

Since the early days of JDK, Java developers have used the JDBC API to develop enterprise-level and data-intensive applications for SQL databases. JDBC is a standard set of Java classes providing vendor-independent access to relational data. JDBC was instrumental in fueling the trend towards server-side Java development using databases that prevails today.

JDBC is, however, somewhat low-level and very code intensive, requiring an exhaustive knowledge of the API calls. Developers typically have to write many lines of code to perform basic operations such as executing a simple SQL statement. The sheer number of lines of code and its repetitive nature make development of large JDBC applications slow and error prone. Second, JDBC does not address the issue of executing Java within a database, where for years, database stored procedures have been the preferred programming model.

SQLJ began as an initiative among the key software vendors to address the above deficiencies of JBDC and to standardize the interaction of SQL and Java in data-intensive applications. Key goals of SQLJ were 100% Java compliance, cross-vendor portability and binary level portability. Oracle, as a cofounder of this initiative, worked with major technology vendors such as Compaq, IBM and Sun to create a standard to submit for ANSI approval. In December 1998, SQLJ was granted ANSI standard status. More information about SQLJ can be found at http://www.sqlj.org.

SQLJ allows you to use a set of programming extensions to embed Structured Query Language (SQL) database requests and statements into Java. The embedded SQL statements comply with the ANSI-standard SQLJ Language Reference syntax. You run a SQLJ translator that converts the SQLJ program to a standard Java program, replacing the embedded SQL statements with calls to SQLJ runtime. The generated Java program is compiled using any Java compiler and run against a database. The SQLJ runtime environment consists of a thin SQLJ runtime library that is implemented in pure Java. It calls a Java Database Connectivity (JDBC) driver targeting the appropriate database. Because SQLJ programs are converted to JDBC, they retain their multi-vendor interoperability, and can be used with any JDBC drivers. SQLJ is similar to the ANSI/ISO embedded SQL specifications that prescribe how static SQL is embedded in C, FORTAN, COBOL, and other languages.

SQLJ, then, focuses on providing a concise syntax for static SQL, and also deals only with embedded static SQL. JDBC, in contrast, provides a dynamic SQL interface.

The SQLJ specification consists of multiple parts:

·         SQLJ Part 0: Embedded SQL  -- is the specification for embedding SQL statements in Java methods.

·         SQLJ Part I: SQL Routines – is the specifications for calling Java static methods as SQL stored procedures and user-defined functions.

·         SQLJ Part II:  SQLJ: SQL Types – is the specification for using Java classes as SQL user-defined datatypes.

 

SQLJ Part II and the SQLJ Object Type

 

In Oracle9i, we introduce SQLJ Object Types. A SQLJ Object Type is a special SQL type fully implemented in Java. Each SQLJ Object type maps to a Java class inside the database.. Once the mapping is registered through the "CREATE TYPE" SQL DDL, the Java application can INSERT/SELECT copies of the Java objects directly into/from the database through the Oracle9i JDBC drivers. The database SQL engine can access the attributes of the Java class as a regular SQL type. Here is an example of creating the mapping:

CREATE TYPE person_T AS OBJECT EXTERNAL NAME 'Person' LANGUAGE JAVA USING SQLData

(ss_no NUMBER(9) EXTERNAL NAME 'ssn', name VARCHAR2(100) EXTERNAL NAME 'name', address VARCHAR2(255) EXTERNAL NAME 'address', MEMBER FUNCTION id() RETURN int EXTERNAL NAME 'id’ );

 

The Java class PERSON is implemented as standard Java, must implement the standard SQLData or Oracle-optimized CustomDatum interfaces to efficiently read and write data out of the server. Each SQLJ Object is the database representation of Java objects that exist outside the database.  To create a SQLJ Object, the Java application creates the corresponding object in Java, and then INSERTs or UPDATEs it to the database. Subsequently, it can be retrieved in a Java application via JDBC, or in a SQL application via standard SQL:

SELECT person.ss_no FROM person_tab;

JDBC applications can also retrieve SQLJ object types as PL/SQL OUT parameters from CallableStatement, and so on.

So we see that the integration between SQL, PL/SQL and Java  -- especially the storage of Java classes as SQL types -- enables you to define columns or rows of Java types and to query and manipulate the objects of such types.

 

Global e-Business capabilities

 

The rapidly evolution in e-Business has suddenly created an opportunity for companies to develop for global audiences. Along with this opportunity have come the problems of dealing with multiple languages, time-zones and currencies -- in even relatively straightforward applications. Customers have high expectations and the recent “dot-com crash” has demonstrated that service is a key factor for success. For global businesses this means providing worldwide operations on a 24 x 7 basis. To meet this challenge, some businesses take a “follow-the-sun” approach -- as the business day ends in one time zone and begins in another, applications “roll-over” to the new time-zone, language, currency. This means that the servers on which such applications run must support multiple locales, time-zones, or languages.

 

Globalized Date-Time

In Oracle9i, we provide a globalized Date-Time capability in the server. This capability natively supports over 500 time-zones in common use around the globe, and also provides automatic Daylight Savings Time adjustments, intra-time-zone conversion and so on. Oracle is the only major commercial database to provide such comprehensive built-in Date-Time support.

Traditionally, databases have been able to store Dates, but e-businesses increasingly need to timestamp transactions with a high degree of precision for the purposes for fulfillment, audit, performance tuning, administration or more. In Oracle9i, the SQL engine natively supports global Timestamps with precision up to a billionth of a second.

The new TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values as well as the fractional second value. The precision of the fractional second is under user-control, up to a billionth of a second. 

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone displacement in its value. The time zone displacement is the difference, in hours and minutes, between local time and the Universal Time Coordinate (UTC.) 

You can ALTER tables to modify existing data columns to TIMESTAMP, and also create new timestamp enabled applications:

CREATE TABLE new_orders as (order_date TIMESTAMP WITH TIME ZONE);

INSERT INTO new_orders SELECT SYSDATE FROM DUAL;

SELECT * FROM new_orders;

 

This returns the result:

 

order_date

---------------------------------------------------

19-JUL-01 10.00.00.000000 AM -08:00

 

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone displacement in its value. Data stored in the database is normalized to the database time zone. The time zone displacement is not stored as part of the column data -- Oracle returns the data in the users' local session time zone. Daylight Saving Time conversions can be automatically performed.

 

CREATE TABLE new_orders as (order_date TIMESTAMP WITH LOCAL TIME ZONE;

INSERT INTO new_orders VALUES ('19-JUL-01 09:34:28 AM');

SELECT * FROM new_orders;

order_date

----------------------------------------------------

19-JUL-01 09.34.28 AM

     

ALTER SESSION SET TIME_ZONE = 'EUROPE/LONDON';

SELECT * FROM new_orders;

order_date

----------------------------------------------------

19-JUL-01 02.34.28 PM

 

Unicode Support

Oracle9i also introduces comprehensive Unicode support. In the ASCII standard, each character can be represented by a byte; however, for many character-sets, especially for those representing Asian languages, multiple bytes are needed to represent a character. In Oracle9i, the server engine provides the more powerful ‘character-length’ semantics on data, versus the traditional ‘byte-length’ semantics. New data-types to hold multi-byte Unicode characters are also available.

The NCHAR data types allow character data in Unicode to be stored in a database regardless of character set. In Oracle9i, the NCHAR datatypes are available in the Unicode character set encodings UTF8 and AL16UTF16. There are corresponding NVARCHAR datatypes as well.

These new datatypes are also seamlessly supported in PL/SQL. With these fundamental enhancements, it is possible to develop powerful global e-business applications spanning timezones and language locales. More information on Unicode support in Oracle9i can be found in the ‘globalization’ whitepapers.

Large Object Enhancements

Finally, e-Business applications are rapidly embracing rich content – images, animations, video and audio. Such rich content is typically very different from numerical or character data – for instance, it is much larger in size -- and new technologies or optimizations are needed to deal with such content. In Oracl8i, the Large Object (LOB) technology was introduced to store rich content, and a number of enhancements made to this area in Oracle9i  – better API, easier migration, added functionality -- reflect the growing importance of such content for databases. Different types of LOBs are available, such as Character LOBs (CLOBs), Binary LOBs (BLOBs), Binary Files (BFILEs) and so on.

It is recommended that existing applications using LONGs should be converted to use LOBs because of the added functionality LOBs provide over LONGs. To assist in the migration process LONG API for LOBs has been introduced in Oracle9i.  The ALTER TABLE command has been enhanced to allow the datatype of a LONG column to be modified to CLOB and that of a LONG_RAW column to be modified to BLOB. SQL functions and operators that accept VARCHARs as arguments (say SUBSTR) accept CLOBs as well. Similarly, SQL functions that accept RAW as arguments accept BLOBs as well.

PL/SQL also smoothens LONG to LOB migration in Oracle9i. All predefined functions defined in the STANDARD package such as SUBSTR, INSTR etc. that accept LONG types will now accept CLOBs as well. All predefined function accepting LONG RAW, can now accept BLOBs. Implicit conversion for LOBs to VARCHARs and RAWs and vice versa is done in assignments and when passing parameter. You can define and bind LOB columns as VARCHARs and RAWs.

With respect to the Oracle Call Interface (OCI) you can now bind VARCHAR2 buffer sup to 4 GB in size for INSERT/UPDATE into CLOB column, or bind RAW buffers up to 4 GB in size for INSERT/UPDATE into BLOB column.

You can define a CLOB as VARCHAR2 to SELECT the LOB data directly into a buffer. You can INSERT/UPDATE LOBs in a single piece, or piecewise, or in array mode. Oracle9i helps you deal with larger amounts of data, with greater flexibility.

Functional indexes built on LOBs can gratly increase retrieval speed where pieces of LOBS are separately fetched. This can serve as infrastructure to XML storage. LOBs can also be used in Index-organized Tables for faster access.

With Oracle9i, the time of mainstream adoption of Large Object technology has come.

 

Completing the Object-Relational Vision

 

Oracle8 introduced support for objects in the server along with relational tables to enable the same data-model across all tiers. Most application programs running in the client or application server tiers today deal with Java, XML or C++ objects.  In addition, application complexity keeps growing, and increasingly businesses need a clean way to model all the interconnections and dependencies between their business objects. In addition to traditional ‘relational’ storage, Oracle supports object models in the database. These models can reflect business objects directly in the database. Rather than dealing with NUMBERs and CHARacters, this technology helps create higher-level abstractions, such as Names and Addresses, which are directly modeled in the server. The server thus learns all the business-level abstractions, and this reduces that the translations the programmer must perform to go from what is stored in servers to what is needed by applications. Insofar as it brings together the best of object and relational models, we call this technology object-relational.

Object-Relational technology also helps deal with arbitrarily complex types. Multimedia data types like images, maps, video clips, and audio clips were once rarely seen outside of specialty software.  Today, most Web-based applications require their database servers to manage such data.  Other software solutions need to store data dealing with financial models, engineering diagrams, or molecular structures. Further, these new data types have to be integrated with the server so that applications can gain all the benefits of the Oracle platform when it comes to dealing with complex data. Oracle’s Type System, conformant to the ANSI SQL:1999 standard, helps application developers harness simple strategies for complex data.

In Oracle9i, Oracle’s Object-Relational vision achieves functional and operational completeness with the introduction of features such as inheritance, multi-level collections, type evolution and so on. Inheritance and multi-level collections bring the server’s modeling capabilities closer to that provided by Java, C++ or XML, making it indeed easy to model business objects in the database and achieve uniformity of data models across tiers. Type evolution allows the developer or the database administrator (DBA) to change these models at run-time. Since all complex models are vulnerable to change, type evolution enables the deployment of object-relational technology with minimum operational risk.

Oracle9i also provides extensive language binding APIs. There is native support for Java inside the database and a tight integration between the object-relational type system and the Java environment.  XML is fast becoming a standard for information interchange. Using the object-relational framework, XML data can be stored, indexed and queried efficiently.

A number of high-end applications have, in recent months, indicated a preference for compiled languages such as C++ where great performance and scalability are needed. To support such applications, mapping between SQL and C++ objects is also supported to by means of a new language interface named the Oracle C++ Call Interface (OCCI). See the Oracle9i OCCI white paper for more information.

Other existing interfaces, such as the OLE/DB interface to database objects, and the Oracle Call Interface (OCI) C-based access to objects have all been strengthened though performance enhancements.

Along with a rich set of language interfaces, we also introduce a comprehensive set of utilities for object-relational data  -- including import/export, SQL loader, replication, etc. A number of development tools support Oracle’s object-relational technology for developing object-oriented software, such as Oracle JDeveloper Business Components for Java (BC4J), as well as other products offered by commercial development-tool vendors or Oracle partners.

 

Object Types

Historically, applications have focused on accessing and modifying corporate data that is stored in tables composed of native SQL data types such as INTEGER, NUMBER, DATE, and CHAR. In Oracle9i, there is support not only for these native types, but also user-defined ‘object’ data types, that are now a part of the recent ANSI SQL:1999 standard. An object type, distinct from native SQL data types, is user-defined and specifies both the underlying persistent data (called attributes of the object type) and the related behaviors (methods of the object type). Object types can be used to extend the modeling capabilities of the database beyond that provided by the native data types. You can use object types to make better models of complex entities in the real world by binding data attributes to semantic behaviors.

Inheritance

Type inheritance is a fundamental concept in any object-oriented system. Type inheritance allows sharing similarities between types as well as extending their characteristics. 

Most object-oriented applications organize their objects into types, and types into type hierarchies. Empirically, it is sufficient to organize type hierarchies into a set of trees. Thus, single type inheritance is sufficient to support type organization for most applications. Java is an object-oriented programming languages supporting single inheritance. With single inheritance, a type may extend (inherit from) one supertype. Such a type (called subtype) inherits all its supertype's attributes and methods. A subtype may also add new attributes and methods and/or override inherited methods.

One of the major benefits of inheritance is substitutability. Substitutability is the primary characteristic of type polymorphism, which allows a value of some subtype to be used where a supertype is expected  (e.g. method parameters), without any specific knowledge of the subtype being needed in advance. Instance substitutability refers to the ability to use an object value of a subtype in a context declared in terms of a supertype.  REF substitutability refers to the ability to use a REF to a subtype in a context declared in terms of a REF to a supertype.

Oracle9i supports the single type inheritance model. This is closely aligned with the ANSI SQL99 standards. Let’s take a closer look at Oracle's support for inheritance.

Type Hierarchy

The root type of a hierarchy is created using the CREATE TYPE statement and should be declared to be ‘NOT FINAL’.

 

CREATE TYPE Person_t AS OBJECT(

name VARCHAR2(100),

dob  DATE,

MEMBER FUNCTION age() RETURN number,

MEMBER FUNCTION print() RETURN varchar2) NOT FINAL;

 

A subtype can be created under a non-FINAL type. It inherits all attributes and methods from its supertype. It can add new attributes and methods and/or override inherited methods.

 

CREATE TYPE Employee_t UNDER Person_t(

   salary NUMBER,

bonus  NUMBER,

MEMBER FUNCTION wages() RETURN number,

OVERRIDING MEMBER FUNCTION print() RETURN varchar2);

A query over a table Persons of Person_t such as

SELECT VALUE(p) FROM Persons p;

 

will retrieve all Person_t instances including Employee_t instances. For more information on the Object-Relational features of Oracle9i, see the Oracle9i Objects white paper.

Method Invocation

A method is a procedure or a function that is part of an object type definition. Methods can be run within the execution environment of Oracle9i or dispatched to run outside it. Methods can be implemented in a variety of languages, not only including PL/SQL, but also C/C++, and Java.

A subtype can override any of the non-final member methods defined within its supertype and supply a different implementation. The methods in the supertype can be implemented in a different language from the overriding methods in the subtype. Oracle supports a multi-language dynamic method dispatch capability. When a method is invoked on an object instance, it is dispatched to a specific implementation based on most specific type of the instance.

Client Environments

Full support for type inheritance is available in a variety of client environments including PL/SQL, Java and C/C++. Subtype instances can be accessed and manipulated via API’s such as JDBC and OCCI (i.e., Oracle C++ Call Interface). Oracle also provides tools like JPublisher and Object Type Translator (OTT) to generate respective Java and C++ mappings from database object type hierarchies.

Collection Types

Collections are SQL data types that contain multiple elements.  Each element or value for a collection has the same substitutable data type.  In Oracle, there are two collection types – Variable Arrays (VARRAYs) and Nested Tables.

A VARRAY contains a variable number of ordered elements.  VARRAY data types can be used as a column of a table or as an attribute of an object type.

Using Oracle SQL, a (named) table type can be created.  These can be used as Nested Tables to provide the semantics of an unordered collection.  As with VARRAY, a Nested Table type can be used as a column of a table or as an attribute of an object type.

In addition, Oracle9i supports multiple levels of nesting within collections, e.g. Nested Tables or VARRAYs embedded within a Nested Table or VARRAY.  For more information on the Collection Types in Oracle9i, see the Oracle9i Objects white paper.

Type Evolution

The usage of type enables the user to evolve their business logic captured in the behavior of the type. Type Evolution is a mechanism that enables the user to change the type and propagate these changes to other schema objects that references the modified type. The schema objects that may reference a type include other types, subtypes, row objects, column objects, program units (packages, functions, procedures), views, functional indexes, or triggers.

Allowed type evolution operations include the following:

1.       Operation on a type attribute:

·         Add an attribute to a type.

·         Drop an attribute from a type.

·         Modify the type of an attribute by increasing its length, precision, or scale.

2.       Operation on a type method:

·         Add a method to a type.

·         Drop a method from a type.

3.       Changing of inheritance-related properties like INSTANTIATABLE and FINAL for an object type.

4.      Explicit propagation of a type change to its dependent types and tables.

These type evolution changes are either structural or non-structural. Structural changes are those affecting the state of the object such as adding or dropping an attribute. A non-structural change – such as renaming an attribute or adding a method -- has no effect on the state of the object.

Consider the following example:

CREATE TYPE Address_t AS OBJECT (

       street  VARCHAR(100),

       zip_code NUMBER);

 

CREATE TYPE Person_t AS OBJECT (

       first_name VARCHAR(50).

       last_name VARCHAR(50),

       age NUMBER,

       address Address_t);

 

CREATE TABLE Persons OF Person_t;

 

In the example, the Persons table is created with a column for each attribute in Person_t as well as one for each Address_t attribute. Consider the following type change example:

 

ALTER TYPE Address_t

ADD ATTRIBUTE (country NVARCHAR(100), int_postal_code NVARCHAR(20)) CASCADE;

 

The effect of this change is that two columns are added to the Persons table corresponding to the newly added attributes. The CASCADE keyword propagates the type change to dependent types and tables.

For more information on the Type Evolution in Oracle9i, see the Oracle9i Objects white paper.

 

PL/SQL Native Compilation and performance

 

A number of significant performance enhancements have been made to PL/SQL. Primary among these are native compilation, bulk binding, and pipelined table functions for PL/SQL.

Native Compilation of PL/SQL

Just as the Oracle JServer Accelerator in Oracle8i eliminated the performance bottlenecks associated with re-interpreting code for each invocation of Java business logic, so the PL/SQL native compiler compiles PL/SQL code for faster access. The greatest benefit is to applications that use stored procedures, triggers etc. and are data intensive; whether the PL/SQL runs in the application server or database server, performance can be very significantly improved. By taking fewer cycles to run a piece of logic, this also enables the Oracle server scalability to improve.

In Oracle9i, a PL/SQL library unit can be compiled as native C code, rather than interpreted as byte code. It is then stored as a shared library in the file system. The process of compiling a PL/SQL function or procedure is very simple:

ALTER FUNCTION my_func COMPILE;

Compilation results in faster execution of PL/SQL programs. The improvement in execution speed is due to the following factors:

·         Elimination of the overhead associated with interpreting byte code

·         Better, faster Control flow in native code than in interpreted code

The compiled code corresponding to a PL/SQL program is mapped to a PGA as opposed to SGA to allow better concurrent access. With native compilation, PL/SQL that does not contain SQL references can be 2-10 times faster, though performance remains dependent on a large number of application-specific factors.

Bulk Binds

A number of other functional enhancements to PL/SQL provide additional performance benefits to server-resident mission-critical business logic. Prominent among these are Bulk Bind enhancements and Bulk Dynamic SQL.

Bulk Binding allows the manipulation of an entire collection of rows in a single DML statement. In general, such binding reduces the number of database calls, and lets applications with long transactions to execute with fewer iterations Internally, bulk binds facilitate the usage of PL/SQL records and tables, improving performance.

Bulk Bind features have been enhanced in Oracle9i to support more efficient and convenient bulk bind operations. Restrictions on usage of collections in SELECT and FETCH clauses have been removed. Error handling for failure in bulk binds has been provided.

Native Dynamic SQL, introduced in Oracle8i, now supports BULK operations. The main benefit of Bulk Dynamic SQL is the improvement in performance --the number of context switches between SQL statement executor and PL/SQL engine is reduced.

PL/SQL Table Functions

Table functions introduced in Oracle8i enable various internal or external, simple or complex data stores to be represented to Oracle as tables and operated on thereafter using the relational metaphor. Oracle 9i supports pipelining and parallelization of table functions. Table Functions can be defined in PL/SQL using a native PL/SQL interface or in Java or C using the Oracle Data Cartridge Interface (ODCI).  Table functions reduce response time by piping the results as soon as they are produced, without waiting for the entire data source to be materialized in memory. Pipelining eliminates the need for buffering the produced rows. Table functions can return multiple rows during each invocation. The number of invocations is reduced, thereby improving performance.

Other secular performance improvements in PL/SQL deal with significantly faster record construction and copying, as well as improvements in dealing with cross-package references.  Oracle9i achieves a 60% reduction in the overhead of calling PL/SQL procedures from SQL statements.

With Oracle9i, PL/SQL extends the envelope for fast, reliable, data intensive server-resident business logic.

HTTP ‘Cookie’ Support in PL/SQL

Another new functionality in PL/SQL relates to the standard UTL_HTTP package. UTL_HTTP has been enhanced to enable the use of HTTP cookies in running web applications. A PL/SQL program can instruct the UTL_HTTP package to maintain HTTP cookies set by the web application. Cookies are returned to the parent application when the PL/SQL program accesses other web pages.

There is also a transparent connection improvement for processing cookies. Each request does not have to be a session; instead, all requests can be part of the same transaction.

 

SQL Standards

Oracle prides itself on being the best implementation of popular standards.  Earlier, we have discussed XML and Java integration (including ANSI SQLJ Part II for SQL/Java interoperability) with the SQL and PL/SQL environments. In Oracle9i, we introduce support for a number of pieces of standard SQL functionality – ranging from ANSI standard JOIN and CASE expressions in SQL, scalar subqueries, explicit DEFAULTs and so on. Collectively, these make Oracle’s not only a feature-rich platform for Oracle’s partners and application developers, but also a safe one from the standpoint of migration.

Joins

Oracle9i harmonizes with the SQL:1999 join syntax in terms of Cross joins, Natural joins, the Using clause, Inner joins, Right or Left outer joins, Full or two sided outer joins, as well as support for arbitrary join conditions.

CASE Expressions

Oracle9i also supports ANSI standard CASE expressions – both the simple and the searched varieties.  The simple CASE expression is similar to the DECODE function. It can be used to search and replace value within a given expression. For each searched value, a return value can be specified. For example, here is a simple CASE expression:

SELECT last_name, (CASE department_id

                    WHEN 10 THEN 'Administration'

                    WHEN 20 THEN 'Marketing'

                    WHEN 30 THEN 'Purchasing'

                   ELSE 'N/A'

                   END) as "Department Names"

FROM employees;

The searched CASE expression is similar to an ‘IF… THEN … ELSE’ construct. It can be used to conditionally search and replace values within expressions. Each ‘WHEN’ condition can be different, and multiple such conditions can be combined with logical operators. Comparison operators are also allowed in the conditional expression. For example, here is a searched CASE expression:

INSERT INTO raise (SELECT last_name,

                  CASE

                  WHEN job_id LIKE 'AD%' THEN '10%'

                  WHEN job_id LIKE 'IT%' THEN '15%'

                  WHEN job_id LIKE 'PU%' THEN '18%'

                  ELSE '20%'

                  END

                  FROM employees);

 

CASE expressions will allow developers to write compact, standard, intuitive SQL code.

Scalar Subqueries

Oracle9i also fully supports scalar subqueries, lifting the restrictions placed on scalar subquery processing in Oracle8i. You can now write subqueries in the select list of a query:

SELECT employee_id, last_name, (SELECT department_nam FROM departments d WHERE

   e.department_id = d.department_id

) department_name

 FROM employees e;

You can also write such subqueries in the WHERE clause, or in a CASE expression, in functions, and so on. Subqueries can greatly increase the readability and compactness of SQL statements.

Explicit Defaults

The new Oracle9i ‘explicit DEFAULT’ feature allows the use of DEFAULT keyword where the column default value is desired. This is of great use to developers of packaged applications, who can seed tables with defaults for cases where the user does not specify a value. Better data integrity is provided, and you do not have to scatter or ‘hard-code’ literals in application code.

 

Other Enhancements

 

We have described some of the major themes of Oracle9i in terms of SQL and PL/SQL, with the application developer’s needs in mind. A number of other enhancements (discussed in detail elsewhere in Oracle documentation, as well as in other whitepapers) make their debut in Oracle9i. A few of the important ones are listed below.

The SQL parallel query mechanism has been extended to stored procedures written in 3GL languages (PL/SQL, Java, as well as external routines). Stored procedures can now incrementally return data to the calling SQL statement. This improves performance.

Oracle9i also introduces an integrated front-end for SQL compilation. This means that PL/SQL immediately supports all SQL syntax changes for SQL statements embedded in PL/SQL. 

The SQL and PL/SQL runtime engines have been more tightly integrated to improve performance.

There are new SQL statements like UPSERTs – which update a record with new values if a record exists, else insert new records.

To aid migration to Oracle9i from other databases, scrolling cursor support has been added.

Multi-table INSERTs allow the INSERT…SELECT statement to insert rows into multiple tables as part of a single DML statement; this can be used, for example, in data warehousing systems to transfer data from one or more operational sources to a set of target tables, or for refreshing materialized views.

Constraints in SQL have been enhanced in various ways.  You can now have explicit control over how indexes are affected while creating or dropping unique and primary keys. There are improvements in the locking of un-indexed foreign keys during primary key updates, as well as improvement in the look up of primary keys during foreign key insertion allows faster foreign key insertion. Constraint enforcement is therefore more efficient.

 

OCCI

 

The Oracle C++ Call Interface (OCCI) is an application program interface (API) that provides C++ applications access to data in an Oracle database. OCCI enables C++ programmers to utilize the full range of Oracle database operations, including SQL statement processing and object manipulation. OCCI provides for:

 

     High performance applications through the efficient use of system memory and network connectivity

     Scalable applications that can service an increasing number of users and requests

     Comprehensive support for application development by using Oracle database objects, including client-side access to Oracle database objects

      Simplified user authentication and password management

      n-tiered authentication

      Consistent interfaces for dynamic connection management and transaction management in two-tier client/server environments or multitiered environments

     Encapsulated and opaque interfaces

 

OCCI provides a library of standard database access and retrieval functions in the form of a dynamic runtime library (OCCI classes) that can be linked in a C++ application at runtime. This eliminates the need to embed SQL or PL/SQL within third-generation language (3GL) programs.

 

Benefits of OCCI

 

OCCI provides these significant advantages over other methods of accessing an Oracle database:

 

     Provides OCI functionality

 

     Leverages C++ and the Object Oriented Programming paradigm

 

     Easy to use

 

     Easy to learn for those familiar with JDBC

 

     Navigational interface to manipulate database objects of user-defined types as C++ class instances

 

OCCI provides the following functionality:

     APIs to design a scalable, shared server application that can support large numbers of users securely

     SQL access functions, for managing database access, processing SQL statements, and manipulating objects retrieved from an Oracle database server

     Datatype mapping and manipulation functions, for manipulating data attributes of Oracle types

     Procedural and Nonprocedural Elements

 

A simple OCCI program is shown below.

 

const string userName = "SCOTT";

const string password = "TIGER";

const string connectString = "";

 

Environment *env = Environment::createEnvironment();

{

 

Connection *conn = env->createConnection(userName, password, connectString);

Statement *stmt = conn->createStatement("SELECT blobcol FROM mytable");

ResultSet *rs = stmt->executeQuery();

rs->next();

Blob b = rs->getBlob(1);

cout << "Length of BLOB : " << b.length();

.

.

.

stmt->closeResultSet(rs);

conn->terminateStatement(stmt);

env->terminateConnection(conn);

 

} Environment::terminateEnvironment(env);

 

 

OCCI enables you to develop scalable, shared server applications on multi-tiered architecture that combine the nonprocedural data access power of structured query language (SQL) with the procedural capabilities of C++.  In a nonprocedural language program, the set of data to be operated on is specified, but what operations will be performed, or how the operations are to be carried out, is not specified. The nonprocedural nature of SQL makes it an easy language to learn and use to perform database transactions. It is also the standard language used to access and manipulate data in modern relational and object-relational database systems.  In a procedural language program, the execution of most statements depends on previous or subsequent statements and on control structures, such as loops or conditional branches, which are not available in SQL. The procedural nature of these languages makes them more complex than SQL, but it also makes them very flexible and powerful. The combination of both nonprocedural and procedural language elements in an OCCI program provides easy access to an Oracle database in a structured programming environment.

 

Conclusion

 

At the center of Oracle’s time-tested platform stand SQL and its procedural counterpart, PL/SQL. In Oracle9i, we have tightly integrated the SQL engine with XML and Java processing in the server. The Oracle9i SQL and PL/SQL engines also introduce crucial new features needed for global e-commerce applications, XML and Java integration, standards support and complex object modeling. The new OCCI interface brings ease of development to the C++ programmer. With these enhancements, the Oracle development stack makes available today what other frameworks are promising for tomorrow.