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: