Tom,
8.1.7.4 Standard Edition on Windoze 2000 Server w/ 3G memory & 1.8Ghz XEO CPU.
I have a daily job which checks alert.log for errors and I am getting the following in the alert log because of the job's itself:
Fri Sep 12 09:50:02 2008
Errors in file D:\OraHome\admin\d7i\bdump\d7iSNP9.TRC:
ORA-12012: error on auto execute of job 38066192
ORA-20000: ORA-06500: PL/SQL: storage error
ORA-06512: at "D7IPRD.ALERTLOG_NOTIFICATION", line 90
ORA-06512: at line 1
Fri Sep 12 09:51:04 2008
Thread 1 advanced to log sequence 344031
Current log# 2 seq# 344031 mem# 0: D:\ORAHOME\ORADATA\D7I\REDO02.LOG
Fri Sep 12 09:51:04 2008
ARC1: Beginning to archive log# 1 seq# 344030
ARC1: Completed archiving log# 1 seq# 344030
Fri Sep 12 09:52:18 2008
Errors in file D:\OraHome\admin\d7i\bdump\d7iSNP9.TRC:
ORA-12012: error on auto execute of job 38066192
ORA-20000: ORA-06500: PL/SQL: storage error
ORA-06512: at "D7IPRD.ALERTLOG_NOTIFICATION", line 90
ORA-06512: at line 1
Below is the procedure which is triggered by the job:
CREATE OR REPLACE PROCEDURE AlertLog_notification
AS
R_email varchar2(2000);
mbody varchar2(32000);
msubj varchar2(2000);
l_days number;
rcount number;
dfile SendMailJPkg.ATTACHMENTS_LIST;
l_errstatus number;
TYPE typ1 IS REF CURSOR;
ref_cr typ1;
SQ varchar2(5000);
l_user_code dw_lookups.lookup_value%TYPE;
BEGIN
l_errstatus := 0;
SQ := 'select lookup_value from dw_lookups where lookup_code = :1';
-- Get the list of users to be notified
r_email := ' ';
OPEN ref_cr FOR SQ using 'N_D7I_ALERTLOG_NOTIFICATION' ;
LOOP
FETCH ref_cr INTO l_user_code;
IF ref_cr%NOTFOUND THEN
EXIT;
END IF;
r_email := r_email || Getperinfo(l_user_code, 'E') || ',';
END LOOP;
r_email := trim(r_email);
IF dw_right(r_email, 1) = ',' THEN
r_email := substr(r_email, 1, length(r_email)-1);
END IF;
mbody := '*****************************************************' || chr(13);
mbody := mbody || '* THIS IS AN AUTOMATED EMAIL, PLEASE DO NOT REPLY *' || chr(13);
mbody := mbody || '*****************************************************' || chr(10) || chr(13);
mbody := mbody || 'FYI.' || chr(10) || chr(13);
mbody := mbody || 'Alert log has ERROR(s) in it.' || chr(10) || chr(13);
rcount := 0;
-- Check if there any contracts about to expire
FOR C1 IN ( select * from (
select text from d7i_alert_log t where rownum < 100 order by line desc
) where instr(text, 'ORA-') > 0
) LOOP
rcount := rcount + 1;
mbody := mbody || c1.text || chr(10) || chr(13);
END LOOP;
IF r_email IS NOT NULL
AND instr(r_email, '@') > 0
AND rcount > 0 THEN
msubj := 'Alert log has ERROR(s) in it.';
l_errstatus := DW_sendemail
(pcuser => 'testuser'
,pfile => dfile
,psendername => 'Administrator'
,psender => 'test@***.com'
,psubject => msubj
,pRecipient => r_email
,pbody => mbody );
END IF;
EXCEPTION WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
Could you point out which is memory intensive in this code please? I am guessing that it is the function or the view below, but I don't know how I can make this use less resources.
d7i_alert_log is a view that I created again with your help:
CREATE OR REPLACE VIEW D7I_ALERT_LOG AS
SELECT "LINE","TEXT"
FROM Table(Cast(Get_D7I_Alert() As Alert_Type))
/
CREATE OR REPLACE FUNCTION Get_D7I_Alert RETURN Alert_Type IS
Alert_Tab Alert_Type := Alert_Type();
l_file UTL_FILE.file_type;
l_line NUMBER(10) := 1;
l_text VARCHAR2(2000);
BEGIN
l_file := UTL_FILE.fopen('D:\OraHome\admin\d7i\bdump', 'TSTALRT.LOG', 'r');
BEGIN
LOOP
utl_file.get_line(l_file, l_text);
Alert_Tab.Extend;
Alert_Tab(Alert_Tab.Last) := Alert_Row_Type(l_line, l_text);
l_line := l_line + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UTL_FILE.fclose(l_file);
RETURN Alert_Tab;
END;
Thanks.