Checking for NOT NULL Values in a BLOB Column

INTRO : The PL/SQL function was executed for all table records, even though the condition BLOB_COLUMN IS NOT NULL was specified. Filtering out NULL BLOB columns was required.

In this post, I will share a simple solution that we implemented to improve query performance and error handling. Recently, we reviewed and updated all the packages in our database that contained the “WHEN OTHERS THEN NULL” blocks, replacing them with more robust error-handling code. The original “WHEN OTHERS THEN NULL” blocks were causing many errors to be silently discarded, making it difficult to diagnose underlying issues. As Thomas Kyte famously said, “A WHEN OTHERS is almost always a bug unless it is immediately followed by a RAISE.” During the process of modifying these packages, we started uncovering numerous errors that had previously been ignored.

One of the errors we identified was :

ORA-01405: fetched column value is NULL error

This error occurred in a PL/SQL function, where NULL values were being passed as input when they shouldn’t have been. I will provide a simple test case to simulate this scenario, using the HR schema as an example.

For illustration, let’s assume we have a table that stores employees’ favorite football teams, which are stored as encrypted data using DBMS_CRYPTO in a BLOB column. The table is structured such that the column containing this encrypted data is nullable, and some records may contain NULL values.

We will search the table for rows that contain the input keyword and determine whether the data exists or not. Since the column is nullable, some rows may have NULL values, which could lead to issues if not handled correctly.

Create a sample test table:

CREATE TABLE HR.ENCRYPTED_DATA_TABLE
(
    ID               NUMBER  GENERATED ALWAYS AS IDENTITY  NOT NULL PRIMARY KEY,
    EMPLOYEE_ID      NUMBER (8) NOT NULL,
    SECRET_DATA      BLOB
);

Now, I will create a basic encryption and decryption function. To use DBMS_CRYPTO, explicit privileges are required.

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO HR;

CREATE TABLE HR.CATCH_ERROR
(
    ID           NUMBER   GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
    RECORD_TIME  DATE     DEFAULT SYSDATE,
    ERROR_MSG    VARCHAR2 (2000)
);

CREATE OR REPLACE PACKAGE HR.encryption_package
AS
    FUNCTION fn_encrypt (P_SECRET_KEY IN VARCHAR2, P_DATA IN CLOB)
        RETURN BLOB;

    FUNCTION fn_decrypt (P_SECRET_KEY IN VARCHAR2, P_ENCRYPTED_DATA IN BLOB)
        RETURN VARCHAR2;
END encryption_package;

CREATE OR REPLACE PACKAGE BODY HR.encryption_package
AS
    FUNCTION fn_encrypt (P_SECRET_KEY IN VARCHAR2, P_DATA IN CLOB)
        RETURN BLOB
    AS
        p_encrypted_data   BLOB;
        p_error            VARCHAR2 (2000);
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        DBMS_LOB.createtemporary (p_encrypted_data, TRUE);
        sys.DBMS_CRYPTO.encrypt (
            dst   => p_encrypted_data,
            src   => P_DATA,
            KEY   => UTL_I18N.string_to_raw (P_SECRET_KEY, 'AL32UTF8'),
            typ   =>
                  sys.DBMS_CRYPTO.encrypt_aes128
                + sys.DBMS_CRYPTO.chain_cbc
                + sys.DBMS_CRYPTO.pad_pkcs5);

        RETURN p_encrypted_data;
    EXCEPTION
        WHEN OTHERS
        THEN
            --RAISE ;
            p_error := SQLERRM;

            INSERT INTO HR.CATCH_ERROR (error_msg)
                     VALUES (
                                   'Error raised from encryption_package.fn_decrypt:'
                                || p_error);

            COMMIT;
            RETURN NULL;
    END fn_encrypt;

    FUNCTION fn_decrypt (P_SECRET_KEY IN VARCHAR2, P_ENCRYPTED_DATA IN BLOB)
        RETURN VARCHAR2
    AS
        p_clear_text   CLOB;
        p_error        VARCHAR2 (2000);
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        DBMS_LOB.createtemporary (p_clear_text, TRUE);

        sys.DBMS_CRYPTO.decrypt (
            dst   => p_clear_text,
            src   => P_ENCRYPTED_DATA,
            KEY   => UTL_I18N.string_to_raw (P_SECRET_KEY, 'AL32UTF8'),
            typ   =>
                  sys.DBMS_CRYPTO.encrypt_aes128
                + sys.DBMS_CRYPTO.chain_cbc
                + sys.DBMS_CRYPTO.pad_pkcs5);

        RETURN DBMS_LOB.SUBSTR (p_clear_text, 4000, 1);
    EXCEPTION
        WHEN OTHERS
        THEN
            --RAISE ;
            p_error := SQLERRM;

            INSERT INTO HR.CATCH_ERROR (error_msg)
                     VALUES (
                                   'Error raised from encryption_package.fn_decrypt:'
                                || p_error);

            COMMIT;

            RETURN NULL;
    END fn_decrypt;
