Oracle PL/SQL/Data Type/Clob

Append data to clob

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clobData CLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
  2  BEGIN
  3    SELECT clobData INTO clob_par FROM myTable WHERE id = id_par;
  4  END initClob;
  5  /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
  2    clobVariable CLOB;
  3    charVariable VARCHAR2(50);
  4    offsetPos INTEGER := 1;
  5    amount_var INTEGER := 50;
  6  BEGIN
  7    initClob(clobVariable, id_par);
  8    DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable);
  9    DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable);
 10    DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
 11  END readClob;
 12  /
Procedure created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE append_example IS
  2    clobSrc CLOB;
  3    clobDest CLOB;
  4  BEGIN
  5    SELECT clobData INTO clobSrc FROM myTable WHERE id = 2;
  6    SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE;
  7
  8    readClob(1);
  9    DBMS_LOB.APPEND(clobDest, clobSrc);
 10    readClob(1);
 11
 12  END append_example;
 13  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Appending and Writing to LOBs

  
SQL>
SQL>
SQL> CREATE TABLE LOBS(
  2       lob_index INTEGER,
  3       CLOB_Locator CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO LOBS VALUES(1,"Teach Yourself Oracle8i in 21 Days");
1 row created.
SQL> INSERT INTO LOBS VALUES(2,"Oracle Data Warehousing Unleashed");
1 row created.
SQL> INSERT INTO LOBS VALUES(3,"");
1 row created.
SQL> INSERT INTO LOBS VALUES(4,"Oracle Unleashed 2E");
1 row created.
SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());
1 row created.
SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());
1 row created.
SQL>
SQL> DECLARE
  2      Source_Lob   CLOB;
  3      Dest_Lob   CLOB;
  4      Write_Amount INTEGER := 10;
  5      Writing_Position INTEGER ;
  6      Buffer VARCHAR2(10) := "Added Text";
  7  BEGIN
  8      SELECT CLOB_LOCATOR into Dest_LOB
  9           FROM LOBS
 10           WHERE LOB_INDEX = 5 FOR UPDATE; -- Locks Row for Update
 11      SELECT CLOB_LOCATOR into Source_LOB
 12           FROM LOBS
 13           WHERE LOB_INDEX = 1;
 14      DBMS_LOB.APPEND(Dest_LOB, Source_LOB);
 15      COMMIT;
 16      SELECT CLOB_LOCATOR into Source_LOB
 17           FROM LOBS
 18           WHERE LOB_INDEX = 6 FOR UPDATE;  -- Locks Row for Update
 19
 20      Writing_Position := DBMS_LOB.GETLENGTH(Source_Lob) + 1;
 21      DBMS_LOB.WRITE(Source_LOB,Write_Amount,Writing_Position,Buffer);
 22      COMMIT;
 23  END;
 24  /
PL/SQL procedure successfully completed.
SQL> drop table lobs;
Table dropped.
SQL> --

Assign string value to clob type variable

 
SQL>
SQL>
SQL> declare
  2      v1 clob := "abc";
  3  begin
  4      If v1 = "abc" then null; end if;
  5
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>

clob data type

 
SQL> create table MyTest(
  2     ch clob
  3  );
Table created.
SQL>
SQL> insert into myTest values("A simple line of text");
1 row created.
SQL> insert into myTest values("Line 1; \n line 2");
1 row created.
SQL>
SQL> select * from mytest;
CH
--------------------------------------------------------------------------------
A simple line of text
Line 1; \n line 2
SQL>
SQL>
SQL> drop table myTest;
Table dropped.

Clob type column

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );
Table created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

clob type data as string

 
SQL> CREATE TABLE nmyTable (
  2    id INTEGER PRIMARY KEY,
  3    nclobData NCLOB
  4  );
