This discussion is archived
5 Replies Latest reply: Nov 13, 2012 8:20 PM by 894936 RSS

PROCEDURE

894936 Newbie
Currently Being Moderated
please do help me:am i following the correct logic or not.

REQUEST: how to move data from A TO B USING PROCEDURES and i need to get the insert_count,update_count,reject_count,oracle_exception_count...........

conditions: 1----already existing record means it shd be updated
else
2----new record means it shd be inserted.
3-----i need to commit every after 500th record.
here iam taking one procedure where it consists of two main tables

SELECT * FROM IVRESS_TEMP_MP_SCHEDULE_DL;---------------A TABLE
SELECT * FROM IVRESS_INTR_MP_SCHEDULE_DL;----------------B TABLE.

So when i run the procedure the data from A Table shd be moved to B Table based upon the conditions..............
and i need to catch exceptions in vress_common_pkg.oracle_error_info---------------(SELECT * FROM VRESS_ORACLE_EXCEPTION :exception table)
and reject data shd be moved to vress_common_pkg.log_reject_info----------------(select * from VRESS_DATA_REJECT_INFO: reject table)

Reject info table and oracle exception tables are written in common package.
 CREATE OR REPLACE PROCEDURE mp_schedule_temp_intrm_dl IS
  /*****************************************************************************************************************
      Package Name              : vress_mp_schedule_dl_pkg
      Program Name              : mp_schedule_temp_intrm_dl
      Program Description       : This processing load Temp information into Intermediate table and maintaing the log_reject info.
      Created by                : Mamatha 
      Created date              : 09-NOV-2011
      Input  Parameters         : None
      Output Parameters         : None
      Return Parameters         : None
  *******************************************************************************************************************/
  CURSOR c_temp_intrm IS
    SELECT error,
           season,
           generation,
           area,
           sku_type,
           sku_name,
           product_name,
           config_factory,
           to_date(TRIM(mp_avl_date)) mp_avl_date,
           to_date(TRIM(mp_assy_date)) mp_assy_date,
           to_date(TRIM(mp_dead_line_date)) mp_dead_line_date,
           to_date(TRIM(go_nogo_avl_date)) go_nogo_avl_date,
           to_date(TRIM(ex_factory_date)) ex_factory_date,
           initial_forcst_pos,
           transport_method,
           TO_DATE(TRIM(act_go_nogo_date)) act_go_nogo_date,
           remarks
      FROM ivress_temp_mp_schedule_dl
    MINUS
    SELECT error,
           season,
           generation,
           area,
           sku_type,
           sku_name,
           product_name,
           config_factory,
           to_date(TRIM(mp_avl_date)) mp_avl_date,
           to_date(TRIM(mp_assy_date)) mp_assy_date,
           to_date(TRIM(mp_dead_line_date)) mp_dead_line_date,
           to_date(TRIM(go_nogo_avl_date)) go_nogo_avl_date,
           to_date(TRIM(ex_factory_date)) ex_factory_date,
           initial_forcst_pos,
           transport_method,
           TO_DATE(TRIM(act_go_nogo_date)) act_go_nogo_date,
           remarks
      FROM ivress_intr_mp_schedule_dl;

  lv_oracle_error_code NUMBER;
  lv_oracle_error_msg  VARCHAR2(2000);
  lv_user              VARCHAR2(50);
  ln_update_count      NUMBER := 0;
  ln_add_tot           NUMBER := 0;
  ln_count             NUMBER;
  last_seq_no          NUMBER;
  -- last_processed_job_end_date DATE;
