求助：JOB執(zhí)行異常，手工執(zhí)行沒問題。這個存儲過程實現(xiàn)按相關(guān)維度聚集明細數(shù)據(jù)(按月)；之后，從1月-12月，依次向上補充當(dāng)期未發(fā)生數(shù)據(jù)(累計值補0) 。問題是，這個存儲過程，手工執(zhí)行沒問題，每次通過JOB調(diào)用，執(zhí)行到第二部補充數(shù)據(jù)的按月雙層循環(huán)，就卡住了?？ㄔ诘谝淮窝h(huán)，COMMIT總是不執(zhí)行。試圖跟蹤SQL，但是總是跟蹤不到，服務(wù)器沒有標(biāo)識的trace文件生成。跟蹤SQL這個問題如果手工執(zhí)行這個過程的話也沒問題。。。。期間，查看alert文件，沒有錯誤記錄。存儲過程本身也沒有異常拋出。 數(shù)據(jù)庫服務(wù)器是ORACLE 9I的。插入數(shù)據(jù)的目標(biāo)表按月分區(qū)的。插入新數(shù)據(jù)之前，跑循環(huán)刪除兩年內(nèi)的所有數(shù)據(jù)所在分區(qū)。

FUNCTION F_ETL_M1_SB_SP_JL_MX_Y(AN_YEARS NUMBER) RETURN NUMBER IS

LN_RETURN? ?NUMBER;

LVC_BBQ_MAX VARCHAR2(6);

LVC_SQLSTR??VARCHAR2(100);

BEGIN

SELECT SYSDATE INTO ldt_zxsj_q FROM DUAL;

--LN_RETURN := F_ETL_INDEX_REBUILD('M1_SB_SPXX_JL_MX_Y', 0);

LVC_SQLSTR := 'ALTER SESSION SET SQL_TRACE = TRUE';

EXECUTE IMMEDIATE LVC_SQLSTR;

LVC_SQLSTR := 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ' ||

'''yuanchh_test_131029''';

EXECUTE IMMEDIATE LVC_SQLSTR;

FOR REC_ND IN 1 .. AN_YEARS LOOP

FOR REC_YF IN 1 .. 12 LOOP

LVC_FQXH? ?:= (TO_CHAR(SYSDATE, 'YYYY') - REC_ND + 1) ||

LPAD(REC_YF, 2, '0');

LVC_SQLSTR := 'ALTER TABLE M1_SB_SPXX_JL_MX_Y TRUNCATE PARTITION ' ||

'PART' || LVC_FQXH;

EXECUTE IMMEDIATE LVC_SQLSTR;

END LOOP;

END LOOP;

--1、抽取每月發(fā)生數(shù)據(jù)

FOR REC_ND IN 1 .. AN_YEARS LOOP

INSERT

INTO M1_SB_SPXX_JL_MX_Y NOLOGGING

(BBQ,

NSRDZDAH,

HYMX_DM,

DJZCLX_DM,

SKSS_SWJG_DM,

NSR_SWJG_DM,

SE_BQ,

SE_BQLJ,

BBQ_TQ,

NSRSBH,

NSRMC,

HYMX_DM_BI,

SJLY) /* NSR_SWJG_NOW*/

SELECT /*+ PARALLEL(a,8) */

A.BBQ,

A.NSRDZDAH,

MAX(A.HYMX_DM),

MAX(A.DJZCLX_DM),

A.SKSS_SWJG_DM,

A.NSR_SWJG_DM,

SUM(A.SE) SE_BQ,