END encryption_package;

Now, I will insert some data and prepare for the test.

INSERT  INTO HR.ENCRYPTED_DATA_TABLE (EMPLOYEE_ID,SECRET_DATA) 
VALUES (17, HR.encryption_package.fn_encrypt('1923mysecret1919',TO_CLOB('Besiktas'))) ;

INSERT  INTO HR.ENCRYPTED_DATA_TABLE (EMPLOYEE_ID,SECRET_DATA) VALUES (23, null) ;

Now, we have two records in the ENCRYPTED_DATA_TABLE. One record has a NULL value in the SECRET_DATA column, while the other contains an encrypted BLOB. The actual topic of this blog post begins here.

The goal is to search for decrypted data and return results for people who support the Besiktas football team. Since the decryption function no longer discards error messages, the query does not return an error. However, an error log message is recorded in the CAUGHT_ERRORS table, allowing us to be aware of the error.

select employee_id,  from HR.ENCRYPTED_DATA_TABLE where encryption_package.fn_decrypt('1923mysecret1919',SECRET_DATA) = 'Besiktas';

select record_time, error_msg from HR.catch_error ;
RECORD_TIME            ERROR_MSG
-----------            ---------
10/01/2025 14:47:04    Error raised from encryption_package.fn_decrypt:ORA-01405: fetched column value is NULL

What I initially tried was simply adding a NOT NULL condition, hoping that this would prevent the errors, since NULL blob rows will be filtered but it did not work.

SELECT employee_id
  FROM HR.ENCRYPTED_DATA_TABLE
 WHERE HR.encryption_package.fn_decrypt ('1923mysecret1919', SECRET_DATA) =
       'Besiktas' and SECRET_DATA is not null;

select record_time, error_msg from HR.catch_error ;
RECORD_TIME            ERROR_MSG
-----------            ---------
10/01/2025 14:47:04    Error raised from encryption_package.fn_decrypt:ORA-01405: fetched column value is NULL
10/01/2025 14:50:04    Error raised from encryption_package.fn_decrypt:ORA-01405: fetched column value is NULL

Although only non-null records were queried, the DECRYPT function was executed for every row.

The simple solution I found was to add a DBMS_LOB.GET_LENGTH() > 0 condition to filter out the NULL BLOB columns. This not only resolved the issue of filtering non-null records but also improved query performance. If the length is greater than 0, it indicates that the BLOB is not NULL.

SELECT employee_id
  FROM HR.ENCRYPTED_DATA_TABLE edt, hr.employees e
 WHERE edt.employee_id=e.employee_id and hr.encryption_package.fn_decrypt ('1923mysecret1919', SECRET_DATA) = 'Besiktas' and dbms_lob.get_length(SECRET_DATA )>0 ;

Hope it helps.