Table created.
SQL> CREATE TABLE myTable (
  2    id INTEGER PRIMARY KEY,
  3    clobData CLOB
  4  );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE nclob_example
  2  AS
  3    clobVariable CLOB := "It is the east and Juliet is the sun";
  4    nclobVariable NCLOB;
  5  BEGIN
  6    INSERT INTO nmyTable (id, nclobData) VALUES (1, clobVariable);
  7
  8    SELECT nclobData INTO clobVariable FROM nmyTable WHERE id = 1;
  9
 10    DBMS_OUTPUT.PUT_LINE("clobVariable = " || clobVariable);
 11  END nclob_example;
 12  /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table nmyTable;
Table dropped.

Compare clob data

 
SQL>
SQL> declare
  2      v1 clob := "abc";
  3  begin
  4
  5      If v1 >= "abc" then null; end if;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>

Compare two clobs

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clobData CLOB NOT NULL
  4  );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE compare_example IS
  2    clobVariable1 CLOB;
  3    clobVariable2 CLOB;
  4    return_var INTEGER;
  5  BEGIN
  6    SELECT clobData INTO clobVariable1 FROM myTable WHERE id = 1;
  7    SELECT clobData INTO clobVariable2 FROM myTable WHERE id = 2;
  8
  9    return_var := DBMS_LOB.ruPARE(clobVariable1, clobVariable2);
 10    DBMS_OUTPUT.PUT_LINE("return_var = " || return_var);
 11
 12    return_var := DBMS_LOB.ruPARE(clobVariable1, clobVariable1);
 13    DBMS_OUTPUT.PUT_LINE("return_var = " || return_var);
 14  END compare_example;
 15  /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.

Copy clob data

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clobData CLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
  2  BEGIN
  3    SELECT clobData INTO clob_par FROM myTable WHERE id = id_par;
  4  END initClob;
  5  /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
  2    clobVariable CLOB;
  3    charVariable VARCHAR2(50);
  4    offsetPos INTEGER := 1;
  5    amount_var INTEGER := 50;
  6  BEGIN
  7    initClob(clobVariable, id_par);
  8    DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable);
  9    DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable);
 10    DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
 11  END readClob;
 12  /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE copy_example IS
  2    clobSrc CLOB;
  3    clobDest CLOB;
  4    src_offsetPos INTEGER := 1;
  5    dest_offsetPos INTEGER := 7;
  6    amount_var INTEGER := 5;
  7  BEGIN
  8    SELECT clobData INTO clobSrc FROM myTable WHERE id = 2;
  9    SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE;
 10
 11    readClob(1);
 12    DBMS_LOB.COPY(clobDest, clobSrc, amount_var,dest_offsetPos, src_offsetPos);
 13    readClob(1);
 14
 15    ROLLBACK;
 16  END copy_example;
 17  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Copying Internal LOBs

  
SQL>
SQL> CREATE TABLE LOBS(
  2       lob_index INTEGER,
  3       CLOB_Locator CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO LOBS VALUES(1,"Teach Yourself Oracle8i in 21 Days");
1 row created.
SQL> INSERT INTO LOBS VALUES(2,"Oracle Data Warehousing Unleashed");
1 row created.
SQL> INSERT INTO LOBS VALUES(3,"");
1 row created.
SQL> INSERT INTO LOBS VALUES(4,"Oracle Unleashed 2E");
1 row created.
SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());
1 row created.
SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());
1 row created.
SQL>
SQL> DECLARE
  2      Source_Lob   CLOB;
  3      Dest_Lob   CLOB;
  4      Copy_Amount INTEGER;
  5  BEGIN
  6      SELECT CLOB_LOCATOR into Dest_LOB
  7           FROM LOBS
  8           WHERE LOB_INDEX = 5 FOR UPDATE; -- FOR UPDATE locks the ROW
  9      SELECT CLOB_LOCATOR into Source_LOB
 10           FROM LOBS
 11           WHERE LOB_INDEX = 1;
 12      Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
 13      DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
 14      COMMIT;
 15      SELECT CLOB_LOCATOR into Dest_LOB
 16           FROM LOBS
 17           WHERE LOB_INDEX = 6 FOR UPDATE;
 18      SELECT CLOB_LOCATOR into Source_LOB
 19           FROM LOBS
 20           WHERE LOB_INDEX = 2;
 21      Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
 22      DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
 23      COMMIT;
 24  END;
 25  /
