You can use various SQL mechanisms to operate on LOBs.
You can access
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
- 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
- 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.