Oracle 存储过程问题

晚上单眼皮 2014-03-12 03:49:04

CREATE OR REPLACE PROCEDURE NEWFORM_STAT_REPORT(dayNum in number) IS
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(2000);
yesterday10 VARCHAR2(8);
yesterdayr VARCHAR2(8);
BEGIN
BEGIN
yesterday10 := to_char(SYSDATE - dayNum, 'yyyymmdd');
yesterdayr := to_char(SYSDATE - dayNum - 1, 'yyyymmdd');
DELETE FROM OMP_R_NEWFORM_STAT_REPORT where ISSLOG_DATE = yesterday10;

INSERT INTO OMP_R_NEWFORM_STAT_REPORT
(TABLE_NO, CARD_CODE, COUNT, ISSLOG_DATE)
SELECT I.TABLE_NO,
P.CARD_CODE,
COUNT(1),
TO_CHAR(TO_DATE(yesterdayr, 'YYYYMMDD'), 'YYYY-MM-DD')
FROM OMP_T_APP_INDEX I,
OMP_L_ISSLOG_DETAIL D,
OMP_M_FORM F,
OMP_T_DATA_SAVE_PRO P
WHERE I.APP_ID = D.APPID
AND I.TABLE_NO = F.TABLE_NO
AND I.APP_ID = P.APP_ID
AND F.TABLE_ATTRIBUTE = '0'
AND D.RESULT = '0'
AND D.ISSLOG_DATE = yesterday10
GROUP BY I.TABLE_NO, P.CARD_CODE, D.ISSLOG_DATE;

COMMIT;
EXCEPTION
when others then
rollback;
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
INSERT INTO OMP_M_USER_OPERATION
(RECORD_DATE, MODULE, USER_ID, USER_NAME, CONTENT, SO_ID)
VALUES
(SYSDATE,
'reportJsp/showReport.jsp_raq=/stat/OMP_R_NEWFORM_STAT_REPORT.raq',
'DATABASE',
null,
CONCAT(CONCAT('表单整合产品统计失败_', v_sqlcode), v_sqlerrm),
null);
COMMIT;
end;
END;
这个是我的存储过程，用一个job让它每天早上9点会自动跑，但最近有9次没有成功执行了，希望大家帮帮忙，是需要优化还是？？？，job执行的错误是“
ORA-04030: 在尝试分配 4193804 字节 (QERHJ hash-joi,QERHJ Hash Table Entries) 时进程内存不足”

...全文