Oracle PL/SQL Tutorial/Large Objects/CLOB

Adding Content to a CLOB

SQL>
SQL> CREATE TABLE MyTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> INSERT INTO myTable(id, clob_column) VALUES (1, EMPTY_CLOB());
1 row created.
SQL>
SQL>
SQL> UPDATE myTable
  2  SET clob_column = "AAAAA"
  3  WHERE id = 1;
1 row updated.
SQL>
SQL> select * from myTable;
        ID CLOB_COLUMN
---------- --------------------------------------------------------------------------------
         1 AAAAA
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

close Clob data pointer

SQL>
SQL>
SQL> CREATE TABLE myClob
  2  (id NUMBER PRIMARY KEY,
  3   clob_data CLOB);
Table created.
SQL>
SQL> INSERT INTO myClob VALUES (101,null);
1 row created.
SQL>
SQL> declare
  2
  3    clob_pointer CLOB;
  4
  5    v_buf VARCHAR2(1000);
  6
  7    Amount BINARY_INTEGER :=1000;
  8
  9    Position INTEGER :=1;
 10  BEGIN
 11    v_buf :=rpad("A",1000,"A");
 12
 13    insert into myClob values (1,EMPTY_CLOB());
 14
 15    SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1 FOR UPDATE;
 16
 17    DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE);
 18
 19    FOR i IN 1..500 LOOP
 20
 21      DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf);
 22
 23      Position :=Position +Amount;
 24
 25    END LOOP;
 26
 27    DBMS_LOB.CLOSE (clob_pointer);
 28  END;
 29  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myClob;
Table dropped.

Compare date value after to_char() and trim()

SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL>
SQL> select gift_id, register_date
  2    from gift
  3   where trim(to_char(register_date, "Month")) = "February";
   GIFT_ID REGISTER_
---------- ---------
         1 14-FEB-99
         2 14-FEB-99
         3 14-FEB-99
         4 14-FEB-99
4 rows selected.
SQL>
SQL>
SQL> drop table gift;
Table dropped.

Convert string to clob

SQL>
SQL> declare
  2
  3    c1 clob := to_clob("abc");
  4    c2 clob;
  5
  6  begin
  7      case c1
  8           when to_clob("abc") then dbms_output.put_line("abc");
  9           when to_clob("def") then dbms_output.put_line("def");
 10      end case;
 11
 12      c2 := case c1
 13         when to_clob("abc") then "abc"
 14         when to_clob("def") then "def"
 15      end;
 16
 17      dbms_output.put_line(c2);
 18
 19  end;
 20  /
abc
abc
PL/SQL procedure successfully completed.
SQL>

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.

Creating Tables Containing CLOB Objects

SQL>
SQL> CREATE TABLE MyTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );
Table created.
SQL>
SQL> desc myTable;
 Name           Null?    Type
 ---------------
 ID             NOT NULL NUMBER(38)
 CLOB_COLUMN    NOT NULL CLOB
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Initialize CLOB column

