5 Replies Latest reply: Nov 13, 2012 10:20 PM by 894936 RSS

    PROCEDURE

    894936
      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
          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
            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
              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
                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
                  Thanks