/**********************************************************************
 * File:	trclvl12.sql
 * Type:	SQL*Plus script
 * Author:	Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:	14-Jun-95
 *
 * Description:
 *	DDL script to create the TRCLVL12 stored procedure, which uses
 *	the DBMS_SYSTEM.SET_EV procedure to enable the SQL Trace event
 *	(10046) to level 12 (i.e. display tracing information, bind
 *	variable values, and wait events).
 *
 * Modifications:
 *********************************************************************/
whenever oserror exit failure
whenever sqlerror exit failure

set echo on feedback on timing on

spool trclvl12

show user
show release

create or replace procedure trclvl12(in_sid in number,
					 in_serial_nbr in number)
is
	v_outstring	 varchar2(200);
begin
	--
	select	substr(m.value || '/ora_' || p.spid || '.trc', 1, 200)
	into	v_outstring
	from	v_$session	s,
		v_$process	p,
		v_$parameter	m
	where	s.sid = in_sid
	and	s.serial# = in_serial_nbr
	and	p.addr = s.paddr
	and	m.name = 'user_dump_dest';
	--
	dbms_system.set_ev(in_sid, in_serial_nbr, 10046, 12, '');
	--
	dbms_output.put_line('Trace output file is "' || v_outstring || '"');
	--
exception
	when no_data_found then
		raise_application_error(-20001,
					'No session with this SID/SERIAL#');
end;
/
show errors

whenever sqlerror continue
drop public synonym trclvl12;
whenever sqlerror exit failure

create public synonym trclvl12 for trclvl12;
grant execute on trclvl12 to public;

exit success