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.