title: PL/SQL : Need to compare data for every field in a table in plsql
body: <p>I need to create a procedure which will take collection as an input and compare the data with staging table data row by row for every field (approx 50 columns).</p>
  
  <p>Business logic :</p>
  
  <ol>
  <li><p>whenever a staging table column value will mismatch with the corresponding collection variable value then i need to update <strong>'FAIL'</strong> into staging table STATUS column and reason into REASON column for that row.</p></li>
  <li><p>If matched then need to update <strong>'SUCCESS'</strong> in STATUS column.</p></li>
  </ol>
  
  <p>Payload will be approx 500 rows in each call.</p>
  
  <p>I have created below sample script:</p>
  
  <p><strong>PKG Specification :</strong> </p>
  
  <pre><code>CREATE OR REPLACE
  PACKAGE process_data
  IS
  TYPE pass_data_rec
  IS
    record
    (
      p_eid employee.eid%type,
      p_ename employee.ename%type,
      p_salary employee.salary%type,
      p_dept employee.dept%type 
    );
  
  type p_data_tab IS TABLE OF pass_data_rec INDEX BY binary_integer;
  PROCEDURE comp_data(inpt_data IN p_data_tab);
  END;
  </code></pre>
  
  <p><strong>PKG Body:</strong></p>
  
  <pre><code>  CREATE OR REPLACE
      PACKAGE body process_data
      IS
      PROCEDURE comp_data (inpt_data IN p_data_tab)
      IS
        status VARCHAR2(10);
        reason VARCHAR2(1000);
        cnt1   NUMBER;
        v_eid employee_copy.eid%type;
        v_ename employee_copy.ename%type;
      BEGIN
        FOR i IN 1..inpt_data.count
        LOOP
          SELECT ec1.eid,ec1.ename,COUNT(*) over () INTO v_eid,v_ename,cnt1
          FROM employee_copy ec1
          WHERE ec1.eid = inpt_data(i).p_eid;
          IF cnt1 &gt; 0 THEN
            IF (v_eid=inpt_data(i).p_eid AND v_ename = inpt_data(i).p_ename) THEN
              UPDATE employee_copy SET status = 'SUCCESS' WHERE eid = inpt_data(i).p_eid;
            ELSE
              UPDATE employee_copy SET status = 'FAIL' WHERE eid = inpt_data(i).p_eid;
            END IF;
          ELSE
            NULL;
          END IF;
        END LOOP;
        COMMIT;
        status :='success';
      EXCEPTION
      WHEN OTHERS THEN
        status:= 'fail';
        --reason:=sqlerrm;
      END;
      END;
  </code></pre>
  
  <p>But in this approach i have below mentioned issues.</p>
  
  <ol>
  <li>Need to declare all local variables for each column value.</li>
  <li><p>Need to compare all variable data using 'and' operator. Not sure whether it is correct way or not because if there are 50 columns then if condition will become very heavy.</p>
  
  <p><code>IF (v_eid=inpt_data(i).p_eid AND v_ename = inpt_data(i).p_ename) THEN</code></p></li>
  <li><p>Need to update REASON column when any column data mismatched (first mismatched column name) for that row, in this approach i am not able to achieve.</p></li>
  </ol>
  
  <p>Please suggest any other good way to achieve this requirement.</p>
  
  <p><strong>Edit :</strong> </p>
  
  <p>There is only one table at my end i.e target table. Input will come from any other source as collection object.</p>