/*
 * Title  : smpl_logger.sql
 * Author : EXADIME LLC
 * Created: 3-JUL-2017
 * Updated: 3-JUL-2017
 * Release: 1.0
 * Purpose: Samples Logger Utility
 * History: N/A
 */

--Program Specifications
--Properties 1.TRACE_LEVEL 2.TARGET
--TRACE_LEVEL can be 1-Error 2-Log 3-Trace (Basic information at lower number to tracing at higher number)
--TARGET 1.Table 2.File
--Parameters MESSAGE_LEVEL, ERROR_LOC, ERROR_CODE, ERROR_MESSAGE, ERROR_DETAILS 
--Target Table Name LOG_TABLE
--Entry columns MESSAGE_LEVEL, ERROR_LOC, ERROR_CODE, ERROR_MESSAGE, ERROR_DETAILS, TIME_STAMP
--Column Information: 	1.Error Code and error Message is a user defined list.
--			2.Error Details column displays the error details.
--log_table(
--	error_key	number(8),
--	error_level	number(1),
--	error_loc	varchar2(30),
--	error_code	varchar2(10),
--	error_message	varchar2(100),
--	error_details	varchar2(100),
--	user_id		number(8),
--	time_stamp	date
--);

create table log_table(
	error_key	number(8),
	error_level	number(1),
	error_loc	varchar2(30),
	error_code	varchar2(20),
	error_message	varchar2(1000),
	error_details	varchar2(1000),
	user_id		number(8),
	time_stamp	date
);

create or replace package smpl_logger
is
	--signature #1
	procedure write_log(p_message_level pls_integer,p_error_loc in varchar2,p_error_code in varchar2, 
	p_error_message varchar2,p_error_details varchar2,p_user_id number default uid);

	--signature #2
	procedure write_log(p_message_level pls_integer,p_error_loc in varchar2,p_error_code in varchar2, 
	p_error_message varchar2);
		
end smpl_logger;
/

create or replace package body smpl_logger
is
	--signature #1
	procedure write_log(p_message_level pls_integer,p_error_loc in varchar2,p_error_code in varchar2, 
	p_error_message varchar2,p_error_details varchar2,p_user_id number default uid)
	is	
		pragma autonomous_transaction;
		trace_level	number:=10;
		v_error_key	number:=1;
		
	begin	
		select nvl(max(error_key),0)+1 into v_error_key from log_table;
		
		if p_message_level <= trace_level then  
			insert into /*+nologging*/ log_table
			(
				error_key,
				error_level, 
				error_loc,
				error_code,
				error_message,
				error_details,
				user_id,
				time_stamp
			)
			values
			(
				v_error_key,
				p_message_level,
				p_error_loc,
				p_error_code,
				p_error_message,
				p_error_details,
				p_user_id,
				sysdate
			);
			commit;
		end if;
	end write_log;
	
	--signature #2
	procedure write_log(p_message_level pls_integer,p_error_loc in varchar2,p_error_code in varchar2, 
	p_error_message varchar2)
	is
	begin
		write_log(p_message_level ,p_error_loc,p_error_code,p_error_message,null);
	end write_log;
	
end smpl_logger;	
/