SQL> CREATE TABLE MyTable (
  2    id          INTEGER PRIMARY KEY,
  3    clob_column CLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> INSERT INTO myTable(id, clob_column) VALUES (1, EMPTY_CLOB());
1 row created.
SQL>
SQL> select * from mytable;
        ID CLOB_COLUMN
---------- -----------
         1
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Insert into clob column

SQL>
SQL> CREATE TABLE myClob
  2  (id NUMBER PRIMARY KEY,
  3   clob_data CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO myClob VALUES (102,RPAD("AAAAAAAAAAAAAAAAAA",40000,"A"));
1 row created.
SQL>
SQL>
SQL>
SQL> drop table myClob;
Table dropped.
SQL>

Loading data to the CLOB by using BFILE

CLOBs are very useful structures. You can store lots of text information in a CLOB.

SQL>
SQL> create table catalog
  2  (id number,
  3   name VARCHAR2(2000),
  4   manual_cl CLOB,
  5   firstpage_bl BLOB,
  6   mastertxt_bf BFILE
  7   );
Table created.
SQL>
SQL>
SQL> create directory IO as "C:\IO";

SQL> --grant read, write on directory IO to public;
SQL>
SQL> insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", BFILENAME ("IO", "text.htm"));
1 row created.
SQL>
SQL>
SQL> declare
  2      v_file_bf     BFILE;
  3      v_manual_cl   CLOB;
  4      lang_ctx      NUMBER := DBMS_LOB.default_lang_ctx;
  5      charset_id    NUMBER := 0;
  6      src_offset    NUMBER := 1;
  7      dst_offset    NUMBER := 1;
  8      warning       NUMBER;
  9  begin
 10      update catalog set manual_cl = EMPTY_CLOB() where id = 1;
 11
 12      select mastertxt_bf, manual_cl into v_file_bf, v_manual_cl from catalog where id = 1;
 13
 14      DBMS_LOB.fileopen(v_file_bf, DBMS_LOB.file_readonly);
 15      DBMS_LOB.loadclobfromfile (v_manual_cl, v_file_bf,DBMS_LOB.getlength (v_file_bf),
 16                              src_offset, dst_offset,charset_id, lang_ctx,warning);
 17      DBMS_LOB.fileclose (v_file_bf);
 18  end;
 19  /
declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 14

SQL>
SQL> drop directory IO;
Directory dropped.
SQL>
SQL> drop table catalog;
Table dropped.

Obtain Clob data pointer

SQL>
SQL> CREATE TABLE myClob
  2  (id NUMBER PRIMARY KEY,
  3   clob_data CLOB);
Table created.
SQL>
SQL> INSERT INTO myClob VALUES (101,null);
1 row created.
SQL>
SQL> declare
  2    clob_pointer CLOB;
  3    v_buf VARCHAR2(1000);
  4    Amount BINARY_INTEGER :=1000;
  5    Position INTEGER :=1;
  6  BEGIN
  7    v_buf :=rpad("A",1000,"A");
  8
  9    insert into myClob values (5,EMPTY_CLOB());
 10
 11    SELECT clob_data INTO clob_pointer FROM myClob WHERE id =5 FOR UPDATE;
 12
 13    DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE);
 14
 15    FOR i IN 1..500 LOOP
 16
 17      DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf);
 18
 19      Position :=Position +Amount;
 20
 21    END LOOP;
 22
 23    DBMS_LOB.CLOSE (clob_pointer);
 24  END;
 25  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myClob;
Table dropped.

Open the CLOB

SQL>
SQL> CREATE TABLE myClob
  2  (id NUMBER PRIMARY KEY,
  3   clob_data CLOB);
Table created.
SQL>
SQL> DECLARE
  2    clob_pointer CLOB;
  3    v_Buf VARCHAR2(1000);
  4    Amount BINARY_INTEGER :=1000;
  5    Position INTEGER :=1;
  6    fp UTL_FILE.FILE_TYPE;
  7  BEGIN
  8
  9    insert into myClob values (1,EMPTY_CLOB());
 10
 11    SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1;
 12
 13    if (DBMS_LOB.ISOPEN(clob_pointer)!=1) then
 14
 15      DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READONLY);
 16
 17    end if;
 18
 19    fp :=UTL_FILE.FOPEN("BFILE_DIR","output.dat","w");
 20
 21    LOOP
 22      BEGIN
 23
 24        DBMS_LOB.READ (clob_pointer, Amount, Position, v_Buf);
 25
 26        UTL_FILE.PUT_LINE(fp, v_Buf, TRUE);
 27
 28        Position :=Position + Amount;
 29      EXCEPTION
 30        WHEN NO_DATA_FOUND THEN
 31          EXIT;
 32      END;
 33    END LOOP;
 34
 35    UTL_FILE.FCLOSE(fp);
 36
 37    DBMS_LOB.CLOSE (clob_pointer);
 38  END;
 39  /

