CREATE
OR
REPLACE
package my_pack
as
TYPE p_cursor IS REF CURSOR ;
end ;
CREATE OR REPLACE procedure server_check_report_pro(p_cursor out my_pack.p_cursor)
is
vlevel number ;
vre varchar2 ( 3000 );
cursor c1 is
select * from server_dbcheck_list;
begin
delete from server_check_report;
for c in c1
loop
server_getlogbyip(c.ip,vre,vlevel);
insert into server_check_report (ip,check_item,check_order,error_level,detail) values
(c.ip, ' OS DB Log Check ' , 0 ,vlevel,substr(vre, 1 , 2999 ));
end loop;
insert into server_check_report(ip,check_item,check_order,error_level,detail)
select a.ip, ' Disk Spaces Check ' , 1 ,nvl(b.error_level, 0 ),nvl(b.remark, '' )
from server_dbcheck_list a,server_logcheck_logs b
where a.ip = b.ip( + )
and error_level( + ) > 0
and id( + ) = 4 ;
insert into server_check_report (ip,check_item,check_order,error_level,detail)
select a.ip,a.tns_name || ' : ' || b.LOG_INFO check_item, 2 ,nvl(b.error_level, 0 ) error_level,decode(nvl(b.error_level, 0 ), 0 , '' , replace ( replace (b.value_list, ' #### ' , ' , ' ), ' " ' , '' )) remark
from server_dbcheck_list a,server_dbcheck_logs b
where a.ip = b.ip( + )
and a.tns_name = b.tns_name( + )
and error_level( + ) > 0
and b.id( + ) = 1 ;
commit ;
open p_cursor for ' select * from server_check_report order by ip,check_order ' ;
end ;
/
TYPE p_cursor IS REF CURSOR ;
end ;
CREATE OR REPLACE procedure server_check_report_pro(p_cursor out my_pack.p_cursor)
is
vlevel number ;
vre varchar2 ( 3000 );
cursor c1 is
select * from server_dbcheck_list;
begin
delete from server_check_report;
for c in c1
loop
server_getlogbyip(c.ip,vre,vlevel);
insert into server_check_report (ip,check_item,check_order,error_level,detail) values
(c.ip, ' OS DB Log Check ' , 0 ,vlevel,substr(vre, 1 , 2999 ));
end loop;
insert into server_check_report(ip,check_item,check_order,error_level,detail)
select a.ip, ' Disk Spaces Check ' , 1 ,nvl(b.error_level, 0 ),nvl(b.remark, '' )
from server_dbcheck_list a,server_logcheck_logs b
where a.ip = b.ip( + )
and error_level( + ) > 0
and id( + ) = 4 ;
insert into server_check_report (ip,check_item,check_order,error_level,detail)
select a.ip,a.tns_name || ' : ' || b.LOG_INFO check_item, 2 ,nvl(b.error_level, 0 ) error_level,decode(nvl(b.error_level, 0 ), 0 , '' , replace ( replace (b.value_list, ' #### ' , ' , ' ), ' " ' , '' )) remark
from server_dbcheck_list a,server_dbcheck_logs b
where a.ip = b.ip( + )
and a.tns_name = b.tns_name( + )
and error_level( + ) > 0
and b.id( + ) = 1 ;
commit ;
open p_cursor for ' select * from server_check_report order by ip,check_order ' ;
end ;
/
如何取得紀錄集呢,打開SQLProgress,登陸進去,點菜單"advance"-->exec func/prod ...在彈出的框中輸入procedure的名字,和參數,直接點OK就可以看到結果.具體操作可以看看SQLProgress的幫助.