Oracle PL/SQL Tutorial/Large Objects/bfilename

bfilename function

   <source lang="sql">

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></source>

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

   <source lang="sql">

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></source>

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

   <source lang="sql">

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></source>