SUM(SUM(A.SE)) OVER(PARTITION BY SUBSTR(A.BBQ, 1, 4), A.NSRDZDAH, /*A.HYMX_DM, A.DJZCLX_DM,*/ A.SKSS_SWJG_DM, A.NSR_SWJG_DM ORDER BY SUBSTR(A.BBQ, 5, 6) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SE_BQLJ,

A.BBQ - '100' BBQ_TQ, --數(shù)據(jù)抽取用

MAX(A.NSRSBH),

MAX(A.NSRMC),

MAX(A.HYMX_DM_BI),

'1'

FROM M1_SB_SPXX_DH_MX_Y A --, DIM_HY B

WHERE /*A.HYMX_DM = B.HY_DM_SJ(+)

AND */A.BBQ >= (TO_CHAR(SYSDATE, 'YYYY') - REC_ND + 1) || '01'

AND A.BBQ <= (TO_CHAR(SYSDATE, 'YYYY') - REC_ND + 1) || '12'

GROUP BY A.BBQ,

A.NSRDZDAH,

--A.HYMX_DM,??--CHANGED @20131027

--A.DJZCLX_DM,

A.SKSS_SWJG_DM,

A.NSR_SWJG_DM;

COMMIT;

END LOOP;

COMMIT;

--2、按數(shù)據(jù)粒度補齊各月未發(fā)生數(shù)據(jù)的累計值(當(dāng)期值補0)，補到當(dāng)前發(fā)生數(shù)據(jù)的最大月為止

SELECT MAX(T.BBQ)

INTO LVC_BBQ_MAX

FROM M1_SB_SPXX_DH_MX_Y T

WHERE T.BBQ <= TO_CHAR(SYSDATE, 'YYYYMM');

FOR REC_ND IN 1 .. AN_YEARS LOOP

FOR REC_YF IN 1 .. 11 LOOP

INSERT /*+append*/ INTO M1_SB_SPXX_JL_MX_Y nologging

(BBQ,

NSRDZDAH,

HYMX_DM,

DJZCLX_DM,

SE_BQ,

SE_TQ,

SKSS_SWJG_DM,

NSR_SWJG_DM,

SE_BQLJ,

SE_SNTQLJ,

NSRSBH,

NSRMC,

HYMX_DM_BI,

TBZF_DY,

TBZF_LJ,

SJLX,

BBQ_TQ,

NSR_SWJG_NOW,

SJLY)

SELECT /*+full(t)*/

(TO_CHAR(SYSDATE, 'YYYY') - REC_ND + 1) ||

LPAD((REC_YF + 1), 2, '0') BBQ,

NSRDZDAH,

HYMX_DM,

DJZCLX_DM,

0 SE_BQ,

0 SE_TQ,

SKSS_SWJG_DM,

NSR_SWJG_DM,

SE_BQLJ,

0 SE_SNTQLJ,

NSRSBH,

NSRMC,

HYMX_DM_BI,

TBZF_DY,

TBZF_LJ,

SJLX,

(TO_CHAR(SYSDATE, 'YYYY') - REC_ND + 1) ||

LPAD((REC_YF + 1), 2, '0') - '100' BBQ_TQ,

NSR_SWJG_NOW,

'0' SJLY --'0'標(biāo)識為補的數(shù)據(jù)

FROM M1_SB_SPXX_JL_MX_Y T

WHERE T.BBQ = (TO_CHAR(SYSDATE, 'YYYY') - REC_ND + 1) ||

LPAD(REC_YF, 2, '0')

AND T.BBQ <= LVC_BBQ_MAX

AND NOT EXISTS

(SELECT /*+full(b)*/1

FROM M1_SB_SPXX_JL_MX_Y B

WHERE B.NSRDZDAH = T.NSRDZDAH

--AND B.HYMX_DM = T.HYMX_DM??--CHANGED @20131027

--AND B.DJZCLX_DM = T.DJZCLX_DM

AND B.SKSS_SWJG_DM = T.SKSS_SWJG_DM

AND B.NSR_SWJG_DM = T.NSR_SWJG_DM

AND B.BBQ = (TO_CHAR(SYSDATE, 'YYYY') - REC_ND + 1) ||

LPAD((REC_YF + 1), 2, '0'));

COMMIT;

END LOOP;

END LOOP;

PKG_GY.P_GY_WRITE_ETL_LOG(lvc_etl,

'F_ETL_M1_SB_SP_JL_MX_Y',

lvc_zxjg,

ldt_zxsj_q,

sysdate,

'01',

null);

RETURN 0;

EXCEPTION

WHEN OTHERS THEN

PKG_GY.P_GY_WRITE_ETL_LOG(lvc_etl,

'F_ETL_M1_SB_SP_JL_MX_Y',

lvc_zxjg_err,

ldt_zxsj_q,

sysdate,

'01',

substr(TO_CHAR(SQLCODE) || SQLERRM, 1, 500));

RETURN 1;

END F_ETL_M1_SB_SP_JL_MX_Y;

總結(jié)

以上是生活随笔為你收集整理的Oracle如何手工执行job,求助高手，JOB执行和手工执行执行结果不同，JOB执行不出来，手工却没问题...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。