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 LONG columns 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.