What's New in Large Objects?

This section describes the new features in the following releases:

• LOB Features Introduced in Oracle Database 10g Release 1
• LOB Features Introduced in Oracle9i Release 2
• LOB Features Introduced in Oracle9i Release 1

LOB Features Introduced in Oracle Database 10g Release 1

The following features are introduced in Oracle Database 10g Release 1 (10.1):

• Increased LOB size limit

  The maximum size limit for LOBs is 8 to 128 terabytes, depending on your database block size. The following APIs support this new size limit:

  • DBMS_LOB PL/SQL package
  • OCI
  • JDBC

  Previous releases supported LOBs up to a maximum size of 4 GB. For details see "Terabyte-Size LOB Support".

• Performance Enhancements

  A number of performance enhancements have been added for this release including:

  • LOB performance in INSERT, UPDATE, and SELECT operations is greatly enhanced in this release. For more information on maximizing LOB performance, see "Temporary LOB Performance Guidelines".
  • Direct support for LOBs in the JDBC Thin driver.
    The JDBC Thin driver now provides direct support for BFILEs, BLOBs, and CLOBs. Prior to this release, it supported them through calls to PL/SQL routines.
• Heterogeneous Cross-Platform Transportable Tablespace Support for LOBs

  Support for LOBs in heterogeneous cross-platform transportable tablespaces is introduced in this release.

  See Also: Oracle Database Administrator's Guide for details on transportable tablespaces

• Regular Expression Support

  A set of SQL functions introduced in this release allow you to perform queries and manipulate string data stored in LOB types and other character datatypes using regular expressions.

  See Also: Oracle Database Application Developer's Guide - Fundamentals for information on supported regular expression syntax. Oracle Database SQL Reference for additional details on Oracle SQL functions for regular expressions. Oracle Database Globalization Support Guide for details on using SQL regular expression functions in a multilingual environment. Mastering Regular Expressions published by O'Reilly & Associates, Inc.

• Implicit Conversion Between NCLOB and CLOB Datatypes

  This release introduces support for implicit conversions between NCLOB and CLOB datatypes. You can perform operations such as cross-type assignments and parameter passing between these types without losing data or character set formatting properties.

  See Also: "Implicit Conversion Between CLOB and NCLOB Datatypes in SQL"

• Partitioned Index-Organized Tables - LOB Support

  LOB columns are now supported in all types of partitioned index-organized tables.

  See Also: "LOBs in Index Organized Tables"

• LogMiner Support for More Types: LONG, Multibyte CLOB and NCLOB

  LogMiner and SQL Apply now support multibyte CLOB and NCLOB data. SQL Apply now also supports LONG data. Support of additional datatypes means that you can now mine a greater variety of data.

  See Also: Oracle Data Guard Concepts and Administration

• New Column in V$TEMPORARY_LOBS

  A new column named 'ABSTRACT_LOBS' has been added to the V$TEMPORARY_LOBS table. This column displays the number of abstract LOBs accumulated in the current session. Abstract LOBs are temporary lobs returned from queries involving XMLType columns. See the Oracle Database Reference for details on the V$TEMPORARY_LOBS table.

Restrictions Removed in Oracle Database 10g Release 1

The following restrictions on LOB features were removed in Oracle Database 10g Release 1:

• NCLOB as an attribute of an object type at table creation

  In previous releases you could not specify an NCLOB as an attribute of an object type when creating a table. This restriction no longer applies.

• Restrictions on LOBs in index organized tables were removed. See "Restrictions for LOBs in Partitioned Index-Organized Tables" for more information.

LOB Features Introduced in Oracle9i Release 2

This section describes features introduced in Oracle9i Release 2 (9.2).

This release introduces new PL/SQL APIs with improved features for loading binary and character data from LOBs:

• DBMS_LOB.LOADBLOBFROMFILE

  This API enables you to load binary large objects from operating system files into internal persistent LOBs and temporary LOBs.

  See Also: "Loading a BLOB with Data from a BFILE"

• DBMS_LOB.LOADCLOBFROMFILE

  This API enables you to load character large objects from operating system files into internal persistent LOBs and temporary LOBs. This API performs the proper character set conversions from the BFILE data character set to the destination CLOB/NCLOB character set.

  See Also: "Loading a CLOB or NCLOB with Data from a BFILE"

• Parallel Execution Support for DML Operations on LOBs

  Support for parallel execution of the following DML operations on tables with LOB columns is introduced in this release. These operations run in parallel execution mode only when performed on a partitioned table. If the table is not partitioned, then these operations run in serial execution mode.

  • INSERT AS SELECT
  • CREATE TABLE AS SELECT
  • DELETE
  • UPDATE
  • MERGE (conditional UPDATE and INSERT)
  • Multitable INSERT

Restrictions Removed in Oracle9i Release 2

The following restrictions are removed in Oracle9i Release 2 (9.2):

