Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Unanswered: Stored Procedure Error

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    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);
    While debugging eliminate whole exception handler above.
    use sqlplus to compile & run procedure and PASTE whole result back here.

    If you will help me by following directions, then I'll debug your code for you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Isn't formatted code easier to read?
    Code:
    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');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2009
    Posts
    34
    Quote Originally Posted by anacedent View Post
    Code:
    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);
    While debugging eliminate whole exception handler above.
    use sqlplus to compile & run procedure and PASTE whole result back here.

    If you will help me by following directions, then I'll debug your code for you.
    I want to do what you said. I even tried to do so.
    Unfortunately it is saying i have insufficient privileges.

    Error

    Connecting to the database Dev-GDW.
    Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.11.11.23', '2212' )
    ORA-01031: insufficient privileges
    ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
    ORA-06512: at line 1
    This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
    Process exited.
    Disconnecting from the database Dev-GDW.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to do what you said. I even tried to do so.
    >Unfortunately it is saying i have insufficient privileges.
    YES, there is an error, which is why it fails to run.

    Now using sqlplus compile the procedure & invoke it in the same session.
    CUT the whole session & PASTE results back here.
    I need to see where sqlplus reports the error.
    Why do you refuse to do as requested?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2009
    Posts
    34
    I dont have SQLPLUS in my laptop.
    This is my office laptop, so i cannot download and install it.

    I did this in SQL developer.
    As you said removed exception code and compiled it and it compiled successfully

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Connecting to the database Dev-GDW.
    >Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
    >Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.11.11.23', '2212' )
    >ORA-01031: insufficient privileges
    >ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
    >ORA-06512: at line 1
    >This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
    >Process exited.
    >Disconnecting from the database Dev-GDW.

    Nothing in what you originally posted included neither the ALTER SESSION above nor DBMS_DEBUG_JDWP.
    I have no idea what you are doing & suspect you don't either.
    Since I can not debug code I can not see, I'll let you enjoy your mystery without any additional interruptions from me.

    Have A Nice Day.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2009
    Posts
    34
    I did as you said by taking out the Exception
    And i gave you the error i got.
    If it is not sufficient let me know what to do. (I need it very badly)

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If it is not sufficient let me know what to do.
    I do not know what you did to produce the errors.
    I do not know how or why the errors occured.

    Since I can not debug code I can not see, You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Nov 2009
    Posts
    34
    Hey this time i removed the Exception code and ran the code
    i got these errors

    Connecting to the database Dev-GDW.
    ORA-12801: error signaled in parallel query server P006, instance lnax33d:dwbdv1012 (2)
    ORA-00001: unique constraint (DWSGDW01.DWI00050CC_SNAP_MO_YR_K_IMC_K) violated
    ORA-06512: at "DWSGDW01.AWP00170_IMC_SNAP", line 27
    ORA-06512: at line 2
    Process exited.
    Disconnecting from the database Dev-GDW.

    Can you help me in any way

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    00001, 00000, "unique constraint (%s.%s) violated"
    // *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
    //         For Trusted Oracle configured in DBMS MAC mode, you may see
    //         this message if a duplicate entry exists at a different level.
    // *Action: Either remove the unique restriction or do not insert the key.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •