Oracle PL/SQL Tutorial/System Packages/DBMS LOB

DBMS_LOB Package

SQL>
SQL>
SQL> CREATE TABLE myBlob
  2  (id NUMBER PRIMARY KEY,
  3   blob_data BLOB);
Table created.
SQL>
SQL>
SQL> DECLARE
  2    bfile_pointer BFILE;
  3    blob_pointer BLOB;
  4    bfile_offset NUMBER :=1;
  5    blob_offset NUMBER :=1;
  6    tot_len INTEGER;
  7  BEGIN
  8
  9    INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
 10
 11    SELECT blob_data INTO blob_pointer FROM myBlob WHERE id = 1 FOR UPDATE;
 12
 13    bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
 14
 15    dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
 16
 17    dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
 18
 19    dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
 20
 21    tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
 22
 23    dbms_lob.close(blob_pointer);
 24
 25    dbms_lob.fileclose(bfile_pointer);
 26
 27    DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
 28  END;
 29  /
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 15

SQL>
SQL> drop table myBlob;
Table dropped.
SQL>

DBMS_LOB.READ

SQL>
SQL> create table myTable
  2  (key NUMBER PRIMARY KEY
  3  ,col_blob BLOB
  4  ,col_clob CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(1, HEXTORAW("101F"), "ZYXW");
1 row created.
SQL>
SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(2, HEXTORAW("111101F"), "ABCD");
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_inmyTable2
  2  IS
  3
  4      v_key1      myTable.key%TYPE;
  5      blobValue1      myTable.col_blob%TYPE;
  6      clobValue1      myTable.col_clob%TYPE;
  7      v_key2      myTable.key%TYPE;
  8      blobValue2      myTable.col_blob%TYPE;
  9      clobValue2      myTable.col_clob%TYPE;
 10      v_buffer    VARCHAR2(1000);
 11      v_offset    NUMBER;
 12      v_amount    NUMBER;
 13      v_dest_offset   NUMBER := 1;
 14      v_src_offset    NUMBER := 1;
 15
 16
 17  BEGIN
 18
 19      SELECT key, col_blob, col_clob
 20      INTO v_key1, blobValue1, clobValue1
 21      FROM myTable
 22      WHERE key = 1
 23      FOR UPDATE;
 24
 25      SELECT key, col_blob, col_clob
 26      INTO v_key2, blobValue2, clobValue2
 27      FROM myTable
 28      WHERE key = 2
 29      FOR UPDATE;
 30
 31      v_amount := 80;
 32      v_offset := 1;
 33      DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer);
 34      DBMS_OUTPUT.PUT_LINE ("Clob1 Contents => " ||  v_buffer);
 35
 36      v_amount := 80;
 37      v_offset := 1;
 38      DBMS_LOB.READ (clobValue2, v_amount, v_offset, v_buffer);
 39      DBMS_OUTPUT.PUT_LINE ("Clob2 Contents => " ||  v_buffer);
 40
 41      v_amount := 80;
 42      v_offset := 1;
 43      DBMS_LOB.COPY (clobValue1, clobValue2, v_amount, v_dest_offset, v_src_offset);
 44
 45      v_amount := 80;
 46      v_offset := 1;
 47      DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer);
 48      DBMS_OUTPUT.PUT_LINE ("Clob1 Contents => " ||  v_buffer);
 49
 50      v_amount := 80;
 51      v_offset := 1;
 52      DBMS_LOB.READ (clobValue2, v_amount, v_offset, v_buffer);
 53      DBMS_OUTPUT.PUT_LINE ("Clob2 Contents => " ||  v_buffer);
 54
 55  END;
 56  /
Procedure created.
SQL> exec sp_inmyTable2
Clob1 Contents => ZYXW
Clob2 Contents => ABCD
Clob1 Contents => ABCD
Clob2 Contents => ABCD
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>

Load a file

SQL>
SQL> set echo on
SQL>
SQL> create table demo
  2  ( id           int primary key,
  3    theBlob      blob
  4  )
  5  /
