-- alert_log_util_pkg.sql
-- jared still jkstill@cybcon.com
-- first create the alert_log external table
-- via the alert_log_external_table.sql script
--
-- the alert_log_util package is used to query the
-- alert_log external table.
-- 

drop type alert_log_row_set;

create or replace type alert_log_row_t as object 
(
  text varchar2(400)
);
/

show error type alert_log_row_t

create or replace type alert_log_row_set as table of alert_log_row_t;
/

show error type alert_log_row_set

create or replace package alert_log_util
is
	type refcur_alert_t is ref cursor return alert_log%rowtype;

	--!! see package body for more details and example usages

	/*
	returns error lines from the alert.log file
	includes ORA- and TNS- errors, as well
	as trace file names and timestamps
	*/
	function alert_log_errors(p refcur_alert_t) 
	return alert_log_row_set pipelined;

	/*
	this function causes externals tables to be created for
	trace files found in the alert log
	*/
	function build_alert_log_trace_tables(p refcur_alert_t) 
	return alert_log_row_set pipelined;

	/*
	this procedure causes externals tables to be created for
	trace files found in the BDUMP and UDUMP directories
	*/
	procedure build_dumpdir_trace_tables;

	/*
	create an external table for the trace file of a currently
	executing function
	*/
	function build_session_trace_table(sid_in number, serial_in number)
	return varchar2;

end;
/

show error package alert_log_util