PL/SQL procedure successfully completed.
SQL> drop table lobs;
Table dropped.
SQL>
SQL> --

Creating an Internal LOB Table

  
SQL> --
SQL>
SQL>
SQL> CREATE TABLE LOBS(
  2       lob_index INTEGER,
  3       CLOB_Locator CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO LOBS VALUES(1,"T");
1 row created.
SQL> INSERT INTO LOBS VALUES(2,"O");
1 row created.
SQL> INSERT INTO LOBS VALUES(3,"");
1 row created.
SQL> INSERT INTO LOBS VALUES(4,"Oracle Unleashed 2E");
1 row created.
SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());
1 row created.
SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());
1 row created.
SQL>
SQL>
SQL> drop table lobs;
Table dropped.
SQL>
SQL> --

DBMS_LOB package.

  
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE ReverseLOB(
  2    p_InputLocator IN CLOB,
  3    p_OutputLocator IN OUT CLOB,
  4    p_ChunkSize IN NUMBER) AS
  5
  6    v_InputOffset      BINARY_INTEGER;
  7    v_OutputOffset     BINARY_INTEGER;
  8    v_LOBLength        BINARY_INTEGER;
  9    v_CurrentChunkSize BINARY_INTEGER;
 10    e_TrimLength       EXCEPTION;
 11    PRAGMA EXCEPTION_INIT(e_TrimLength, -22926);
 12
 13  BEGIN
 14    v_LOBLength := DBMS_LOB.GETLENGTH(p_InputLocator);
 15
 16    BEGIN
 17      DBMS_LOB.TRIM(p_OutputLocator, v_LOBLength);
 18    EXCEPTION
 19      WHEN e_TrimLength THEN
 20        NULL;
 21    END;
 22
 23    v_InputOffset := 1;
 24    v_OutputOffset := v_LOBLength + 1;
 25
 26    LOOP
 27      EXIT WHEN v_InputOffset > v_LOBLength;
 28
 29      IF (v_LOBLength - v_InputOffset + 1) > p_ChunkSize THEN
 30        v_CurrentChunkSize := p_ChunkSize;
 31      ELSE
 32        v_CurrentChunkSize := v_LOBLength - v_InputOffset + 1;
 33      END IF;
 34
 35      v_OutputOffset := v_OutputOffset - v_CurrentChunkSize;
 36
 37      DBMS_LOB.COPY(p_OutputLocator,
 38                    p_InputLocator,
 39                    v_CurrentChunkSize,
 40                    v_OutputOffset,
 41                    v_InputOffset);
 42
 43      v_InputOffset := v_InputOffset + v_CurrentChunkSize;
 44    END LOOP;
 45  END ReverseLOB;
 46  /
Procedure created.
SQL>
SQL>
SQL> CREATE TABLE lobdemo (
  2    key NUMBER,
  3    clob_col CLOB,
  4    blob_col BLOB);