Table created.
SQL>
SQL> create or replace directory my_files as "c:\temp\";
Directory created.
SQL>
SQL> create sequence blob_seq;
Sequence created.
SQL>
SQL>
SQL> create or replace procedure load_a_file( p_dir_name in varchar2,p_file_name in varchar2) as
  2      l_blob    blob;
  3      l_bfile   bfile;
  4  begin
  5      insert into demo values ( blob_seq.nextval, empty_blob() )
  6      returning theBlob into l_Blob;
  7
  8      l_bfile := bfilename( p_dir_name, p_file_name );
  9      dbms_lob.fileopen( l_bfile );
 10
 11      dbms_lob.loadfromfile( l_blob, l_bfile,dbms_lob.getlength( l_bfile ) );
 12      dbms_lob.fileclose( l_bfile );
 13  end;
 14  /
Procedure created.
SQL>
SQL> exec load_a_file( "MY_FILES", "clean.sql" );
BEGIN load_a_file( "MY_FILES", "clean.sql" ); END;
*
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 "sqle.LOAD_A_FILE", line 9
ORA-06512: at line 1

SQL>
SQL> drop table demo;
Table dropped.
SQL> drop sequence blob_seq;
Sequence dropped.
SQL>

Print clob data out

SQL>
SQL> create or replace procedure print_clob( p_clob in clob )
  2  as
  3       l_offset number default 1;
  4  begin
  5     loop
  6       exit when l_offset > dbms_lob.getlength(p_clob);
  7       dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
  8       l_offset := l_offset + 255;
  9     end loop;
 10  end;
 11  /
Procedure created.
SQL>
SQL>
SQL>

Show Java source file

SQL>
SQL> CREATE OR REPLACE PROCEDURE show_java_source (
  2     name IN VARCHAR2,
  3     schema IN VARCHAR2 := NULL
  4  )
  5  IS
  6     b CLOB;
  7     v VARCHAR2 (2000);
  8     i INTEGER;
  9  BEGIN
 10     DBMS_LOB.createtemporary (b, FALSE);
 11     dbms_java.export_source (name, NVL (schema, USER), b);
 12     i := 1000;
 13     DBMS_LOB.read (b, i, 1, v);
 14     DBMS_OUTPUT.put_line (v);
 15  END;
 16  /
Warning: Procedure created with compilation errors.
SQL>
SQL>
SQL>

Use dbms_lob.getlength and dbms_lob.substr with blob type column

SQL>
SQL> create table demo
  2  ( id           int primary key,
  3    theBlob      blob
  4  )
  5  /
Table created.
SQL>
SQL> select id, dbms_lob.getlength(theBlob) len, clean(theBlob) piece, dbms_lob.substr(theBlob,40,1) raw_data
  2  from demo
  3  where id =1
  4  /
no rows selected
SQL> drop table demo;
Table dropped.

Use dbms_lob.getlength to get the length of a clob data

SQL>
SQL> create or replace procedure print_clob( p_clob in clob )
  2  as
  3       l_offset number default 1;
  4  begin
  5     loop
  6       exit when l_offset > dbms_lob.getlength(p_clob);
  7       dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
  8       l_offset := l_offset + 255;
  9     end loop;
 10  end;
 11  /
Procedure created.
SQL>
SQL>

Use dbms_lob package to deal with clob data

SQL>
SQL> create global temporary table myTable
  2  ( id    int primary key,
  3    c_lob clob,
  4    b_lob blob
  5  )
  6  /
Table created.
SQL>
SQL> create sequence myTable_seq;
Sequence created.
SQL>
SQL> create or replace function to_clob( p_cname in varchar2,p_tname in varchar2,p_rowid in rowid ) return clob
  2  as
  3      l_clob clob;
  4      l_id   int;
  5  begin
  6      select myTable_seq.nextval into l_id from dual;
  7      execute immediate "insert into myTable (id,c_lob) select :id, to_lob( " || p_cname || " )
  8            from " || p_tname || " where rowid = :rid " using IN l_id, IN p_rowid;
  9
 10      select c_lob into l_clob from myTable where id = l_id ;
 11
 12      return l_clob;
 13  end;
 14  /
Function created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop sequence myTable_seq;
Sequence dropped.

Use dbms_lob.substr to get part of clob data

SQL>
SQL>
SQL> set echo on
SQL>
SQL>
SQL> create table t ( str varchar2(10), lob clob );
Table created.
SQL>
SQL> insert into t values ( "hello", "hello" );
1 row created.
SQL>
SQL> select substr( str, 3, 2 ), dbms_lob.substr( lob, 3, 2) lob
  2  from t
  3  /
SU
--
LOB
--------------------------------------------------------------------------------
ll
ell

SQL>
SQL> drop table t;
Table dropped.