create or replace package body alert_log_util
is

	debug boolean := true;

	-- directory names initialized at first execution
	-- see end of package
	type dir_t is table of varchar2(30) index by binary_integer;
	dir_table dir_t;

	/*
	*******************************************************
	procedure get_dir_list( directory_in in varchar2, oracle_directory in varchar2 )

	this procedure calls the java used to get a directory
	listing. 
	use dump_location() to translate and Oracle directory
	into a path name used to call this
	eg. get_dir_list('/u01/oracle/admin/ts01/bdump','BDUMP')
	*******************************************************
	*/
	procedure get_dir_list( directory_in in varchar2, oracle_directory in varchar2 )
   	as language java
	name 'DirList.getList( java.lang.String, java.lang.String )';

	function file_exists ( dir_in varchar2, file_name_in varchar2)
	return boolean
	is
		v_file_exists boolean;
		v_file_length number;
		v_file_blocksize number;
	begin

		utl_file.fgetattr(
			dir_in, 
			file_name_in, 
			v_file_exists, 
			v_file_length, 
			v_file_blocksize
		);

		return v_file_exists;

	end;


	/*
	******************************************************
	function dump_location( dumpdir_in varchar2 ) return varchar2

	pass the name of an oracle directory
	dir name converted to upper case
	return the actual directory name

	eg. v_dumpdir := dump_location('BDUMP')
	******************************************************
	*/

	function dump_location( dumpdir_in varchar2 ) return varchar2
	is
		r_dir varchar2(255);
	begin
		-- all directories seem to be owned by SYS		
		select directory_path into r_dir
		from dba_directories
		where owner = 'SYS'
		and directory_name = upper(dumpdir_in);

		return r_dir;

	end;


	/*
	******************************************************
	procedure trace_table_ddl( 
		table_name_in varchar2
		, trace_file_name_in varchar2
		, dumpdir_in varchar2
	)

	execute DDL to create external tables for trace files
	pass in the name of the table, name of trace file and
	whether it is in BDUMP or UDUMP

	Ignores ORA-955 object already exists

	eg. trace_table_ddl('TS01_ORA_1449','ts01_ora_1449.trc','UDUMP')
	******************************************************
	*/

	procedure trace_table_ddl ( 
		table_name_in varchar2
		, trace_file_name_in varchar2
		, dumpdir_in varchar2
	)
	is
		pragma autonomous_transaction;

		table_exists exception;
		pragma exception_init( table_exists, -955 );
		v_sql varchar2(2000);

	begin

		v_sql := 'create table ' || table_name_in || ' ( text varchar2(400) ) '
			|| ' organization external ( ' 
			|| '    type oracle_loader '
			|| '    default directory ' || upper(dumpdir_in)
			|| '    access parameters ( '
			|| '       records delimited by newline '
			|| '       nobadfile '
			|| '       nodiscardfile '
			|| '       nologfile '
			|| '    ) '
			|| ' location(' || '''' || trace_file_name_in || '''' || ')'
			|| ' ) '
			|| ' reject limit unlimited ';

		if debug then
			dbms_output.put_line(substr(v_sql,1,255));
			dbms_output.put_line(substr(v_sql,255));
		end if;

		begin
			execute immediate v_sql;	
		exception
		when table_exists then
			null;
		when others then
			raise;
		end;
	end;


	/*
	******************************************************
	function alert_log_errors(p refcur_alert_t) 
		return alert_log_row_set

	Returns all error rows from the alert log external table 
	via 'pipe row'.  Includes trace file lines and date 
	stamp lines.  Date stamp only printed when it changes.

	Requires that the external table already created 
	via alert_log_external_table.sql

	eg. select * 
	from table(alert_log_util.alert_log_errors(cursor(select * from alert_log)))
	******************************************************
	*/

	function alert_log_errors(p refcur_alert_t) 
		return alert_log_row_set
	pipelined is
		out_rec alert_log_row_t := alert_log_row_t(null);
		trec p%rowtype;
		currdate varchar2(400);
		last_printed_date varchar2(400);
		testday varchar2(3);
		testerr varchar2(4);
		firstdate boolean := true;
	begin
		currdate := 'NA';
		last_printed_date := currdate;
		loop
			fetch p into trec; 
			exit when p%notfound;

			trec.text := ltrim(trec.text);

			-- is this line a date stamp?
			testday := substr(trec.text,1,3);
			if testday = 'Sat'
				or testday = 'Sun'
				or testday = 'Mon'
				or testday = 'Tue'
				or testday = 'Wed'
				or testday = 'Thu'
				or testday = 'Fri'
			then
				currdate := trec.text;
			end if;

			testerr := substr(trec.text,1,4);
			if testerr = 'ORA-' 
				or testerr = 'TNS-'
				or substr(trec.text,1,14) = 'Errors in file'
			then
				-- several date stamp lines may contain the same date and time
				-- so only output it when it changes
				-- the 'or' with firstdate is to output the first date in the table
				if last_printed_date != currdate 
					or ( 
							currdate != 'NA' 
							and firstdate
						)
				then


					last_printed_date := currdate;
					firstdate := false;
					out_rec.text := '****';
					PIPE ROW(out_rec);
					out_rec.text := currdate;
					PIPE ROW(out_rec);
					out_rec.text := '****';
					PIPE ROW(out_rec);
				end if;

				-- debug
				if debug then
					out_rec.text := 'DEBUG - testerr: ' || testerr;
					pipe row(out_rec);
					out_rec.text := 'DEBUG - currdate: ' || currdate;
					pipe row(out_rec);
				end if;

				out_rec.text := trec.text;
				pipe row(out_rec);

			end if;

		end loop;
		close p;
		return;
	end;

	/*
	******************************************************
	function build_alert_log_trace_tables(p refcur_alert_t) 
		return alert_log_row_set

	This function creates an external table for all trace
	files found in the alert log. An external table is created
	only if the file actually exists.

	eg. select text
	from table(alert_log_util.build_alert_log_trace_tables(cursor(select * from alert_log)))
	******************************************************
	*/

	function build_alert_log_trace_tables(p refcur_alert_t) 
		return alert_log_row_set
	pipelined is
		out_rec alert_log_row_t := alert_log_row_t(null);
		trec p%rowtype;
		trace_file varchar2(400);
		trace_table varchar2(30);
		v_dump varchar2(10);
	begin
		loop
			fetch p into trec; 
			exit when p%notfound;

			trec.text := ltrim(trec.text);

			if substr(trec.text,1,14) = 'Errors in file'
			then

				-- remove leading text of 'Errors in file'
				trace_file := substr(trec.text, instr(trec.text,' ',1,3)+1);

				if instr(lower(trace_file), 'bdump') > 0 then
					v_dump := 'BDUMP';
				elsif instr(lower(trace_file), 'udump') > 0 then
					v_dump := 'UDUMP';
				else
					raise_application_error(-20000,'Cannot determine dump location of ' || trace_file);
				end if;

				-- translate slashes
				trace_file := translate(trace_file,'\','/');
				-- ' this quote fixes the VIM editor hiliting error caused by the
				-- backslash preceding the quote in the previous line

				-- remove directories
				trace_file := substr(trace_file, instr(trace_file,'/',-1)+1);

				-- remove the trailing colon
				trace_file := substr(trace_file,1,instr(trace_file,':')-1);

				-- remove suffix
				trace_table := substr(trace_file, 1, instr(trace_file,'.',1,1)-1);

				-- build if file exists
				if file_exists(v_dump,trace_file) then

					trace_table_ddl(trace_table, trace_file, v_dump);

					out_rec.text := trace_table;
					pipe row(out_rec);

				end if;
	
			end if;

		end loop;
		close p;
		return;
	end;

	/*
	**********************************************
	function build_session_trace_table(sid_in number, serial_in number)

	to build an external table for a trace file of a database session
	use the following query
	select alert_log_util.build_session_trace_table(SID,SERIAL#) from dual

	eg.  select alert_log_util.build_session_trace_table(17,23) from dual

	it is up to the user to ensure that tracing is on for the session
	**********************************************
	*/

	function build_session_trace_table(sid_in number, serial_in number)
	return varchar2
	is
		trace_file varchar2(400);
		trace_table varchar2(30);
		v_instance varchar2(20);
		v_process_id varchar2(20);
	begin

		select instance_name into v_instance
		from v$instance;

		begin
			select p.spid into v_process_id
			from v$session s, v$process p
			where s.sid = sid_in and s.serial# = serial_in
			and p.addr = s.paddr;
		exception
		when no_data_found then
			null;
		when others then
			raise;
		end;

		trace_table := v_instance || '_ora_' || v_process_id;
		trace_file := v_instance || '_ora_' || v_process_id || '.trc';

		dbms_output.put_line('trace file is: ' || trace_file);
		-- create the trace file external table
		-- build if file exists
		if file_exists('UDUMP',trace_file) then

			trace_table_ddl(trace_table, trace_file, 'UDUMP');

			return trace_table;

		else
			return 'FILE NOT FOUND: ' || trace_file;
		end if;

	end;

	/*
	**********************************************************
	procedure build_dumpdir_trace_tables

	build external tables for all trace files found in the
	the UDUMP and BDUMP directories.  The selection of directories
	scanned may be altered by modifying the values stored in
	the dir_table array as seen in the main section of this package.

	All entries stored there must of course have been previously
	created by the 'CREATE DIRECTORY' command

	eg.

	execute alert_log_util.build_dumpdir_trace_tables 

	select table_name
	from user_external_tables;
	**********************************************************
	*/
	procedure build_dumpdir_trace_tables
	is
		v_dumpdir varchar2(255);
		v_trace_table varchar2(30);
	begin
		-- loop through the dump_table array
		-- get the directory list
		-- build the external tables
		for i in 0..dir_table.last
		loop
			v_dumpdir := dump_location(dir_table(i));
			--dbms_output.put_line(dir_table(i));
			get_dir_list(v_dumpdir, dir_table(i));
		end loop;
		for frec in ( select * from dirlist )
		loop
			v_trace_table := substr(frec.filename,1,instr(frec.filename,'.')-1);
			trace_table_ddl(v_trace_table, frec.filename, frec.directoryname);
		end loop;
	end;


begin

	dir_table(0) := 'UDUMP';
	dir_table(1) := 'BDUMP';

end;
/

show error package body alert_log_util


drop public synonym alert_log_util;
create public synonym alert_log_util for system.alert_log_util;

grant execute on alert_log_util to dba;