create or replace type typ_traceline as table of varchar2(4000);
/

create or replace directory tracefiles as 'E:\ora11g\base\diag\rdbms\is11g01\is11g01\trace';
/

create or replace function trace_file_contents(pi_trace_file in varchar2)
  return typ_traceline
  pipelined as
  l_trace_files_dir varchar2(100) := 'TRACEFILES';
  l_bfile           bfile := bfilename(l_trace_files_dir, pi_trace_file);
  l_last            number := 1;
  l_current         number;
begin
  dbms_lob.fileopen(l_bfile);
  loop
    l_current := dbms_lob.instr(l_bfile, '0A', l_last, 1);
    exit when(nvl(l_current, 0) = 0);
    pipe row(utl_raw.cast_to_varchar2(dbms_lob.substr(l_bfile, l_current - l_last + 1, l_last)));
    l_last := l_current + 1;
  end loop;
  dbms_lob.fileclose(l_bfile);
  return;
end;
/

create or replace procedure start_tracing(pi_schema_name  in varchar2,
                                          pi_program      in integer,
                                          po_trace_cursor out sys_refcursor)
  authid current_user is
  l_program    varchar2(1000);
  l_sid        integer;
  l_serial     integer;
  l_trace_file varchar2(1000);
begin
  if pi_program = 1 then
    l_program := 'java.exe';
  elsif pi_program = 2 then
    l_program := 'JDBC Thin Client';
  elsif pi_program = 3 then
    l_program := 'SQL Developer'; -- 2.0.0 +
  elsif pi_program = 4 then
    l_program := 'sqldeveloperW.exe'; -- 1.5.5
  elsif pi_program = 5 then
    l_program := 'sqldeveloper.exe'; -- 1.5.0
  elsif pi_program = 7 then
    l_program := 'toad.exe';
  elsif pi_program = 8 then
    l_program := 'plsqldev.exe';
  elsif pi_program = 9 then
    l_program := 'sqlplus.exe';
  else
    open po_trace_cursor for
      select 'Unknown program!' as warning from dual;
    return;
  end if;
  begin
    select sid, serial#
      into l_sid, l_serial
      from v$session
     where username = upper(pi_schema_name)
       and program = l_program;
  exception
    when no_data_found then
      open po_trace_cursor for
        select 'There are no sessions with these parameters!' as warning from dual;
      return;
    when others then
      open po_trace_cursor for
        select 'There is more than one session with these parameters!' as warning from dual;
      return;
  end;
  dbms_monitor.session_trace_enable(l_sid, l_serial, true, false);
  open po_trace_cursor for
    select 'Tracing started' as warning from dual;
end start_tracing;
/

create or replace procedure show_trace_file(pi_schema_name  in varchar2,
                                            pi_program      in integer,
                                            po_trace_cursor out sys_refcursor)
  authid current_user is
  l_program    varchar2(1000);
  l_sid        integer;
  l_serial     integer;
  l_trace_file varchar2(1000);
begin
  if pi_program = 1 then
    l_program := 'java.exe';
  elsif pi_program = 2 then
    l_program := 'JDBC Thin Client';
  elsif pi_program = 3 then
    l_program := 'SQL Developer'; -- 2.0.0 +
  elsif pi_program = 4 then
    l_program := 'sqldeveloperW.exe'; -- 1.5.5
  elsif pi_program = 5 then
    l_program := 'sqldeveloper.exe'; -- 1.5.0
  elsif pi_program = 7 then
    l_program := 'toad.exe';
  elsif pi_program = 8 then
    l_program := 'plsqldev.exe';
  elsif pi_program = 9 then
    l_program := 'sqlplus.exe';
  else
    open po_trace_cursor for
      select 'Unknown program!' as warning from dual;
    return;
  end if;
  begin
    select sid, serial#
      into l_sid, l_serial
      from v$session
     where username = upper(pi_schema_name)
       and program = l_program;
  exception
    when no_data_found then
      open po_trace_cursor for
        select 'There are no sessions with these parameters!' as warning from dual;
      return;
    when others then
      open po_trace_cursor for
        select 'There is more than one session with these parameters!' as warning from dual;
      return;
  end;
  select regexp_substr(tracefile, '[^\]+', 1, 9)
    into l_trace_file
    from v$process
   where addr in (select paddr
                    from v$session
                   where sid = l_sid
                     and serial# = l_serial);
  open po_trace_cursor for
    select * from table(trace_file_contents(l_trace_file));
end show_trace_file;
/

-- call block 

declare
  pi_schema_name  varchar2(200);
  pi_program      number;
  po_trace_cursor sys_refcursor;
  v_line          varchar2(4000);
begin
  pi_schema_name := 'LUCIAN_SPM'; 
  pi_program     := 9; 
  -- Values accepted for pi_program:
  -- 1 = java.exe
  -- 2 = JDBC Thin Client
  -- 3 = SQL Developer version 2.0 or above
  -- 4 = SQL Developer version 1.5.5
  -- 5 = SQL Developer version 1.5
  -- 7 = Toad
  -- 8 = PL/SQL Developer
  -- 9 = sqlplus
  system.start_tracing(pi_schema_name  => pi_schema_name,
                       pi_program      => pi_program,
                       po_trace_cursor => po_trace_cursor);
  loop
    fetch po_trace_cursor
      into v_line;
    exit when po_trace_cursor%notfound;
    dbms_output.put_line(v_line);
  end loop;
end;
/

declare
  pi_schema_name  varchar2(200);
  pi_program      number;
  po_trace_cursor sys_refcursor;
  v_line          varchar2(4000);
begin
  pi_schema_name := 'LUCIAN_SPM'; 
  pi_program     := 9; 
  system.show_trace_file(pi_schema_name  => pi_schema_name,
                         pi_program      => pi_program,
                         po_trace_cursor => po_trace_cursor);
  loop
    fetch po_trace_cursor
      into v_line;
    exit when po_trace_cursor%notfound;
    dbms_output.put_line(v_line);
  end loop;
end;
/