CREATE OR REPLACE package body QADBA.util
is
function ndate(dt in date)
return date
is
begin
return nvl(dt, to_date('01/01/0001', 'MM/DD/YYYY'));
end;
function instance
return varchar2
is
buf varchar2(80);
i pls_integer;
begin
select * into buf from global_name;
return basename(buf);
end;
function basename(s in varchar2)
return varchar2
is
buf varchar2(80);
i pls_integer;
begin
i := instr(s, '.');
if i = 0
then
i := length(s);
else
i := i - 1;
end if;
return substr(s, 0, i);
end;
procedure create_log(logname in varchar2)
is
begin
log(logname, user, 'context', 'create');
insplog.util.create_log_table(logname);
exception
when others
then
dbms_output.put_line('create_log:' || sqlcode || ' - ' || sqlerrm);
end;
procedure remove_log(logname in varchar2)
is
lfile varchar2(80);
begin
lfile := lower(instance) || '.' || logname;
utl_file.fremove('INSP_LOG_DIR', lfile);
exception
when others
then
dbms_output.put_line('remove error:' || sqlcode || ' - ' || sqlerrm);
end;
procedure log(logname in varchar2, usr in varchar2, context in varchar2, message in varchar2)
is
fp utl_file.file_type;
lfile varchar2(80);
begin
lfile := lower(instance) || '.' || logname;
begin
fp := utl_file.fopen('INSP_LOG_DIR', lfile, 'a');
exception
when others
then
dbms_output.put_line('open error:' || sqlcode || ' - ' || sqlerrm);
return;
end;
begin
utl_file.put_line(fp,
usr
|| '|'
|| to_char(sysdate, 'YYYY/MM/DD')
|| '|'
|| to_char(sysdate, 'HH24:MI:SS')
|| '|'
|| context
|| '|'
|| message);
exception
when others
then
dbms_output.put_line('put error:' || sqlcode || ' - ' || sqlerrm);
-- null;
end;
begin
utl_file.fclose(fp);
exception
when others
then
dbms_output.put_line('others error:' || sqlcode || ' - ' || sqlerrm);
-- null;
end;
exception
when others
then
null;
end;
-------------------------------------------------------------------------------------
procedure p(s1 in varchar2, s2 in varchar2 := null, s3 in varchar2 := null, s4 in varchar2 := null, s5 in varchar2 := null,
s6 in varchar2 := null, s7 in varchar2 := null, s8 in varchar2 := null, s9 in varchar2 := null, s10 in varchar2 := null,
s11 in varchar2 := null, s12 in varchar2 := null, s13 in varchar2 := null, s14 in varchar2 := null, s15 in varchar2 := null,
s16 in varchar2 := null, s17 in varchar2 := null, s18 in varchar2 := null, s19 in varchar2 := null, s20 in varchar2 := null,
s21 in varchar2 := null, s22 in varchar2 := null, s23 in varchar2 := null, s24 in varchar2 := null, s25 in varchar2 := null,
s26 in varchar2 := null, s27 in varchar2 := null, s28 in varchar2 := null, s29 in varchar2 := null, s30 in varchar2 := null,
s31 in varchar2 := null, s32 in varchar2 := null, s33 in varchar2 := null, s34 in varchar2 := null, s35 in varchar2 := null,
s36 in varchar2 := null, s37 in varchar2 := null, s38 in varchar2 := null, s39 in varchar2 := null, s40 in varchar2 := null,
s41 in varchar2 := null, s42 in varchar2 := null, s43 in varchar2 := null, s44 in varchar2 := null, s45 in varchar2 := null,
s46 in varchar2 := null, s47 in varchar2 := null, s48 in varchar2 := null, s49 in varchar2 := null, s50 in varchar2 := null,
s51 in varchar2 := null, s52 in varchar2 := null, s53 in varchar2 := null, s54 in varchar2 := null, s55 in varchar2 := null,
s56 in varchar2 := null, s57 in varchar2 := null, s58 in varchar2 := null, s59 in varchar2 := null, s60 in varchar2 := null,
s61 in varchar2 := null, s62 in varchar2 := null, s63 in varchar2 := null, s64 in varchar2 := null)
is
buf varchar2(8192) := null;
delim varchar2(1) := ' ';
procedure cat(s in varchar2)
is
begin
if s is not null
then
buf := buf || s || delim;
end if;
end;
begin
cat(s1);
cat(s2);
cat(s3);
cat(s4);
cat(s5);
cat(s6);
cat(s7);
cat(s8);
cat(s9);
cat(s10);
cat(s11);
cat(s12);
cat(s13);
cat(s14);
cat(s15);
cat(s16);
cat(s17);
cat(s18);
cat(s19);
cat(s20);
cat(s21);
cat(s22);
cat(s23);
cat(s24);
cat(s25);
cat(s26);
cat(s27);
cat(s28);
cat(s29);
cat(s30);
cat(s31);
cat(s32);
cat(s33);
cat(s34);
cat(s35);
cat(s36);
cat(s37);
cat(s38);
cat(s39);
cat(s40);
cat(s41);
cat(s42);
cat(s43);
cat(s44);
cat(s45);
cat(s46);
cat(s47);
cat(s48);
cat(s49);
cat(s50);
cat(s51);
cat(s52);
cat(s53);
cat(s54);
cat(s55);
cat(s56);
cat(s57);
cat(s58);
cat(s59);
cat(s60);
cat(s61);
cat(s62);
cat(s63);
cat(s64);
dbms_output.put_line(buf);
end;
-------------------------------------------------------------------------------
procedure l(logname in varchar2, usr in varchar2, context in varchar2, s1 in varchar2, s2 in varchar2 := null,
s3 in varchar2 := null, s4 in varchar2 := null, s5 in varchar2 := null, s6 in varchar2 := null, s7 in varchar2 := null,
s8 in varchar2 := null, s9 in varchar2 := null, s10 in varchar2 := null, s11 in varchar2 := null, s12 in varchar2 := null,
s13 in varchar2 := null, s14 in varchar2 := null, s15 in varchar2 := null, s16 in varchar2 := null, s17 in varchar2 := null,
s18 in varchar2 := null, s19 in varchar2 := null, s20 in varchar2 := null, s21 in varchar2 := null, s22 in varchar2 := null,
s23 in varchar2 := null, s24 in varchar2 := null, s25 in varchar2 := null, s26 in varchar2 := null, s27 in varchar2 := null,
s28 in varchar2 := null, s29 in varchar2 := null, s30 in varchar2 := null, s31 in varchar2 := null, s32 in varchar2 := null,
s33 in varchar2 := null, s34 in varchar2 := null, s35 in varchar2 := null, s36 in varchar2 := null, s37 in varchar2 := null,
s38 in varchar2 := null, s39 in varchar2 := null, s40 in varchar2 := null, s41 in varchar2 := null, s42 in varchar2 := null,
s43 in varchar2 := null, s44 in varchar2 := null, s45 in varchar2 := null, s46 in varchar2 := null, s47 in varchar2 := null,
s48 in varchar2 := null, s49 in varchar2 := null, s50 in varchar2 := null, s51 in varchar2 := null, s52 in varchar2 := null,
s53 in varchar2 := null, s54 in varchar2 := null, s55 in varchar2 := null, s56 in varchar2 := null, s57 in varchar2 := null,
s58 in varchar2 := null, s59 in varchar2 := null, s60 in varchar2 := null, s61 in varchar2 := null, s62 in varchar2 := null,
s63 in varchar2 := null, s64 in varchar2 := null)
is
buf varchar2(8192) := null;
delim varchar2(1) := ' ';
procedure cat(s in varchar2)
is
begin
if s is not null
then
buf := buf || s || delim;
end if;
end;
begin
cat(s1);
cat(s2);
cat(s3);
cat(s4);
cat(s5);
cat(s6);
cat(s7);
cat(s8);
cat(s9);
cat(s10);
cat(s11);
cat(s12);
cat(s13);
cat(s14);
cat(s15);
cat(s16);
cat(s17);
cat(s18);
cat(s19);
cat(s20);
cat(s21);
cat(s22);
cat(s23);
cat(s24);
cat(s25);
cat(s26);
cat(s27);
cat(s28);
cat(s29);
cat(s30);
cat(s31);
cat(s32);
cat(s33);
cat(s34);
cat(s35);
cat(s36);
cat(s37);
cat(s38);
cat(s39);
cat(s40);
cat(s41);
cat(s42);
cat(s43);
cat(s44);
cat(s45);
cat(s46);
cat(s47);
cat(s48);
cat(s49);
cat(s50);
cat(s51);
cat(s52);
cat(s53);
cat(s54);
cat(s55);
cat(s56);
cat(s57);
cat(s58);
cat(s59);
cat(s60);
cat(s61);
cat(s62);
cat(s63);
cat(s64);
log(logname, user, context, buf);
end;
FUNCTION SPLIT (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array
IS
i number :=0;
pos number :=0;
lv_str varchar2(50) := p_in_string;
strings t_array;
BEGIN
-- determine first chuck of string
pos := instr(lv_str,p_delim,1,1);
-- while there are chunks left, loop
WHILE ( pos != 0) LOOP
-- increment counter
i := i + 1;
-- create array element for chuck of string
strings(i) := substr(lv_str,1,pos-1);
-- remove chunk from string
lv_str := substr(lv_str,pos+1,length(lv_str));
-- determine next chunk
pos := instr(lv_str,p_delim,1,1);
-- no last chunk, add to array
IF pos = 0 THEN
strings(i+1) := lv_str;
END IF;
END LOOP;
-- return array
RETURN strings;
END SPLIT;
end;
/
is
function ndate(dt in date)
return date
is
begin
return nvl(dt, to_date('01/01/0001', 'MM/DD/YYYY'));
end;
function instance
return varchar2
is
buf varchar2(80);
i pls_integer;
begin
select * into buf from global_name;
return basename(buf);
end;
function basename(s in varchar2)
return varchar2
is
buf varchar2(80);
i pls_integer;
begin
i := instr(s, '.');
if i = 0
then
i := length(s);
else
i := i - 1;
end if;
return substr(s, 0, i);
end;
procedure create_log(logname in varchar2)
is
begin
log(logname, user, 'context', 'create');
insplog.util.create_log_table(logname);
exception
when others
then
dbms_output.put_line('create_log:' || sqlcode || ' - ' || sqlerrm);
end;
procedure remove_log(logname in varchar2)
is
lfile varchar2(80);
begin
lfile := lower(instance) || '.' || logname;
utl_file.fremove('INSP_LOG_DIR', lfile);
exception
when others
then
dbms_output.put_line('remove error:' || sqlcode || ' - ' || sqlerrm);
end;
procedure log(logname in varchar2, usr in varchar2, context in varchar2, message in varchar2)
is
fp utl_file.file_type;
lfile varchar2(80);
begin
lfile := lower(instance) || '.' || logname;
begin
fp := utl_file.fopen('INSP_LOG_DIR', lfile, 'a');
exception
when others
then
dbms_output.put_line('open error:' || sqlcode || ' - ' || sqlerrm);
return;
end;
begin
utl_file.put_line(fp,
usr
|| '|'
|| to_char(sysdate, 'YYYY/MM/DD')
|| '|'
|| to_char(sysdate, 'HH24:MI:SS')
|| '|'
|| context
|| '|'
|| message);
exception
when others
then
dbms_output.put_line('put error:' || sqlcode || ' - ' || sqlerrm);
-- null;
end;
begin
utl_file.fclose(fp);
exception
when others
then
dbms_output.put_line('others error:' || sqlcode || ' - ' || sqlerrm);
-- null;
end;
exception
when others
then
null;
end;
-------------------------------------------------------------------------------------
procedure p(s1 in varchar2, s2 in varchar2 := null, s3 in varchar2 := null, s4 in varchar2 := null, s5 in varchar2 := null,
s6 in varchar2 := null, s7 in varchar2 := null, s8 in varchar2 := null, s9 in varchar2 := null, s10 in varchar2 := null,
s11 in varchar2 := null, s12 in varchar2 := null, s13 in varchar2 := null, s14 in varchar2 := null, s15 in varchar2 := null,
s16 in varchar2 := null, s17 in varchar2 := null, s18 in varchar2 := null, s19 in varchar2 := null, s20 in varchar2 := null,
s21 in varchar2 := null, s22 in varchar2 := null, s23 in varchar2 := null, s24 in varchar2 := null, s25 in varchar2 := null,
s26 in varchar2 := null, s27 in varchar2 := null, s28 in varchar2 := null, s29 in varchar2 := null, s30 in varchar2 := null,
s31 in varchar2 := null, s32 in varchar2 := null, s33 in varchar2 := null, s34 in varchar2 := null, s35 in varchar2 := null,
s36 in varchar2 := null, s37 in varchar2 := null, s38 in varchar2 := null, s39 in varchar2 := null, s40 in varchar2 := null,
s41 in varchar2 := null, s42 in varchar2 := null, s43 in varchar2 := null, s44 in varchar2 := null, s45 in varchar2 := null,
s46 in varchar2 := null, s47 in varchar2 := null, s48 in varchar2 := null, s49 in varchar2 := null, s50 in varchar2 := null,
s51 in varchar2 := null, s52 in varchar2 := null, s53 in varchar2 := null, s54 in varchar2 := null, s55 in varchar2 := null,
s56 in varchar2 := null, s57 in varchar2 := null, s58 in varchar2 := null, s59 in varchar2 := null, s60 in varchar2 := null,
s61 in varchar2 := null, s62 in varchar2 := null, s63 in varchar2 := null, s64 in varchar2 := null)
is
buf varchar2(8192) := null;
delim varchar2(1) := ' ';
procedure cat(s in varchar2)
is
begin
if s is not null
then
buf := buf || s || delim;
end if;
end;
begin
cat(s1);
cat(s2);
cat(s3);
cat(s4);
cat(s5);
cat(s6);
cat(s7);
cat(s8);
cat(s9);
cat(s10);
cat(s11);
cat(s12);
cat(s13);
cat(s14);
cat(s15);
cat(s16);
cat(s17);
cat(s18);
cat(s19);
cat(s20);
cat(s21);
cat(s22);
cat(s23);
cat(s24);
cat(s25);
cat(s26);
cat(s27);
cat(s28);
cat(s29);
cat(s30);
cat(s31);
cat(s32);
cat(s33);
cat(s34);
cat(s35);
cat(s36);
cat(s37);
cat(s38);
cat(s39);
cat(s40);
cat(s41);
cat(s42);
cat(s43);
cat(s44);
cat(s45);
cat(s46);
cat(s47);
cat(s48);
cat(s49);
cat(s50);
cat(s51);
cat(s52);
cat(s53);
cat(s54);
cat(s55);
cat(s56);
cat(s57);
cat(s58);
cat(s59);
cat(s60);
cat(s61);
cat(s62);
cat(s63);
cat(s64);
dbms_output.put_line(buf);
end;
-------------------------------------------------------------------------------
procedure l(logname in varchar2, usr in varchar2, context in varchar2, s1 in varchar2, s2 in varchar2 := null,
s3 in varchar2 := null, s4 in varchar2 := null, s5 in varchar2 := null, s6 in varchar2 := null, s7 in varchar2 := null,
s8 in varchar2 := null, s9 in varchar2 := null, s10 in varchar2 := null, s11 in varchar2 := null, s12 in varchar2 := null,
s13 in varchar2 := null, s14 in varchar2 := null, s15 in varchar2 := null, s16 in varchar2 := null, s17 in varchar2 := null,
s18 in varchar2 := null, s19 in varchar2 := null, s20 in varchar2 := null, s21 in varchar2 := null, s22 in varchar2 := null,
s23 in varchar2 := null, s24 in varchar2 := null, s25 in varchar2 := null, s26 in varchar2 := null, s27 in varchar2 := null,
s28 in varchar2 := null, s29 in varchar2 := null, s30 in varchar2 := null, s31 in varchar2 := null, s32 in varchar2 := null,
s33 in varchar2 := null, s34 in varchar2 := null, s35 in varchar2 := null, s36 in varchar2 := null, s37 in varchar2 := null,
s38 in varchar2 := null, s39 in varchar2 := null, s40 in varchar2 := null, s41 in varchar2 := null, s42 in varchar2 := null,
s43 in varchar2 := null, s44 in varchar2 := null, s45 in varchar2 := null, s46 in varchar2 := null, s47 in varchar2 := null,
s48 in varchar2 := null, s49 in varchar2 := null, s50 in varchar2 := null, s51 in varchar2 := null, s52 in varchar2 := null,
s53 in varchar2 := null, s54 in varchar2 := null, s55 in varchar2 := null, s56 in varchar2 := null, s57 in varchar2 := null,
s58 in varchar2 := null, s59 in varchar2 := null, s60 in varchar2 := null, s61 in varchar2 := null, s62 in varchar2 := null,
s63 in varchar2 := null, s64 in varchar2 := null)
is
buf varchar2(8192) := null;
delim varchar2(1) := ' ';
procedure cat(s in varchar2)
is
begin
if s is not null
then
buf := buf || s || delim;
end if;
end;
begin
cat(s1);
cat(s2);
cat(s3);
cat(s4);
cat(s5);
cat(s6);
cat(s7);
cat(s8);
cat(s9);
cat(s10);
cat(s11);
cat(s12);
cat(s13);
cat(s14);
cat(s15);
cat(s16);
cat(s17);
cat(s18);
cat(s19);
cat(s20);
cat(s21);
cat(s22);
cat(s23);
cat(s24);
cat(s25);
cat(s26);
cat(s27);
cat(s28);
cat(s29);
cat(s30);
cat(s31);
cat(s32);
cat(s33);
cat(s34);
cat(s35);
cat(s36);
cat(s37);
cat(s38);
cat(s39);
cat(s40);
cat(s41);
cat(s42);
cat(s43);
cat(s44);
cat(s45);
cat(s46);
cat(s47);
cat(s48);
cat(s49);
cat(s50);
cat(s51);
cat(s52);
cat(s53);
cat(s54);
cat(s55);
cat(s56);
cat(s57);
cat(s58);
cat(s59);
cat(s60);
cat(s61);
cat(s62);
cat(s63);
cat(s64);
log(logname, user, context, buf);
end;
FUNCTION SPLIT (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array
IS
i number :=0;
pos number :=0;
lv_str varchar2(50) := p_in_string;
strings t_array;
BEGIN
-- determine first chuck of string
pos := instr(lv_str,p_delim,1,1);
-- while there are chunks left, loop
WHILE ( pos != 0) LOOP
-- increment counter
i := i + 1;
-- create array element for chuck of string
strings(i) := substr(lv_str,1,pos-1);
-- remove chunk from string
lv_str := substr(lv_str,pos+1,length(lv_str));
-- determine next chunk
pos := instr(lv_str,p_delim,1,1);
-- no last chunk, add to array
IF pos = 0 THEN
strings(i+1) := lv_str;
END IF;
END LOOP;
-- return array
RETURN strings;
END SPLIT;
end;
/
No comments:
Post a Comment