Oracle PL/SQL/Data Type/bfile

BFile column

  
SQL> CREATE TABLE myTable (
  2    id           INTEGER PRIMARY KEY,
  3    bfile_column BFILE NOT NULL
  4  );
Table created.
SQL>
SQL> drop table myTable;
Table dropped.

BFILENAME demo

    
SQL> CREATE TABLE book (
  2     id     NUMBER (10) PRIMARY KEY,
  3     isbn               CHAR(10 CHAR),
  4     description        CLOB,
  5     nls_description    NCLOB,
  6     misc               BLOB,
  7     chapter_title      VARCHAR2(30 CHAR),
  8     bfile_description  BFILE
  9  );
Table created.
SQL>
SQL>
SQL> INSERT INTO book (id,isbn,description,nls_description,misc,bfile_description)VALUES (1,"3", EMPTY_CLOB(),EMPTY_CLOB(),EMPTY_BLOB(),BFILENAME("book_LOC", "b.pdf"));
1 row created.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     v_dest_blob BLOB;
  3     v_dest_clob CLOB;
  4     v_source_locator1 BFILE := BFILENAME("book_LOC", "bfile_example.pdf");
  5     v_source_locator2 BFILE := BFILENAME("book_LOC", "bfile_example.txt");
  6
  7  BEGIN
  8
  9     UPDATE book SET description = EMPTY_CLOB(),misc = EMPTY_BLOB();
 10
 11     SELECT description, misc INTO v_dest_clob, v_dest_blob FROM book WHERE id = 1 FOR UPDATE;
 12      
 13     DBMS_LOB.LOADFROMFILE(v_dest_blob, v_source_locator1, DBMS_LOB.LOBMAXSIZE, 1, 1);
 14     DBMS_LOB.LOADFROMFILE(v_dest_clob, v_source_locator2, DBMS_LOB.LOBMAXSIZE, 1, 1);
 15
 16     DBMS_OUTPUT.PUT_LINE("Size of BLOB post-load: "||DBMS_LOB.GETLENGTH(v_dest_blob));
 17     DBMS_OUTPUT.PUT_LINE("Size of CLOB post-load: "||DBMS_LOB.GETLENGTH(v_dest_clob));
 18
 19     
 20     DBMS_LOB.CLOSE(v_source_locator1);
 21     DBMS_LOB.CLOSE(v_source_locator2);
 22     DBMS_LOB.CLOSE(v_dest_blob);
 23     DBMS_LOB.CLOSE(v_dest_clob);
 24
 25  EXCEPTION
 26     WHEN OTHERS
 27     THEN
 28        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 29
 30        DBMS_LOB.CLOSE(v_source_locator1);
 31        DBMS_LOB.CLOSE(v_source_locator2);
 32        DBMS_LOB.CLOSE(v_dest_blob);
 33        DBMS_LOB.CLOSE(v_dest_clob);
 34
 35  END;
 36  /
SQL> SET LONG 64000
SQL> SELECT description
  2  FROM book
  3  WHERE id = 1;
DESCRIPTION
--------------------------------------------------------------------------------

1 row selected.
SQL>
SQL>
SQL> drop table book;
Table dropped.