Oracle PL/SQL Tutorial/Large Objects/bfilename

bfilename function

SQL>
SQL> create or replace type vcArray as table of varchar2(4000)
  2  /
Type created.
SQL>
SQL> create or replace
  2  function trace_file_contents( p_filename in varchar2 )
  3  return vcArray
  4  pipelined
  5  as
  6      l_bfile       bfile := bfilename("UDUMP_DIR",p_filename);
  7      l_last        number := 1;
  8      l_current     number;
  9  begin
 10      select rownum into l_current
 11        from user_avail_trace_files
 12       where filename = p_filename;
 13
 14      dbms_lob.fileopen( l_bfile );
 15      loop
 16          l_current := dbms_lob.instr( l_bfile, "0A", l_last, 1 );
 17          exit when (nvl(l_current,0) = 0);
 18          pipe row(utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_current-l_last+1,l_last ) ));
 19          l_last := l_current+1;
 20      end loop;
 21      dbms_lob.fileclose(l_bfile);
 22      return;
 23  end;
 24  /
Function created.
SQL>
SQL>
SQL>

Stored procedure to read the data from the BFILE into the clob clob and display the new clob contents.

SQL>
SQL> create table myTable
  2  (key NUMBER PRIMARY KEY
  3  ,col_blob BLOB
  4  ,col_clob CLOB);
Table created.
SQL>
SQL> INSERT INTO myTable (key, col_blob, col_clob)
  2  VALUES (1, HEXTORAW("101F"), "ZYXW");
1 row created.
SQL>
SQL>
SQL> ALTER TABLE myTable add (col_bfile BFILE);
Table altered.
SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_bfile_read
  2  IS
  3      v_fileloc   BFILE;
  4      v_col_clob  CLOB;
  5      v_filename  VARCHAR2(2000);
  6      v_amount    NUMBER := 80;
  7      v_offset    NUMBER := 1;
  8      v_buffer    VARCHAR2(80);
  9
 10      v_dir   VARCHAR2(80) := "INFILE";
 11
 12  BEGIN
 13      v_fileloc := BFILENAME (v_dir, "b_file.txt");
 14
 15      DBMS_LOB.FILEOPEN (v_fileloc, DBMS_LOB.FILE_READONLY);
 16
 17      SELECT col_clob
 18      INTO v_col_clob
 19      FROM myTable
 20      WHERE key = 2
 21      FOR UPDATE;
 22
 23      DBMS_LOB.LOADFROMFILE (v_col_clob, v_fileloc, DBMS_LOB.GETLENGTH (v_fileloc) );
 24      DBMS_LOB.READ (v_col_clob, v_amount, v_offset, v_buffer);
 25
 26      DBMS_OUTPUT.PUT_LINE (v_buffer);
 27
 28      DBMS_LOB.FILECLOSE (v_fileloc);
 29   EXCEPTION
 30      WHEN NO_DATA_FOUND THEN
 31      DBMS_LOB.FILECLOSEALL;
 32      RAISE_APPLICATION_ERROR(-20001, "No data found!");
 33
 34      WHEN VALUE_ERROR THEN
 35      DBMS_LOB.FILECLOSEALL;
 36      RAISE_APPLICATION_ERROR(-20001, "Value Error!");
 37
 38      WHEN DBMS_LOB.INVALID_ARGVAL THEN
 39      DBMS_LOB.FILECLOSEALL;
 40      RAISE_APPLICATION_ERROR(-20001, "Read only mode not specified");
 41
 42      WHEN OTHERS THEN
 43      DBMS_LOB.FILECLOSEALL;
 44      RAISE_APPLICATION_ERROR(-20009, "Other exception raised: " || SQLCODE);
 45  END;
 46  /
Procedure created.
SQL>
SQL> exec sp_bfile_read

SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Use the file named b_file.txt and attach it to the myTable table in the col_bfile column.

SQL>
SQL> create table myTable
  2  (key NUMBER PRIMARY KEY
  3  ,col_blob BLOB
  4  ,col_clob CLOB);
Table created.
SQL>
SQL> INSERT INTO myTable (key, col_blob, col_clob)
  2  VALUES (1, HEXTORAW("101F"), "ZYXW");
1 row created.
SQL>
SQL>
SQL> ALTER TABLE myTable add (col_bfile BFILE);
Table altered.
SQL>
SQL>
SQL>
SQL>
SQL> UPDATE myTable
  2  SET col_bfile = BFILENAME ("INFILE", "b_file.txt")
  3  WHERE key = 1;
1 row updated.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>