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>