Oracle PL/SQL Tutorial/System Packages/DBMS LOB

DBMS_LOB Package

   <source lang="sql">

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

DBMS_LOB.READ

   <source lang="sql">

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

Load a file

   <source lang="sql">

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

Print clob data out

   <source lang="sql">

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

Show Java source file

   <source lang="sql">

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

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

   <source lang="sql">

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.</source>

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

   <source lang="sql">

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

Use dbms_lob package to deal with clob data

   <source lang="sql">

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.</source>

Use dbms_lob.substr to get part of clob data

   <source lang="sql">

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.</source>