1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-20001: AWP00170_IMC_SNAP Stored Procedure Error

Discussion in 'SQL PL/SQL' started by yashk111, Dec 9, 2009.

  1. yashk111

    yashk111 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    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

    Code (SQL):

    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. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Stored Procedure Error

    Display v_err_msg or check the v_err_msg in tabke dwt10440_sp_log_detail for better tracking of error message.

    Also you can assign one transaction label before each DML and display that variable on getting the Exception, so that you can spot easily where exactly the error happened.
     
  3. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai
    Re: Stored Procedure Error

    Also, I would suggest to take out the "WHEN OTHERS" Exception Clause and re-run the procedure.

    That should be hiding the place where the actual errors is occuring!
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: Stored Procedure Error

    This is not at all an ideal solution. But could be done for the testing purpose.
     
  5. Sikkandar.S.P

    Sikkandar.S.P Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Chennai
    Re: Stored Procedure Error

    Yes, I have suggested that solution to first find the error.
     
  6. yashk111

    yashk111 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I copied the Insert and select statemnts with where condiitons and ran the code out of stored procedure to check the error and the code looks like this

    Code (SQL):

    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, 0)
                             )) 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');
     
    Error Report:

    SQL Error: ORA-12838: cannot read/modify an object after modifying it in parallel
    12838. 00000 - "cannot read/modify an object after modifying it in parallel"
    *Cause: Within the same transaction, an attempt was made to add read or
    modification statements on a table after it had been modified in parallel
    or with direct load. This is not permitted.
    *Action: Rewrite the transaction, or break it up into two transactions
    one containing the initial modification and the second containing the
    parallel modification operation.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Have you not queried dwt10440_sp_log_detail to see why this ended so abruptly?
     
  8. yashk111

    yashk111 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I have seen in the LOG table.
    But in the LOG table we canonly know status no error message wil be given.
    In the log table it says Ended with errors
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    When you exactly got the error when you tried to execute the code outside the procedure ?
    During insert or when yiu tried to select after insert ?
    Show us the SQLPLUS output.