5 SQL Semantics for LOBs
You can use various SQL mechanisms to operate on LOBs.
You can access CLOB and NCLOB data types using SQL
VARCHAR2 semantics, such as SQL string operators and functions.
These techniques allow you to use LOBs directly in SQL code and provide an alternative
to using LOB-specific APIs for some operations, and are beneficial in the following
situations:
- When performing operations on LOBs that are relatively small in size, i.e., up to about 100K bytes
- After migrating your database from
LONGcolumns to LOB data types, so that any SQL string functions contained in your existing PL/SQL application continue to work
SQL semantics are not recommended in the following situations, you must use
LOB APIs instead:
- When using advanced features such as random access and piece-wise fetch.
- When performing operations on LOBs that are relatively large in size (greater than 1MB), because using SQL semantics can impact performance.
Note:
SQL semantics are used with persistent and temporary LOBs, and do not apply to BFILEs.- SQL Functions and Operators Supported for Use with LOBs
Many SQL operators and functions that takeVARCHAR2columns as arguments, also accept LOB columns. The following list summarizes those categories of SQL functions and operators that are supported for use with LOBs. - Detailed Semantics of SQL Operations on LOBs
This section explains semantics of SQL operations on LOBs in details. - Restrictions on SQL Operations on LOBs
There are many SQL operations that are not supported on LOB columns. This section lists those operations.