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> drop table facebook;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.