Oracle PL/SQL Tutorial/Large Objects/EMPTY BLOB

EMPTY_BLOB() function

SQL>
SQL> CREATE TABLE myBlob
  2  (id NUMBER PRIMARY KEY,
  3   blob_data BLOB);
Table created.
SQL>
SQL>
SQL> UPDATE myBlob
  2  SET blob_data =EMPTY_BLOB()
  3  WHERE id =1;
0 rows updated.
SQL>
SQL> drop table myBlob;
Table dropped.
SQL>
SQL>

Insert a new row using EMPTY_CLOB() to create a LOB locator

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.