December 8, 2009 at 2:52 pm
As i told i am modifying a Stored Procedure.
I have added a new column to target table and now i have to insert values into that new column.
I modified the code but getting some errors. I tried for an hour for finding the error. The code is compoling successfully, but while running i am getting errors.
This is the code
create or replace
PROCEDURE "AWP00170_IMC_SNAP"
AS
v_start_dt date;
v_sp_name varchar2(30);
v_count number;
v_err_msg varchar2(200);
BEGIN
-- store in variable to update the same row later
v_start_dt:=sysdate;
v_sp_name:='AWP00170_IMC_SNAP';
insert into dwt10430_sp_log
values(v_sp_name,v_start_dt,null,'started');
commit;
EXECUTE IMMEDIATE 'Alter Session Enable Parallel DML';
INSERT /*+ APPEND*/ INTO DWSGDW01.DWT00050_IMC_CCYYMM_FACT (
SNAP_MO_YR_KEY_NO
, IMC_KEY_NO
, IMC_AFF_KEY_NO
, IMC_CNTRY_KEY_NO
, IMC_DERIVED_NM
, IMC_ST_PROV_CD
, IMC_POSTL_CD
, IMC_TYPE_CD
, ENGAGE_FLG
, IMC_CURR_SEG_KEY_NO
, IMC_APPL_DT_KEY_NO
, LAST_RENEW_DT_KEY_NO
, IMC_DLETE_DT_KEY_NO
, SPON_IMC_KEY_NO
, IMC_CLASS_CD
, IMC_HIGH_AWD_KEY_NO
, IMC_ACHV_AWD_KEY_NO
, IMC_CURR_QUAL_AWD_KEY_NO
, IMC_ISPON_FLG
, IMC_ISPON_AFF_ID
, IMC_LANG_CD
, IMC_ISPON_IMC_KEY_NO
, IMC_EXPIRE_DT_KEY_NO
, FRONTLINE_CNT
, LOM_DY_NO
, LOA_KEY_NO
, GLOBL_IMC_TYPE_KEY_NO
, POSTL_CD_KEY_NO
, STATUS_KEY_NO
, LAST_ORD_OIMC_DT_KEY_NO
, LAST_ORD_VIMC_DT_KEY_NO
, LAST_SPON_DT_KEY_NO
, LAST_UPDT_DT_KEY_NO
, LAST_LOAD_DT_KEY_NO
, LAST_UPDT_PROC_KEY_NO
, LAST_LOAD_PROC_KEY_NO
, LOS_GRP_KEY_NO
, contb_distb_flg)
SELECT /*+ PARALLEL (IMC 8)*/
CAST(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') as NUMERIC (10,0))AS SNAP_MO_YR_KEY_NO
, IMC.IMC_KEY_NO
, IMC.IMC_AFF_KEY_NO
, IMC.IMC_CNTRY_KEY_NO
, IMC.IMC_NAME
, NVL(CNTAC.PRM_STATE,CNTAC.PRM_PROV)
, CNTAC.PRM_POST_CODE
, IMC.IMC_TYPE_CD
, CASE WHEN trim(DERV.ENGAGE_FLG) = 'Y' THEN 1 else 0 END
, DERV.IMC_CURR_SEG_KEY_NO
, IMC.APPL_DT_KEY_NO
, DERV.RENEW_DT_KEY_NO
, IMC.IMC_DLETE_DT_KEY_NO
, IMC.SPON_IMC_KEY_NO
, IMC.IMC_CLASS_CD
, DERV.IMC_HIGH_AWD_KEY_NO
, DERV.IMC_ACHV_AWD_KEY_NO
, DERV.IMC_CURR_QUAL_AWD_KEY_NO
, IMC.ISPON_FLG
, IMC.ISPON_AFF_ID
, CNTAC.PRM_LANG_CD
, IMC.ISPON_IMC_KEY_NO
, DERV.EXPIRE_DT_KEY_NO
, DERV.FRONTLN_DISTB_CNT --FRONTLINE_CNT
, CAST(case
when (TO_DATE(TO_CHAR(last_day(ADD_MONTHS(SYSDATE,-1)),'YYYYMMDD'),'YYYYMMDD') - TO_DATE (IMC.APPL_DT_KEY_NO,'YYYYMMDD')) + 1 < 1 then 1
else (TO_DATE(TO_CHAR(last_day(ADD_MONTHS(SYSDATE,-1)),'YYYYMMDD'),'YYYYMMDD') - TO_DATE (IMC.APPL_DT_KEY_NO,'YYYYMMDD')) + 1
end AS NUMERIC (10,0)) as LOM_DY_NO
, IMC.IMC_LOA_KEY_NO
, IMC.GLOBL_IMC_TYPE_KEY_NO
, CNTAC.POSTL_CD_KEY_NO
, IMC.STATUS_KEY_NO
, DERV.LAST_ORD_OIMC_DT_KEY_NO
, DERV.LAST_ORD_VIMC_DT_KEY_NO
, DERV.LAST_SPON_DT_KEY_NO
, CAST(TO_CHAR(SYSDATE, 'YYYYMMDD')as numeric(10,0)) AS LAST_UPDT_DT_KEY_NO
, CAST(TO_CHAR(SYSDATE, 'YYYYMMDD')as numeric(10,0)) as LAST_LOAD_DT_KEY_NO
, CAST(-1 AS NUMERIC(10,0)) as LAST_UPDT_PROC_KEY_NO
, CAST(-1 AS NUMERIC(10,0)) as LAST_LOAD_PROC_KEY_NO
, IMC.LOS_GRP_KEY_NO
, CASE WHEN DT.DT_KEY_NO IS NULL THEN 'N'
ELSE 'Y' END AS contb_distb_flg
FROM DWSGDW01.DWT00740_IMC_DIM IMC, DWV00002_BUS_STAT_DIM BUS, DWT00410_IMC_DERV_DIM DERV, DWSGDW01.DWT00750_IMC_CNTAC_DIM CNTAC
, (SELECT * FROM DWSGDW01.MV00002_DT_DIM WHERE MO_YR_KEY_NO = CAST (TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM')as NUMERIC (10,1))) DT
where DERV.IMC_KEY_NO = IMC.IMC_KEY_NO and BUS.STATUS_KEY_NO = IMC.STATUS_KEY_NO AND CNTAC.IMC_KEY_NO = IMC.IMC_KEY_NO
and ((APPL_DT_KEY_NO < to_char(last_day(SYSDATE),'YYYYMMDD')
AND (IMC_DLETE_DT_KEY_NO = 19000101
OR TO_CHAR(ADD_MONTHS(TO_DATE(IMC_DLETE_DT_KEY_NO, 'YYYYMMDD'),12),'YYYY-MM-DD') > to_char(last_day(SYSDATE),'YYYY-MM-DD') ))
OR BUS.GLOBL_BUS_STAT_CD ='ACTIVE');
commit;
update dwt10430_sp_log
set (end_dt, status) = (select sysdate,'finished' from dual)
where sp_name=v_sp_name
and start_dt=v_start_dt;
commit;
EXCEPTION
WHEN OTHERS THEN
v_err_msg := substr(SQLERRM, 1, 200);
insert into dwt10440_sp_log_detail
values(v_sp_name,sysdate,v_err_msg);
insert into dwt10440_sp_log_detail
values(v_sp_name,sysdate,DBMS_UTILITY.format_error_backtrace);
update dwt10430_sp_log
set (end_dt, status) = (select sysdate,'ended with errors' from dual)
where sp_name=v_sp_name
and start_dt=v_start_dt;
commit;
-- This is line 144 as per my code (i deleted some comments)
RAISE_APPLICATION_ERROR(-20001, v_sp_name);
end;
Error Message
Connecting to the database Dev-GDW.
ORA-20001: AWP00170_IMC_SNAP
ORA-06512: at "DWSGDW01.AWP00170_IMC_SNAP", line 144
ORA-06512: at line 2
Process exited.
Disconnecting from the database Dev-GDW.
Can anyone say what error is?
I even ran the old code when no changes are made, then it ran successfully.
December 8, 2009 at 3:01 pm
That's not a SQL Server procedure. That's Oracle.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 3:09 pm
Oracle? :w00t:
STONE THE HERETIC !!!
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply