title: How to do conditional processing in a bulk collect loop?
body: <p>we have <code>Oracle 11G</code> and i'm trying to move data from one table to another using <code>bulk collect</code>. Problem is when I tried to evaluate if one field from origin is empty my package got invalidated. What I have:</p>
  
  <p>Declaration:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE MYSCHEMA.MYPKG AS
  CURSOR CUR_MYDATA IS
          SELECT
              o.name,
              o.last_name,
              o.id,
              o.socnum
          FROM
              origin o
          WHERE
              1=1
              AND o.name like upper ('a%');
  
          TYPE t_name IS TABLE OF origin.name%TYPE;
          TYPE t_lastname IS TABLE OF origin.last_name%TYPE;
          TYPE t_id IS TABLE OF origin.id%TYPE;
          TYPE t_socnum IS TABLE OF origin.socnum%TYPE;
  
          l_name t_name;
          l_lastname t_lastname;
          l_id t_id;
          l_socnum t_socnum;
  
  PROCEDURE MYPROCEDURE;
  
  END MYPKG;
  </code></pre>
  
  <p>Body:</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS
  
      PROCEDURE MYPROCEDURE IS
  
      BEGIN
          OPEN CUR_MYDATA;
          LOOP
          FETCH CUR_MYDATA BULK COLLECT INTO l_name,l_lastname,l_id,l_socnum;
              forall i IN 1 .. l_name.COUNT
              IF ( l_socnum(i) IS NULL) 
                  THEN (select oo.socnum from other_origin where oo.id=l_id(i)) 
              END IF;
                  INSERT INTO destiny (
                      d_name,
                      d_lastname,
                      d_id,
                      d_socnum) 
                  VALUES (
                      l_name(i),
                      l_lastname(i),
                      l_id(i),
                      l_socnum(i),
              EXIT WHEN l_name.count = 0;
          END LOOP;
      END MYPROCEDURE;
  
  END MYPKG;
  </code></pre>
  
  <p>but when I check body status it is <code>INVALID</code></p>
  
  <p>any thoughs?</p>