bfilename function : bfilename « Large Objects « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Large Objects
3. bfilename

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>