Oracle PL/SQL/Data Type/Clob

Append data to clob

   <source lang="sql">

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.

 </source>
   
  

Appending and Writing to LOBs

   <source lang="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,"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> --

 </source>
   
  

Assign string value to clob type variable

   <source lang="sql">

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>

 </source>
   
  

clob data type

   <source lang="sql">

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.

 </source>
   
  

Clob type column

   <source lang="sql">

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>

 </source>
   
  

clob type data as string

   <source lang="sql">

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.

 </source>
   
  

Compare clob data

   <source lang="sql">

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>

 </source>
   
  

Compare two clobs

   <source lang="sql">

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.

 </source>
   
  

Copy clob data

   <source lang="sql">

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.

 </source>
   
  

Copying Internal LOBs

   <source lang="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      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> --

 </source>
   
  

Creating an Internal LOB Table

   <source lang="sql">
 

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

 </source>
   
  

DBMS_LOB package.

   <source lang="sql">
 

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>

 </source>
   
  

erase clob data

   <source lang="sql">

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.

 </source>
   
  

In() function with clob data

   <source lang="sql">

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>

 </source>
   
  

Initialize clob

   <source lang="sql">

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>

 </source>
   
  

Print out the length of the CLOBs previously INSERTed

   <source lang="sql">
 

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>

 </source>
   
  

Read clob type data, DBMS_LOB.READ

   <source lang="sql">

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.

 </source>
   
  

Use between ... and with clob data

   <source lang="sql">

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>

 </source>
   
  

Use clob to store xml data

   <source lang="sql">

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>

 </source>
   
  

Use clob type in PL/SQL

   <source lang="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> --

 </source>
   
  

Use dbms_lob.erase to remove value from clob type value

   <source lang="sql">
 

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

 </source>
   
  

Use dbms_lob.fileopen to open bfile

   <source lang="sql">
 

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

 </source>
   
  

use like operator with clob type data

   <source lang="sql">

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>

 </source>