BEGIN

  SELECT MAX(seq_no)
    INTO last_seq_no
    FROM vress_plan_job_result
   WHERE job_code = 'VRS0406000';

  DBMS_OUTPUT.PUT_LINE('CHECKING WHETHER RECORD EXISTS OR NOT');
  DBMS_OUTPUT.PUT_LINE('                                     ');

  FOR c_temp_intr_ld IN c_temp_intrm LOOP
    BEGIN
      SELECT COUNT(1)
        INTO ln_count
        FROM ivress_intr_mp_schedule_dl
       WHERE generation = c_temp_intr_ld.generation
         AND sku_type = c_temp_intr_ld.sku_type
         AND sku_name = c_temp_intr_ld.sku_name;
    
      IF ln_count >= 1 THEN
      
        BEGIN
          ----------------------------------CHECKING WHETHER RECORD EXISTS OR NOT------------------------
          UPDATE ivress_intr_mp_schedule_dl
             SET error              = c_temp_intr_ld.error,
                 season             = c_temp_intr_ld.season,
                 area               = c_temp_intr_ld.area,
                 product_name       = c_temp_intr_ld.product_name,
                 config_factory     = c_temp_intr_ld.config_factory,
                 mp_avl_date        = c_temp_intr_ld.mp_avl_date,
                 mp_assy_date       = c_temp_intr_ld.mp_assy_date,
                 mp_dead_line_date  = c_temp_intr_ld.mp_dead_line_date,
                 go_nogo_avl_date   = c_temp_intr_ld.go_nogo_avl_date,
                 ex_factory_date    = c_temp_intr_ld.ex_factory_date,
                 initial_forcst_pos = c_temp_intr_ld.initial_forcst_pos,
                 transport_method   = c_temp_intr_ld.transport_method,
                 act_go_nogo_date   = c_temp_intr_ld.act_go_nogo_date,
                 remarks            = c_temp_intr_ld.remarks,
                 vress_updated_by   = USER,
                 vress_updated_dt   = SYSDATE
           WHERE generation = c_temp_intr_ld.generation
             AND sku_type = c_temp_intr_ld.sku_type
             AND sku_name = c_temp_intr_ld.sku_name;
          ln_update_count := ln_update_count + SQL%ROWCOUNT; -------------------update rowcount
          IF MOD(ln_update_count, 500) = 0 THEN
            COMMIT;
          END IF;
         
          DBMS_OUTPUT.PUT_LINE('---------1');
        
        EXCEPTION
          WHEN OTHERS THEN
            --Capture exception while trying to updating records from temp to  intrm table
            lv_oracle_error_code := SUBSTR(SQLCODE, 1, 100);
            lv_oracle_error_msg  := SUBSTR(SQLERRM, 1, 255);
            ----------------oracle error info
            vress_common_pkg.oracle_error_info(SQLCODE,
                                               SQLERRM,
                                               'VRESS_MP_SCHEDULE_DL_PKG.mp_schedule_temp_intrm_dl',
                                               user);
          
            ------------------reject info
            vress_common_pkg.log_reject_info(last_seq_no, -----vress_plan_job_result seq_no
                                             'mp_schedule_temp_intrm_dl', -----procedure name
                                             'Rejection Records', -----process type
                                             'intrm data load - rejected records', -----process name
                                             c_temp_intr_ld.generation, -----KEY_DATA_01
                                             c_temp_intr_ld.sku_type, -----KEY_DATA_02
                                             c_temp_intr_ld.sku_name, -----KEY_DATA_03
                                             null, -----KEY_DATA_04
                                             null, -----KEY_DATA_05
                                             null, -----KEY_DATA_06
                                             null, -----KEY_DATA_07
                                             null, -----KEY_DATA_08
                                             'Records rejected while inserting into ivress_intr_mp_schedule_dl', --error_msg
                                             SQLERRM, -------oracle_error_msg
                                             'Check for the oracle exception in error info table', --action
                                             0, -------------------severity 
                                             'records rejected while updating into intermediate table.', ----------remarks
                                             user); ------------create user
        END;
        DBMS_OUTPUT.PUT_LINE('NO OF RECORDS UPDATED................  ' ||
                             ln_update_count);
      
      ELSE
        BEGIN
          INSERT INTO ivress_intr_mp_schedule_dl
            (seq_no,
             error,
             season,
             area,
             product_name,
             config_factory,
             mp_avl_date,
             mp_assy_date,
             mp_dead_line_date,
             go_nogo_avl_date,
             ex_factory_date,
             initial_forcst_pos,
             transport_method,
             act_go_nogo_date,
             remarks,
             vress_created_by,
             vress_created_dt)
          VALUES
            (ivress_mp_schedule_intr_seq.nextval,
             c_temp_intr_ld.error,
             c_temp_intr_ld.season,
             c_temp_intr_ld.area,
             c_temp_intr_ld.product_name,
             c_temp_intr_ld.config_factory,
             c_temp_intr_ld.mp_avl_date,
             c_temp_intr_ld.mp_assy_date,
             c_temp_intr_ld.mp_dead_line_date,
             c_temp_intr_ld.go_nogo_avl_date,
             c_temp_intr_ld.ex_factory_date,
             c_temp_intr_ld.initial_forcst_pos,
             c_temp_intr_ld.transport_method,
             c_temp_intr_ld.act_go_nogo_date,
             c_temp_intr_ld.remarks,
             USER,
             SYSDATE);
          ln_add_tot := ln_add_tot + SQL%ROWCOUNT; ---------------------------------------insert rowcount
          DBMS_OUTPUT.PUT_LINE('NO OF RECORDS INSERTED...............  ' ||
                               SQL%ROWCOUNT);
        
          IF MOD(ln_add_tot, 500) = 0 THEN
            COMMIT;
          END IF;
        
        EXCEPTION
          WHEN OTHERS THEN
            lv_oracle_error_code := SUBSTR(SQLCODE, 1, 100);
            lv_oracle_error_msg  := SUBSTR(SQLERRM, 1, 255);
            vress_common_pkg.oracle_error_info(SQLCODE,
                                               SQLERRM,
                                               'VRESS_MP_SCHEDULE_DL_PKG.mp_schedule_temp_intrm_dl inner loop',
                                               user);
        END;
      END IF;
    
      --  COMMIT;
    
    EXCEPTION
      WHEN OTHERS THEN
        --Capture exception while trying to insert records from temp to intrm table
        lv_oracle_error_code := SUBSTR(SQLCODE, 1, 100);
        lv_oracle_error_msg  := SUBSTR(SQLERRM, 1, 255);
        vress_common_pkg.oracle_error_info(SQLCODE,
                                           SQLERRM,
                                           'VRESS_MP_SCHEDULE_DL_PKG.mp_schedule_temp_intrm_dl outer loop',
                                           user);
        vress_common_pkg.log_reject_info(last_seq_no, -----vress_plan_job_result seq_no
                                         'mp_schedule_temp_intrm_dl', -----procedure name
                                         'Rejection Records', -----process type
                                         'intrm data load - rejected records', -----process name
                                         c_temp_intr_ld.generation, -----KEY_DATA_01
                                         c_temp_intr_ld.sku_type, -----KEY_DATA_02
                                         c_temp_intr_ld.sku_name, -----KEY_DATA_03
                                         null, -----KEY_DATA_04
                                         null, -----KEY_DATA_05
                                         null, -----KEY_DATA_06
                                         null, -----KEY_DATA_07
                                         null, -----KEY_DATA_08
                                         'Records rejected while inserting into ivress_intr_mp_schedule_dl', --error_msg
                                         SQLERRM, -------oracle_error_msg
                                         'Check for the oracle exception in error info table', --action
                                         0, -------------------severity 
                                         'records rejected while inserting into intermediate table.', -----------remarks
                                         user); ---------create user
    END;
  END LOOP;

  /**************************
  ------------------------EXECUTION OF PROCEDURE---------------------
  BEGIN
  mp_schedule_temp_intrm_dl;
  END;
  
  ------------------------SELECTING FROM TEMP-------------------------
  SELECT * FROM IVRESS_TEMP_MP_SCHEDULE_DL;
  
  ------------------------SELECTING FROM INTERM-----------------------
  SELECT * FROM IVRESS_INTR_MP_SCHEDULE_DL;
Edited by: 891933 on Nov 10, 2011 9:12 PM
  • 1. Re: PROCEDURE
    bpat Journeyer
    Currently Being Moderated
    891933 wrote:
    {   please do help me:am i following the correct logic or not.
    The current PLSQL logic will be very slow.
    Instead use MERGE to do an UPDATE or INSERT.

    Look at the documentation(based on your database version) for more details.
  • 2. Re: PROCEDURE
    sb92075 Guru
    Currently Being Moderated
    3-----i need to commit every after 500th record.
    This is a excellent way to throw ORA-01555; Snapshot To Old errors!
  • 3. can we use merge in plsql?
    894936 Newbie
    Currently Being Moderated
    hI

    How can we use merge in plsql?

    is the above code in procedure does the same by below?

    Please help...

    many thanks
    MERGE 
    INTO ivress_intr_mp_schedule_dl tgt
    USING ivress_temp_mp_schedule_dl src
    ON (ivress_temp_mp_schedule_dl.generation = ivress_intr_mp_schedule_dl.generation
         AND     ivress_temp_mp_schedule_dl.sku_type = ivress_intr_mp_schedule_dl.sku_type
        AND ivress_temp_mp_schedule_dl = ivress_intr_mp_schedule_dl.sku_name
         )
    WHEN MATCHED THEN
    UPDATE SET
                         ivress_intr_mp_schedule_dl.error              = ivress_temp_mp_schedule_dl.error,
                     ivress_intr_mp_schedule_dl.season             = ivress_temp_mp_schedule_dl.season,
                     ivress_intr_mp_schedule_dl.area               = ivress_temp_mp_schedule_dl.area,
                     ivress_intr_mp_schedule_dl.product_name       = ivress_temp_mp_schedule_dl.product_name,
                     ivress_intr_mp_schedule_dl.config_factory     = ivress_temp_mp_schedule_dl.config_factory,
                     ivress_intr_mp_schedule_dl.mp_avl_date        = ivress_temp_mp_schedule_dl.mp_avl_date,
                     ivress_intr_mp_schedule_dl.mp_assy_date       = ivress_temp_mp_schedule_dl.mp_assy_date,
                     ivress_intr_mp_schedule_dl.mp_dead_line_date  = ivress_temp_mp_schedule_dl.mp_dead_line_date,
                     ivress_intr_mp_schedule_dl.go_nogo_avl_date   = ivress_temp_mp_schedule_dl.go_nogo_avl_date,
                     ivress_intr_mp_schedule_dl.ex_factory_date    = ivress_temp_mp_schedule_dl.ex_factory_date,
                     ivress_intr_mp_schedule_dl.initial_forcst_pos = ivress_temp_mp_schedule_dl.initial_forcst_pos,
                     ivress_intr_mp_schedule_dl.transport_method   = ivress_temp_mp_schedule_dl.transport_method,
                     ivress_intr_mp_schedule_dl.act_go_nogo_date   = ivress_temp_mp_schedule_dl.act_go_nogo_date,
                     ivress_intr_mp_schedule_dl.remarks            = ivress_temp_mp_schedule_dl.remarks,
                     ivress_intr_mp_schedule_dl.vress_updated_by   = USER,
                     ivress_intr_mp_schedule_dl.vress_updated_dt   = SYSDATE
    
    WHEN NOT MATCHED  THEN     
    INSERT INTO ivress_intr_mp_schedule_dl
                (seq_no,
                 error,
                 season,
                 area,
                 product_name,
                 config_factory,
                 mp_avl_date,
                 mp_assy_date,
                 mp_dead_line_date,
                 go_nogo_avl_date,
                 ex_factory_date,
                 initial_forcst_pos,
                 transport_method,
                 act_go_nogo_date,
                 remarks,
                 vress_created_by,
                 vress_created_dt)
              VALUES
                (ivress_mp_schedule_intr_seq.nextval,
                 ivress_temp_mp_schedule_dl.error,
                 ivress_temp_mp_schedule_dl.season,
                 ivress_temp_mp_schedule_dl.area,
                 ivress_temp_mp_schedule_dl.product_name,
                 ivress_temp_mp_schedule_dl.config_factory,
                 ivress_temp_mp_schedule_dl.mp_avl_date,
                 ivress_temp_mp_schedule_dl.mp_assy_date,
                 ivress_temp_mp_schedule_dl.mp_dead_line_date,
                 ivress_temp_mp_schedule_dl.go_nogo_avl_date,
                 ivress_temp_mp_schedule_dl.ex_factory_date,
                 ivress_temp_mp_schedule_dl.initial_forcst_pos,
                 ivress_temp_mp_schedule_dl.transport_method,
                 ivress_temp_mp_schedule_dl.act_go_nogo_date,
                 ivress_temp_mp_schedule_dl.remarks,
                 USER,
                 SYSDATE);
                     
         
         
  • 4. Re: can we use merge in plsql?
    bpat Journeyer
    Currently Being Moderated
    I haven't gone through the code completely.
    But you are in right direction. Check it once and let us know if it works or not.
  • 5. Re: PROCEDURE
    894936 Newbie
    Currently Being Moderated
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points