Open the BLOB : BLOB « Large Objects « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> CREATE TABLE myBlob
  2  (id NUMBER PRIMARY KEY,
  3   blob_data BLOB);

Table created.

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








34.3.BLOB
34.3.1.Creating Tables Containing BLOB Objects
34.3.2.Initialize BLOB column data
34.3.3.Adding Content to a BLOB
34.3.4.Use update statement to change the value in blob type column
34.3.5.UPDATE sets blob_column to a binary number:
34.3.6.Open the BLOB
34.3.7.Loading a page to a BLOB
34.3.8.Populating a BLOB column from an external BFILE
34.3.9.INSERT a row with an empty blob
34.3.10.Display the length of the BLOB
34.3.11.Obtain length of the populated BLOB
34.3.12.Close the BLOB