title: Fetching a PL/SQL sys_refcursor into a table of records
body: <p>I am trying to fetch the columns from a previously returned into a table of records. However, when i run the code in the package body I get the following error:</p>
  
  <pre><code>ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
  </code></pre>
  
  <p>However, when i try to do this in a PL/SQL anonymous block window, I can successfully fetch the records using Bulk Collect instruction. </p>
  
  <p>Here is my <strong>successful</strong> try as I stated:</p>
  
  <pre><code>DECLARE
  --
  O_ref SYS_REFCURSOR; 
  
  -- Variable and types declaration.
  TYPE REC_TYP is record  (
       column_1     number(8),
       column_2     varchar2(13)
    );
  
  TYPE TAB_TYP is table of REC_TYP;
  
  L_tab_typ   TAB_TYP;
  --
  BEGIN
    -- 
    open O_ref for 
      select sku,
             upc 
        from upc_ean
       where sku = 2004030; 
      --     
    LOOP
      --
        FETCH O_ref BULK COLLECT into L_tab_typ;
        EXIT WHEN L_tab_typ.COUNT = 0;
        --
        FOR indx IN 1 .. L_tab_typ.COUNT 
        LOOP
          --
          dbms_output.put_line('SKU: ' || L_tab_typ(indx).column_1);
          dbms_output.put_line('UPC: ' || L_tab_typ(indx).column_2);
          --
        END LOOP;
        --
    END LOOP;
    --
    CLOSE O_ref;
    --
  END;
  </code></pre>
  
  <p>When I run this code I get the following output:</p>
  
  <pre><code>SKU: 2004030
  UPC: 5601126003439
  SKU: 2004030
  UPC: 5601126039056
  </code></pre>
  
  <p>In the package body I have the following:</p>
  
  <p>Why doesn't this work in a regular package? </p>
  
  <pre><code>FUNCTION GET_STORE_ITEMS(I_store           IN   number
                           ----------- output ------------
                           O_item_data       OUT  NB_TAB_ITEM_DETAIL, -- i want to return a table type after I get the info from the sys_ref
                           ----------- error -------------
                           O_error_message   OUT  VARCHAR2)
    RETURN BOOLEAN IS
      --
      L_tab_type        NB_TAB_ITEM_DETAIL;
      L_sys_ref         SYS_REFCURSOR;
      L_test_sku        number(8);
      --
      CURSOR C_GET_ITEMS IS
        --
        SELECT a.sku
          FROM win_store a
         WHERE a.store = I_store;
      --
    BEGIN
      --
      -- Loop over the fashion skus.
      FOR R_items IN C_GET_ITEMS LOOP
        --
        BEGIN
          --
          IF GET_ITEM_DETAIL(I_store         =&gt; I_store,
                             I_sku           =&gt; R_items.sku,
                             O_item_data     =&gt; L_sys_ref, -- returns a sys_refcursor with the same structure as the type
                             O_error_message =&gt; L_error_message) = FALSE THEN
            --
            O_error_message := NB_MESSGE40_SQL.EMESSAGE(L_error_message);
            RETURN FALSE;
            --
          END IF;
          --
          LOOP
          --
            FETCH L_sys_ref BULK COLLECT into L_tab_type; -- It jumps to when others exception
            EXIT WHEN L_tab_type.COUNT = 0;
            --
            FOR indx IN 1 .. L_tab_type.COUNT 
            LOOP
              --
              L_test_sku := L_tab_type(indx).sku;
              --
            END LOOP;
            --
          END LOOP;
          --                      
          END;                                        
          --
      END LOOP;
      --
      RETURN TRUE;
      --
    EXCEPTION
      --
      WHEN OTHERS THEN
        --
        -- ...
        RETURN FALSE;
        --
  END GET_STORE_ITEMS;
  </code></pre>
  
  <p>Thank you!</p>