Empty Versus NULL LOBs

Now that you understand the distinction between a LOB locator and the value to which it points, you need to wrap your mind around another key concept: the empty LOB. An empty LOB is what you have when a LOB locator doesn't point to any LOB data. This is not the same as a NULL LOB, which is a LOB column (or variable) that doesn't hold a LOB locator. Clear as mud, right? Let's look at some example code:

SQL> DECLARE 2 directions CLOB; 3 BEGIN 4 IF directions IS NULL THEN 5 DBMS_OUTPUT.PUT_LINE('directions is NULL'); 6 ELSE 7 DBMS_OUTPUT.PUT_LINE('directions is not NULL'); 8 END IF; 9 END; 10 / directions is NULL

Here we've declared a CLOB variable, which is NULL because we haven't yet assigned it a value. You're used to this behavior, right? It's the same with any other datatype: declare a variable without assigning a value and the result is NULL. Let's press ahead and create a LOB locator for the variable. The following code uses a call to EMPTY_CLOB in conjunction with an INSERT statement to create a LOB locator. Subsequently, a SELECT statement retrieves that same LOB locator from the database and places it into the directions variable. We'll talk more about the reasons for this somewhat cumbersome approach in the next section. For now, focus on the output from this code snippet.

First the code:

DECLARE directions CLOB;BEGIN --Delete any existing rows for 'Munising Falls' so that this --example can be executed multiple times DELETE FROM waterfalls WHERE falls_name='Munising Falls'; --Insert a new row using EMPTY_CLOB( ) to create a LOB locator INSERT INTO waterfalls (falls_name,falls_directions) VALUES ('Munising Falls',EMPTY_CLOB( )); --Retrieve the LOB locater created by the previous INSERT statement SELECT falls_directions INTO directions FROM waterfalls WHERE falls_name='Munising Falls'; IF directions IS NULL THEN DBMS_OUTPUT.PUT_LINE('directions is NULL'); ELSE DBMS_OUTPUT.PUT_LINE('directions is not NULL'); END IF; DBMS_OUTPUT.PUT_LINE('Length = ' || DBMS_LOB.GETLENGTH(directions));END;

and now the output:

directions is not NULLLength = 0

EMPTY_CLOB is a built-in function that returns a CLOB locator, which we've stored in the database and then retrieved. Our CLOB variable is now no longer NULL because it contains a value: the locator. However, the results from the call to DBMS_LOB.GETLENGTH indicate that there is no data being pointed to; thus, the directions CLOB is an empty LOB. This is important to understand because the way in which you test for the presence or absence of data is more complicated with a LOB than it is for other datatypes.

A simple IS NULL test suffices for traditional scalar datatypes:

IF some_number IS NULL THEN --You know there is no data

If an IS NULL test on a NUMBER or a VARCHAR2 (or any other scalar type) returns TRUE, you know that the variable holds no data. With LOBs, however, you not only need to check for nullity (no locator), but you also need to check the length:

IF some_clob IS NULL THEN --There is no dataELSEIF DBMS_LOB.GETLENGTH(some_clob) = 0 THEN --There is no dataELSE --Only now is there dataEND IF;

As illustrated in this example, you can't check the length of a LOB without first having a locator. Thus, to determine whether a LOB holds data, you must first check for the presence of a locator using an IS NULL test, and then check for a non-zero length.