Insert a new row using EMPTY_CLOB() to create a LOB locator : EMPTY_BLOB « Large Objects « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Large Objects
3. EMPTY_BLOB

SQL>
SQL> CREATE TABLE facebook (
  2     name VARCHAR2(80),
  3     photo BLOB,
  4     directions CLOB,
  5     description NCLOB,
  6     web_page BFILE);

Table created.

SQL>
SQL> CREATE DIRECTORY bfile_data AS 'c:\xxx';

Directory created.

SQL> Demonstrates the difference between an empty LOB and a NULL LOB
SP2-0734: unknown command beginning "Demonstrat..." - rest of line ignored.
SQL> DECLARE
  2     directions CLOB;
  3  BEGIN
  4
  5     DELETE FROM facebook WHERE name='Falls';
  6
  7
  8     INSERT INTO facebook
  9               (name,directions)
 10        VALUES ('Falls',EMPTY_CLOB());
 11
 12     SELECT directions
 13       INTO directions
 14       FROM facebook
 15      WHERE name='Falls';
 16
 17     IF directions IS NULL THEN
 18        DBMS_OUTPUT.PUT_LINE('directions is NULL');
 19     ELSE
 20        DBMS_OUTPUT.PUT_LINE('directions is not NULL');
 21     END IF;
 22
 23     DBMS_OUTPUT.PUT_LINE('Length = '|| DBMS_LOB.GETLENGTH(directions));
 24  END;
 25  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table facebook;

Table dropped.

SQL> drop directory bfile_data;

Directory dropped.