SQL>
SQL> drop table myClob;
Table dropped.

Performing basic string operations on CLOBs

SQL>
SQL> create table catalog
  2  (
  3   id number,
  4   name VARCHAR2(2000),
  5   manual_cl CLOB,
  6   firstpage_bl BLOB,
  7   mastertxt_bf BFILE
  8   );
Table created.
SQL>
SQL>
SQL> create directory IO as "C:\IO";
Directory created.
SQL> --grant read, write on directory IO to public;
SQL>
SQL> insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", BFILENAME ("IO", "text.htm"));
1 row created.
SQL>
SQL>
SQL> declare
  2      v_manual_cl   CLOB;
  3      v_nr          NUMBER;
  4      v_tx          VARCHAR2 (2000);
  5      v_add_tx      VARCHAR2 (2000):="Loaded: "||TO_CHAR(SYSDATE,"mm/dd/yyyy hh24:mi");
  6  begin
  7      select manual_cl into v_manual_cl from catalog where id = 1
  8      for update;
  9
 10      DBMS_LOB.writeappend (v_manual_cl,LENGTH (v_add_tx), v_add_tx);
 11
 12      v_nr := INSTR (v_manual_cl, "Loaded:", -1);
 13      v_tx := SUBSTR (v_manual_cl, v_nr);
 14      DBMS_OUTPUT.put_line (v_tx);
 15  end;
 16
 17  drop table catalog;
 18
 19  drop directory IO;
 20
 21  --Keep in mind that LOB pointers are transaction dependent. This means that if you have a COMMIT command in your code, the LOB pointer could become invalid (not pointing to anything) and you may not be able to perform some operations by using that locator.
 22

Read clob data to varchar2 type variable

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> CREATE OR REPLACE PROCEDURE write_example(id_par IN INTEGER) IS
  2    clobVariable CLOB;
  3    charVariable VARCHAR2(10) := "pretty";
  4    offsetPos INTEGER := 7;
  5    amount_var INTEGER := 6;
  6  BEGIN
  7    SELECT clobData INTO clobVariable FROM myTable WHERE id = id_par FOR UPDATE;
  8
  9    readClob(1);
 10    DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable);
 11    readClob(1);
 12
 13  END write_example;
 14  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

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.

Reading and Writing to a CLOB

SQL>
SQL> CREATE TABLE myClob
  2  (id NUMBER PRIMARY KEY,
  3   clob_data CLOB);
Table created.
SQL>
SQL> INSERT INTO myClob VALUES (101,null);
1 row created.
SQL>
SQL> declare
  2    clob_pointer CLOB;
  3    v_buf VARCHAR2(1000);
  4    Amount BINARY_INTEGER :=1000;
  5    Position INTEGER :=1;
  6  BEGIN
  7    v_buf :=rpad("A",1000,"A");
  8
  9    insert into myClob values (1 ,EMPTY_CLOB());
 10
 11    commit;
 12
 13    SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1 FOR UPDATE;
 14    DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE);
 15
 16    FOR i IN 1..500 LOOP
 17
 18      DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf);
 19
 20      Position :=Position +Amount;
 21
 22    END LOOP;
 23
 24    DBMS_LOB.CLOSE (clob_pointer);
 25
 26  END;
 27  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myClob;
Table dropped.
SQL>
SQL>

Update clob data

SQL>
SQL> CREATE TABLE myClobTable (id NUMBER,clob_data CLOB);
Table created.
SQL>
SQL> CREATE TABLE myLongTable (id NUMBER,long_data LONG);
Table created.
SQL>
SQL> INSERT INTO myLongTable VALUES (100,RPAD("A",200000,"A"));
1 row created.
SQL>
SQL>
SQL> update myClobTable set clob_data =(select to_lob(long_data)from
  2                  myLongTable where id =100)
  3
SQL> drop table myLongTable;
Table dropped.
SQL>
SQL>
SQL> drop table myClobTable;
Table dropped.