|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)
Part Number A96591-01
This section describes the new features in the following releases:
This release introduces new PL/SQL APIs with improved features for loading binary and character data from LOBs:
This API allows you to load binary large objects from operating system files into internal persistent LOBs, temporary LOBs, and external LOBs (BFILES).
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.
The following restrictions are removed in Oracle9i Release 2.
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.
You can now create LOB columns in locally managed tablespaces.
You can now store LOBs in
AUTO segment-managed tablespaces.
NCLOB parameters are now allowed as attributes in object types.
Partitioned Index Organized Tables (PIOT) are now supported.
Client-side PL/SQL DBMS_LOB procedures are now supported.
Prior to Release 9.2, in a PL/SQL trigger body of an
ROW DML trigger, you could read the
:old value of the LOB, but you could not read the
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:
The following sections describe the new features in Oracle9i Large Objects (LOBs):
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.
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).
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.
The following are new JDBC LOB-related functionality:
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.
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:
There was no change in LOB functionality between Oracle8i Release 2 (8.1.6) and Oracle8i Release 3 (8.1.7).
New LOB features introduced in Oracle8i, Release 2 (8.1.6) were:
A CACHE READS option for LOB columns.
The 4,000 byte restriction for bind variables binding to an internal LOB was removed.
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.
New LOB features included in the Oracle8i, Release 8.1.5 are:
LOBs in partitioned tables