What's New with Large Objects (LOBs)?

This section describes the new features in the following releases:

• LOB Features Introduced with Oracle9i, Release 2 (9.2)
• LOB Features Introduced with Oracle9i, Release 1 (9.0.1)
• LOB Features Introduced with Oracle8i Release 2 (8.1.6)
• LOB Features Introduced with Oracle8i, Release 8.1.5

LOB Features Introduced with 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 allows you to load binary large objects from operating system files into internal persistent LOBs, temporary LOBs, and external LOBs (BFILES).

  See Also: "Loading an Internal Persistent BLOB with Binary Data from a BFILE" "Loading a Temporary BLOB with Binary Data from a BFILE" "Loading a BLOB with BFILE Data"

• DBMS_LOB.LOADCLOBFROMFILE

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

  See Also: "Loading an Internal Persistent CLOB with BFILE Data" "Loading a Temporary CLOB/NCLOB with Character Data from a File" "Loading a CLOB with BFILE Data"

Removed Restrictions

The following restrictions are removed in Oracle9i Release 2.

• Trigger restriction removed

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

• 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.

• PL/SQL trigger body

  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.

• Trigger support

  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 */
  
  

LOB Features Introduced with Oracle9i, Release 1 (9.0.1)

The following sections describe the new features in Oracle9i Large Objects (LOBs):

• LONG-to-LOB Migration API

  To assist you in migrating to LOBs, Oracle now supports the LONG API for LOBs. This API ensures that when you change your LONG columns to LOBs, your existing applications will require few changes, if any. When possible, change your existing applications to use LOBs instead of LONGs because of the added benefits that LOBs provide.

  See Also: Chapter 8, "Migrating From LONGs to 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 7, "Modeling and Design"

• 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 collection of C++ classes which enable a C++ program to connect to a database, execute SQL statements, insert/update values in database tables, retrieve results of a query, execute stored procedures in the database, and access metadata of database schema objects. OCCI API provides advantages over JDBC and ODBC.

  See Also: Chapter 3, "LOB Support in Different Programmatic Environments" Chapter 10, "Internal Persistent LOBs"

• 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

    See Also: Chapter 3, "LOB Support in Different Programmatic Environments" Chapter 10, "Internal Persistent LOBs" Chapter 11, "Temporary LOBs" Chapter 12, "External LOBs (BFILEs)"

• 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. The behavior of 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, "Large Objects: Advanced Topics"

• 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: Chapter 13, "Using OraOLEDB to Manipulate LOBs"

LOB Features Introduced with Oracle8i Release 2 (8.1.6)

New LOB features introduced in Oracle8i, Release 2 (8.1.6) were:

• CACHE READS

  A CACHE READS option for LOB columns.

• 4,000 byte restriction removed

  The 4,000 byte restriction for bind variables binding to an internal LOB was removed.

• Binding More Than 4,000 Bytes of Data

  From Oracle8i Release 2 (8.1.6) and higher, Oracle supports binding more than 4,000 bytes of data to internal LOB columns in INSERT and UPDATE statements.

  If a table has LONG and LOB columns, you can bind more than 4,000 bytes of data for either the LONG column or the LOB columns, but not both in the same statement.

LOB Features Introduced with Oracle8i, Release 8.1.5

New LOB features included in the Oracle8i, Release 8.1.5 are:

• Temporary LOBs
• Varying width CLOB and NCLOB support
• Support for LOBs in partitioned tables
• New API for LOBs (open/close/isopen, writeappend, getchunksize)
• Support for LOBs in non-partitioned index-organized tables
• Copying the value of a LONG to a LOB