title: PL/SQL exception handling
body: <p>I am fairly new to pl/sql programming and have been trying to solve this from past 2 hours</p>
  
  <p>This is my code</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE xx_upd_new_code AS   
  
    CURSOR c1 IS
      SELECT
        eite.t_ep_item_ep_id,
        bite.main_item
      FROM bidw.item@demantra bite,
        t_ep_item eite
      WHERE bite.item_code = eite.item
            AND bite.main_item = bite.old_code
            AND bite.current_flag = 1
            AND bite.main_item IS NOT NULL;
  
    a1 c1%ROWTYPE;
  
    BEGIN
      xx_dbex('Starts.', 'xx_upd_new_code', 'XX');
  
      OPEN c1;
      LOOP
        FETCH c1 INTO a1;
        EXIT WHEN c1%NOTFOUND;
  
        xx_dbex('Item: ' || a1.main_item, 'xx_upd_new_code', 'XX');
  
        UPDATE t_ep_item
        SET item = a1.main_item
        WHERE t_ep_item_ep_id = a1.t_ep_item_ep_id;
  
        COMMIT;
  
        xx_dbex('Ends.', 'xx_upd_new_code', 'XX');
      END LOOP;
  
      EXCEPTION 
      WHEN OTHERS THEN xx_dbex('Error.', 'xx_upd_new_code', 'XX');   
    END xx_upd_new_code;
  </code></pre>
  
  <p>Whenever it gets an error the procedure ends but I want it to move to next item.
  xx_debex is a procedure which stores the logs.</p>
  
  <p>I have been googling, I found out that i can write two exceptions &amp; can raise an exception if a condition is not satisfied.I tried this but it throws an error
  identifier a must be declared
  identifier item must be declared</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE xx_upd_new_code AS   
  
    CURSOR c1 IS
      SELECT
        eite.t_ep_item_ep_id,
        bite.main_item
      FROM bidw.item@demantra bite,
        t_ep_item eite,
        items ite
      WHERE bite.item_code = eite.item
            AND bite.main_item = bite.old_code
            AND bite.current_flag = 1              
            AND bite.main_item IS NOT NULL;
  
    a1 c1%ROWTYPE;
  
    BEGIN
      xx_dbex('Starts.', 'xx_upd_new_code', 'XX');
  
      OPEN c1;
      LOOP
        FETCH c1 INTO a1;
        EXIT WHEN c1%NOTFOUND;
  
        IF item != a1.main_item THEN
          xx_dbex('Item: ' || a1.main_item, 'xx_upd_new_code', 'XX');
  
          UPDATE t_ep_item
          SET item = a1.main_item
          WHERE t_ep_item_ep_id = a1.t_ep_item_ep_id;
          COMMIT;
  
          xx_dbex('Ends.', 'xx_upd_new_code', 'XX');          
        ELSE
          RAISE a;
        END IF;
  
      END LOOP;
  
      EXCEPTION 
        WHEN a THEN xx_dbex('Error.', 'xx_upd_new_code', 'XX');
        WHEN OTHERS THEN NULL;   
    END xx_upd_new_code;
  </code></pre>
  
  <p>Also,somewhere it was writen i can write it in two different blocks</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE xx_upd_new_code AS
  
    CURSOR c1 IS
      SELECT
        eite.t_ep_item_ep_id,
        bite.main_item
      FROM bidw.item@demantra bite,
        t_ep_item eite
      WHERE bite.item_code = eite.item
            AND bite.main_item = bite.old_code
            AND bite.current_flag = 1
            AND bite.main_item IS NOT NULL;
  
    a1 c1%ROWTYPE;
  
    BEGIN
      xx_dbex('Starts.', 'xx_upd_new_code', 'XX');
  
      BEGIN
  
        EXCEPTION
        WHEN OTHERS THEN xx_dbex('Error', 'xx_upd_new_code', 'XX');
      END;
  
      OPEN c1;
      LOOP
        FETCH c1 INTO a1;
        EXIT WHEN c1%NOTFOUND;
  
        xx_dbex('Item: ' || a1.main_item, 'xx_upd_new_code', 'XX');
        UPDATE t_ep_item
        SET item = a1.main_item
        WHERE t_ep_item_ep_id = a1.t_ep_item_ep_id;
        COMMIT;
  
      END LOOP;
  
      EXCEPTION WHEN OTHERS THEN
      xx_dbex('END', 'xx_upd_new_code', 'XX'); 
    END xx_upd_new_code;
  </code></pre>
  
  <p>I am still unable to figure it out.</p>