DBMS_LOB Package : DBMS_LOB « System Packages « Oracle PL/SQL Tutorial






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>








31.12.DBMS_LOB
31.12.1.DBMS_LOB Package
31.12.2.Print clob data out
31.12.3.Use dbms_lob.getlength to get the length of a clob data
31.12.4.Show Java source file
31.12.5.Use dbms_lob package to deal with clob data
31.12.6.Use dbms_lob.getlength and dbms_lob.substr with blob type column
31.12.7.Use dbms_lob.substr to get part of clob data
31.12.8.DBMS_LOB.READ
31.12.9.Load a file