CLOBs and NULL values in Oracle Database

?? Tip for newbie Oracle DB Developers

It's a piece of common knowledge that an empty string ('') is equivalent to NULL in the Oracle Database. While this might be taken for granted by some new developers, the underlying low-level and logical reasons for this distinction (you may google it) remain significant for different datatypes.

When working with CLOBs (Character Large Objects) from scratch and especially modifying your code, you might encounter unexpected behaviour. For instance, uninitialized CLOB values may return TRUE in null checks. However, built-in functions that handle CLOBs similarly to varchars (such as SUBSTR, REPLACE, REGEXP_*, etc.) will return empty CLOBs. These empty CLOBs are not NULL, but they are equivalent to EMPTY_CLOB when processed in PL/SQL.

For more details, you can refer to the (https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/EMPTY_BLOB-EMPTY_CLOB.html)

So, for a simple dataset

WITH ASDF AS ( 
            SELECT 
                1                AS ID, 
                NULL             AS VAL, 
                'just null clob' AS COMM 
            FROM 
                DUAL 
            UNION ALL 
            SELECT 
                2                                            AS ID, 
                'asdf'                                       AS VAL, 
                'clob thats will be trimmed to empty string' AS COMM 
            FROM 
                DUAL 
            UNION ALL 
            SELECT 
                3             AS ID, 
                'asdfghjk'    AS VAL, 
                'normal clob' AS COMM 
            FROM 
                DUAL 
        )        

We are getting SQL examples of unexpected behaviour

So you need to solve this situation with additional care using a comparison with

dbms_lob.compare( VAL, empty_clob() ) = 0        

The same for PL/SQL

 X.VAL = EMPTY_CLOB()        

Fulltext examples in liveSQL

https://livesql.oracle.com/ords/livesql/s/cxklu3ys8rdopdiwj1gcj8mzb

#PLSQL #OracleDB #ForBeginers #SQL