This section describes the new features in the following releases:
Insert, update, and select of remote LOBs is supported, subject to some remaining restrictions.
Read and write of LOB data for multiple locators is supported by new OCI functions.
The JDBC 3.0 API is now supported for Java access to LOBs.
The Data Interface is now supported in Java.
Some phases of online redefinition of LONG-to-LOB migration can execute in parallel.
Directory objects can be managed by Enterprise Manager Web console.
Three new LOB session-level statistics are now available to users: LOB reads, LOB writes, and LOB writes unaligned.
The following restrictions on LOB features were removed in Oracle Database 10g Release 2.
You can initialize a LOB attribute to character or raw data of any size. Earlier, this feature was available only for top-level LOB columns.
See "Initializing Persistent LOB Columns and Attributes to a Value "
Select of remote LOB data defined as character or raw datatype is supported for the OCI, JDBC, and PL/SQL interfaces.
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.
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:
|
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.
Partitioned Index-Organized Tables - LOB Support
LOB columns are now supported in all types of partitioned 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.
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.
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.
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.
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.
Parallel Execution Support for DML Operations on LOBs
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 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.
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 error. | Client raises error. |
| Prior to Rel.9.0.1 | LOB type | Server sends locator. | Server sends locator. |
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).
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.
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.
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.
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.