Oracle PL/SQL Tutorial/Large Objects/EMPTY BLOB

EMPTY_BLOB() function

   <source lang="sql">

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></source>

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

   <source lang="sql">

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.</source>