Oracle PL/SQL Tutorial/PL SQL Data Types/BFILE

BFILE type variable

   <source lang="sql">

SQL> CREATE TABLE myBFile

 2  (id NUMBER PRIMARY KEY,
 3   bfile_data BFILE);

Table created. SQL> SQL> DECLARE

 2    bfileValue BFILE;
 3  BEGIN
 4    INSERT INTO myBFile VALUES (1,BFILENAME("BFILE_DIR","test.bmp"))
 5    RETURNING bfile_data INTO bfileValue;
 6
 7  END;
 8  /

PL/SQL procedure successfully completed. SQL> SQL> drop table myBFile; Table dropped. SQL></source>

Close the BFILE

   <source lang="sql">

SQL> SQL> CREATE TABLE myBlob

 2  (id NUMBER PRIMARY KEY,
 3   blob_data BLOB);

Table created. 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 (5,EMPTY_BLOB());
10
11    SELECT blob_data INTO blob_pointer FROM myBlob WHERE id =5 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.</source>

Open the target CLOB and the source BFILE

   <source lang="sql">

SQL> SQL> CREATE TABLE facebook (

 2     name VARCHAR2(80),
 3     photo BLOB,
 4     directions CLOB,
 5     description NCLOB,
 6     web_page BFILE);

Table created. SQL> SQL> CREATE DIRECTORY bfile_data AS "c:\xxx"; Directory created. SQL> SQL> DECLARE

 2     myBFile BFILE := BFILENAME("BFILE_DATA","TanneryFalls.directions");
 3     directions CLOB;
 4     destination_offset INTEGER := 1;
 5     source_offset INTEGER := 1;
 6     language_context INTEGER := DBMS_LOB.default_lang_ctx;
 7     warning_message INTEGER;
 8  BEGIN
 9     DELETE FROM facebook WHERE name="Falls";
10
11     INSERT INTO facebook (name,directions) VALUES ("Falls",EMPTY_CLOB(  ));
12
13     SELECT directions INTO directions FROM facebook WHERE name="Falls";
14
15
16     DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
17     DBMS_LOB.OPEN(myBFile);
18
19     DBMS_LOB.LOADCLOBFROMFILE(directions, myBFile,
20                               DBMS_LOB.LOBMAXSIZE,
21                               destination_offset, source_offset,
22                               NLS_CHARSET_ID("US7ASCII"),
23                               language_context, warning_message);
24
25     IF warning_message = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
26          dbms_output.put_line(
27             "Warning! Some characters couldn""t be converted.");
28     END IF;
29
30     DBMS_LOB.CLOSE(directions);
31     DBMS_LOB.CLOSE(myBFile);
32  END;
33  /

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 716 ORA-06512: at line 17

SQL> SQL> SQL> drop table facebook; Table dropped. SQL> drop directory bfile_data; Directory dropped.</source>