Table created.
SQL>
SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (1, "abcdefghijklmnopqrstuvwxyz");
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (2, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (3, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (4, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (5, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
  2    VALUES (6, EMPTY_CLOB());
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT key, clob_col
  2    FROM lobdemo
  3    WHERE key BETWEEN 1 AND 6
  4    ORDER BY key;
       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         1
abcdefghijklmnopqrstuvwxyz
         2

         3

       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         4

         5

         6

6 rows selected.
SQL>
SQL> DECLARE
  2    v_Source CLOB;
  3    v_Destination CLOB;
  4    v_Key NUMBER;
  5    CURSOR c_Destinations IS
  6      SELECT key, clob_col
  7      FROM lobdemo
  8      WHERE key BETWEEN 2 and 6
  9      FOR UPDATE;
 10  BEGIN
 11    SELECT clob_col
 12      INTO v_Source
 13      FROM lobdemo
 14      WHERE key = 1;
 15
 16    OPEN c_Destinations;
 17    LOOP
 18      FETCH c_Destinations INTO v_Key, v_Destination;
 19      EXIT WHEN c_Destinations%NOTFOUND;
 20
 21      IF (v_Key = 2) THEN
 22        ReverseLOB(v_Source, v_Destination, 4);
 23      ELSIF (v_Key = 3) THEN
 24        ReverseLOB(v_Source, v_Destination, 2);
 25      ELSIF (v_Key = 4) THEN
 26        ReverseLOB(v_Source, v_Destination, 1);
 27      ELSIF (v_Key = 5) THEN
 28        ReverseLOB(v_Source, v_Destination, 10);
 29      ELSIF (v_Key = 6) THEN
 30        ReverseLOB(v_Source, v_Destination, 30);
 31      END IF;
 32    END LOOP;
 33    CLOSE c_Destinations;
 34    COMMIT;
 35  END;
 36  /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT key, clob_col
  2    FROM lobdemo
  3    WHERE key BETWEEN 1 AND 6
  4    ORDER BY key;
       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         1
abcdefghijklmnopqrstuvwxyz
         2
yzuvwxqrstmnopijklefghabcd
         3
yzwxuvstqropmnklijghefcdab

       KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
         4
zyxwvutsrqponmlkjihgfedcba
         5
uvwxyzklmnopqrstabcdefghij
         6
abcdefghijklmnopqrstuvwxyz

6 rows selected.
SQL>
SQL> DROP TABLE lobdemo;
Table dropped.
SQL>
SQL>

erase clob data

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clobData    CLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
  2    clobVariable CLOB;
  3    charVariable VARCHAR2(50);
  4    offsetPos INTEGER := 1;
  5    amount_var INTEGER := 50;
  6  BEGIN
  7    initClob(clobVariable, id_par);
  8    DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable);
  9    DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable);
 10    DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
 11  END readClob;
 12  /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE erase_example IS
  2    clobVariable CLOB;
  3    offsetPos INTEGER := 2;
  4    amount_var INTEGER := 5;
  5  BEGIN
  6    SELECT clobData INTO clobVariable FROM myTable WHERE id = 1 FOR UPDATE;
  7
  8    readClob(1);
  9    DBMS_LOB.ERASE(clobVariable, amount_var, offsetPos);
 10    readClob(1);
 11
 12  END erase_example;
 13  /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.

In() function with clob data

 
SQL> declare
  2      v1 clob := "abc";
  3  begin
  4
  5      If "abc" IN (v1) then null; end if;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>

Initialize clob

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );
Table created.
SQL>
SQL> -- create the PL/SQL procedures and methods
SQL> CREATE OR REPLACE PROCEDURE myProcedure(clob_par IN OUT CLOB,id_par IN INTEGER) IS
  2  BEGIN
  3    SELECT clob_column INTO clob_par FROM myTable WHERE id = id_par;
  4  END myProcedure;
  5  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Print out the length of the CLOBs previously INSERTed

  
SQL>
SQL> CREATE TABLE lobdemo (
  2    key NUMBER,
  3    clob_col CLOB,
  4    blob_col BLOB);
Table created.
SQL>
SQL>
SQL> DECLARE
  2    v_CLOB CLOB;
  3    v_Key lobdemo.key%TYPE;
  4    CURSOR c_SomeLOBs IS
  5      SELECT key, clob_col
  6      FROM lobdemo
  7      WHERE key BETWEEN 50 and 61;
  8  BEGIN
  9    OPEN c_SomeLOBs;
 10    LOOP
 11      FETCH c_SomeLOBs INTO v_Key, v_CLOB;
 12      EXIT WHEN c_SomeLOBs%NOTFOUND;
 13
 14      DBMS_OUTPUT.PUT_LINE(
 15        "Length of CLOB at key " || v_Key || " is: " ||
 16        DBMS_LOB.GETLENGTH(v_CLOB));
 17    END LOOP;
 18    CLOSE c_SomeLOBs;
 19  END;
 20  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table lobdemo;
Table dropped.
SQL>

Read clob type data, DBMS_LOB.READ

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
  2  BEGIN
  3    SELECT clob_column INTO clob_par FROM myTable WHERE id = id_par;
  4  END initClob;
  5  /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
  2    clob_var CLOB;
  3    char_buffer_var VARCHAR2(50);
  4    offset INTEGER := 1;
  5    amount_var INTEGER := 50;
  6  BEGIN
  7    initClob(clob_var, id_par);
  8    DBMS_LOB.READ(clob_var, amount_var, offset, char_buffer_var);
  9    DBMS_OUTPUT.PUT_LINE("char_buffer_var = " || char_buffer_var);
 10    DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
 11  END readClob;
 12  /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.

Use between ... and with clob data

 
SQL> declare
  2      v1 clob := "abc";
  3  begin
  4
  5      If v1 between "a" and "z" then null; end if;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>

Use clob to store xml data

 
SQL> CREATE TABLE myTable
  2    (myID            NUMBER PRIMARY KEY,
  3     myValue     XMLTYPE )
  4     XMLTYPE myValue STORE AS CLOB
  5  /
Table created.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Use clob type in PL/SQL

  
SQL>
SQL>      declare
  2        l_clob clob := "12345";
  3      begin
  4        dbms_lob.write( l_clob, 2, 3, "AB" );
  5        dbms_output.put_line( l_clob );
  6        dbms_lob.write( l_clob, 2, 9, "CD" );
  7        dbms_output.put_line( l_clob );
  8      end;
  9      /
12AB5
12AB5   CD
PL/SQL procedure successfully completed.
SQL>
SQL> --

Use dbms_lob.erase to remove value from clob type value

  
SQL>
SQL>
SQL>      declare
  2        l_c clob := "Hello World!";
  3        l_a number := 9;
  4      begin
  5        dbms_lob.erase( l_c, l_a, 6 );
  6        dbms_output.put_line( "The clob now = *" || l_c || "*" );
  7        dbms_output.put_line( "The amount that was erased was: " || l_a );
  8      end;
  9      /
The clob now = *Hello       *
The amount that was erased was: 7
PL/SQL procedure successfully completed.
SQL> --

Use dbms_lob.fileopen to open bfile

  
SQL>
SQL> create table bfile_table(
  2        name varchar2(255),
  3        the_file bfile );
Table created.
SQL> insert into bfile_table values ( "doc 1", bfilename( "WROX_DIR", "my_doc.pdf" ) );
1 row created.
SQL>
SQL>      declare
  2        l_bfile bfile;
  3        l_dir_alias varchar2(2000);
  4        l_filename varchar2(2000);
  5      begin
  6        select the_file
  7          into l_bfile
  8          from bfile_table
  9         where name = "doc 1";
 10       dbms_lob.fileopen( l_bfile, dbms_lob.file_readonly );
 11       if dbms_lob.fileexists( l_bfile ) = 1 then
 12         dbms_lob.filegetname( l_bfile, l_dir_alias, l_filename );
 13         dbms_output.put_line( "Directory alias: " || l_dir_alias );
 14         dbms_output.put_line( "Filename: " || l_filename );
 15       end if;
 16       if dbms_lob.fileisopen( l_bfile ) = 1 then
 17         dbms_lob.fileclose( l_bfile );
 18       end if;
 19    end;
 20   /
     declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 10

SQL>
SQL> drop table bfile_table;
Table dropped.
SQL>
SQL>
SQL> --

use like operator with clob type data

 
SQL> declare
  2      v1 clob := "abc";
  3  begin
  4
  5      If v1 like "a%" then null; end if;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>