• Trigger restrictions removed

  This release supports DML BEFORE ROW Trigger :new for LOBs. This means that triggers on LOBs follow the same rules as triggers on any other type of column.

  Prior to Release 9.2, in a PL/SQL trigger body of an BEFORE ROW DML trigger, you could read the :old value of the LOB, but you could not read the :new value.

  In releases prior to 9.2, if a view with a LOB column has an INSTEAD OF TRIGGER, then you cannot specify a string INSERT/UPDATE into the LOB column. This restriction is removed in release 9.2. For example:

  CREATE TABLE t(a LONG);
  CREATE VIEW v AS SELECT * FROM t;
  CREATE TRIGGER trig INSTEAD OF insert on v....;
  ALTER TABLE t MODIFY (a CLOB);
  INSERT INTO v VALUES ('abc');       /* works now */
  
  

• Locally managed tablespaces restriction removed

  You can now create LOB columns in locally managed tablespaces.

• LOBs in AUTO segment-managed tablespaces restriction removed

  You can now store LOBs in AUTO segment-managed tablespaces.

• NCLOB parameters

  NCLOB parameters are now allowed as attributes in object types.

• Partitioned Index Organized Tables

  Partitioned Index Organized Tables (PIOT) are now supported.

• Client-side PL/SQL DBMS_LOB procedures

  Client-side PL/SQL DBMS_LOB procedures are now supported.

• Selecting a bind variable into a LOB column

  For fetch, in prior releases, you could not use SELECT INTO to bind a character variable to a LOB column. SELECT INTO used to bind LOB locators to the column. This constraint has been removed.

LOB Features Introduced in Oracle9i Release 1

The following LOB features were introduced in Oracle9i Release 1 (9.0.1):

• Data Interface for LOBs

  Using the data interface for LOBs, you can bind and define character data for CLOB columns and binary data for BLOB columns. Doing so, enables you to insert data directly into the LOB column and select data from the LOB column without using a LOB locator.

  When using a version of the Oracle Database client that differs from the version of the Oracle Database server, queries produce different results when a client application selects a LOB column defining it as a character type or a LOB type. The following table outlines the characteristics of various Oracle Database client and server combinations in this release and prior to this release.

  Client Release	LOB Column Defined on the Client Side As	Result Using Server from Oracle Database Release 1 (9.0.1) and higher	Result Using Server prior to Oracle Database Release 1 (9.0.1)
  9.0.1 and higher	Character type	Server sends data	Client raises error.
  9.0.1 and higher	LOB type	Server sends locator	Server sends locator.
  Prior to Rel.9.0.1	Character type	Client raises an error	Client raises error.
  Prior to Rel.9.0.1	LOB type	Server sends locator	Server sends locator.

  See Also: Chapter 13, "Data Interface for Persistent LOBs"

• Using SQL Semantics with LOBs

  In this release, for the first time, you can access (internal persistent) LOBs using SQL VARCHAR2 semantics, such as SQL string operators and functions. By providing you with an SQL interface, which you are familiar with, accessing LOB data can be greatly facilitated. These semantics are recommended when using small-sized LOBs (~ 10-100KB).

  See Also: Chapter 9, "SQL Semantics and LOBs"

• Using Oracle C++ Call Interface (OCCI) with LOBs

  Oracle C++ Call Interface (OCCI) is a new C++ API for manipulating data in an Oracle database. OCCI is organized as an easy-to-use set of C++ classes which enable a C++ program to connect to a database, run SQL statements, insert/update values in database tables, retrieve results of a query, run stored procedures in the database, and access metadata of database schema objects. OCCI API provides advantages over JDBC and ODBC.

  See Also: Chapter 6, "Overview of Supplied LOB APIs"

• New JDBC LOB Functionality

  The following are new JDBC LOB-related functionality:

  • Temporary LOB APIs: create temporary LOBs and destroy temporary LOBs
  • Trim APIs: trim the LOBs to the specified length
  • Open and Close APIs: open and close LOBs explicitly
  • New Streaming APIs: read and write LOBs as Java streams from the specified offset.
  • Empty LOB instances can now be created with JDBC. The instances do not require database round trips.
• Support for LOBs in Partitioned Index-Organized Tables

  Oracle9i introduces support for LOB, VARRAY columns stored as LOBs, and BFILEs in partitioned index-organized tables. Results of queries on LOB columns in these tables is similar to that of LOB columns in conventional (heap-organized) partitioned tables, except for a few minor differences.

  See Also: Chapter 5, "Advanced Design Considerations"

• Using OLEDB and LOBs (new to this manual)

  OLE DB is an open specification for accessing various types of data from different stores in a uniform way. OLEDB supports the following functions for these LOB types:

  • Persistent LOBs: READ/WRITE through the rowset.
  • BFILEs: READ-ONLY through the rowset.

    See Also: "Oracle Provider for OLE DB (OraOLEDB)"

Restrictions Removed in Oracle9i Release 1

This section describes restrictions removed in Oracle9i Release 1 (9.0.1).

In earlier releases, you could not call functions and procedures in DBMS_LOB packages from client-side PL/SQL. This restriction is removed in release version 9.0.1. In this release, you can call DBMS_LOB functions and procedures from client-side or server-side PL/SQL.