drop table system_config;
create table system_config
(param_type varchar2(50),
 param_key varchar2(100),
 param_value varchar2(4000) CONSTRAINT PARAM_VALUE_NN NOT NULL,
 param_comment  varchar2(100),
 created_on timestamp default systimestamp,
 created_by varchar2(100) default sys_context('USERENV','OS_USER'),
 CONSTRAINT pk_system_config PRIMARY KEY (PARAM_TYPE,PARAM_KEY) USING INDEX
);

comment on table system_config is 'this table will be used to softcode things in application 

code';

comment on column system_config.param_value is 'this column can store nig value like email 

disclaimer or messages to popu up on screen , date value etc..';

insert into system_config(param_type,param_key,param_value)
values('debug','switch','Y');

drop table system_error;

create table system_error
(app_name varchar2(50),
 err_code number,
 err_message varchar2(4000),
 created_on timestamp default systimestamp,
 created_by varchar2(100) default sys_context('USERENV','OS_USER')
);

comment on table system_error is 'captures all application level error based on debug 

switch';

create or replace package pkg_demo_util2
is
/*
----------------------------------------------------------
version	author		date		comments
----------------------------------------------------------
1.0	Sachi Padhi	04/06/2015	this is an sample utility package 
----------------------------------------------------------
*/

type system_param_r --record variable
IS
  record
  (
    KEY VARCHAR2(200),
    param_value system_param.param_value%type );

type system_param_tn --numeric index array
IS
  TABLE OF system_param_r INDEX BY binary_integer;

type system_param_tv --varchar2 index array
IS
  TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(200);



procedure prc_store_error(
	p_app_name	system_error.app_name%type,
	p_err_code	system_error.err_code%type,
	p_err_message	system_error.err_message%type
);

procedure prc_store_n_raise_error(
	p_app_name	system_error.app_name%type,
	p_err_code	system_error.err_code%type,
	p_err_message	system_error.err_message%type
);

function fn_get_config_value(
	p_config_type system_config.param_type%type,
	p_config_key system_config.param_key%type
	) return varchar2
result_cache
;

g_app_name varchar2(50) ;
end pkg_demo_util2;
/


create or replace package body pkg_demo_util2
is
/*
----------------------------------------------------------
version	author		date		comments
----------------------------------------------------------
1.0	Sachi Padhi	04/06/2015	this is an sample utility package
----------------------------------------------------------
*/
  tbl_system_param_n system_param_tn; --number index array
  tbl_system_param_v system_param_tv; --varchar2 index array
procedure prc_store_error(
	p_app_name	system_error.app_name%type,
	p_err_code	system_error.err_code%type,
	p_err_message	system_error.err_message%type
) 
is pragma autonomous_transaction;
begin
  if pkg_demo_util2.fn_get_config_value('debug','switch') = 'Y' then
	insert into system_error(app_name,err_code, err_message)
	values(p_app_name,p_err_code, p_err_message);
	commit;
  end if;	
end prc_store_error;


procedure prc_store_n_raise_error(
	p_app_name	system_error.app_name%type,
	p_err_code	system_error.err_code%type,
	p_err_message	system_error.err_message%type
) 
is 
begin
	prc_store_error(
	p_app_name	=> p_app_name,
	p_err_code	=> p_err_code,
	p_err_message	=> p_err_message
	) ;
	raise_application_error(-20001 ,p_err_message);
end prc_store_n_raise_error;

function fn_get_config_value(
	p_config_type system_config.param_type%type,
	p_config_key system_config.param_key%type
	) return varchar2
result_cache 
relies_on (system_config)
is
	v_result system_config.param_value%type;
begin

	select param_value into v_result 
	from system_config
	where param_type = p_config_type
	and param_key = p_config_key;

	return v_result ;
exception
when others then
	prc_store_n_raise_error(
	p_app_name	=> g_app_name,
	p_err_code	=> SQLCODE,
	p_err_message	=> DBMS_UTILITY.FORMAT_ERROR_STACK
	) ;	
end fn_get_config_value;

--special initialization block for the session
--Here you can populate all static table data into package collection index by varchar2
--and use the collection in your package instead of quering the tables
begin
  g_app_name  := nvl(g_app_name ,sys_context('CLIENTCONTEXT','APP_NAME'));

  SELECT upper(trim(param_type)
    ||'-'
    ||trim(param_key)) AS KEY,
    param_value bulk collect
  INTO tbl_system_param_n
  FROM system_config;

  --convert the numeric index array to varchar2 index array for future use in package body
  FOR i IN 1 .. tbl_system_param_n.count
  LOOP
    tbl_system_param_v(tbl_system_param_n(i).key) := tbl_system_param_n(i).param_value;
    dbms_output.put_line('key - value '||tbl_system_param_n(i).key ||' - '||                 

tbl_system_param_n(i).param_value);
  END LOOP;

end pkg_demo_util2;
/