PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

Question

I have created a procure to display the data in two table using BULK COLLECT, but i keep getting this error.

PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

However it works if i remove the BULK COLLECT and include a where clause in the statement.

create or replace PROCEDURE sktReport IS

  TYPE inventory_table_type is RECORD (
                    v_WH_ID                  INVENTORY.WH_ID%TYPE,
                    v_wa_Product_quantity_id Product_quantity.ST_ID%TYPE); 
  v_inventory_table inventory_table_type;
BEGIN

  SELECT INVENTORY.WH_ID, Product_quantity.ST_ID,  
    BULK COLLECT INTO
     v_inventory_table.v_WH_ID, 
     v_inventory_table.v_wa_Product_quantity_id,  
   FROM INVENTORY
  INNER JOIN Product_quantity
     ON Product_quantity.ST_ID = INVENTORY.ST_ID;

  FOR i IN v_inventory_table.v_WH_ID..v_inventory_table.v_WH_ID
  LOOP
    DBMS_OUTPUT.PUT_LINE ('ID : ' || v_inventory_table.v_WH_ID
                          || ' quantity ID : ' || v_inventory_table.v_in_Product_quantity_id);
  END LOOP;

END;

Answer 1

Here i have demonstrated a simple example to replicate your scenario. Please see below code. This may help you out.

SET serveroutput ON;
DECLARE
TYPE AV_TEST
IS
  RECORD
  (
    lv_att1 PLS_INTEGER,
    lv_att2 PLS_INTEGER );
type av_test_tab
IS
  TABLE OF av_test;
  av_test_tab_av av_test_tab;
BEGIN
  NULL;
  SELECT LEVEL,
    LEVEL+1 BULK COLLECT
  INTO av_test_tab_av
  FROM DUAL
    CONNECT BY LEVEL < 10;
  dbms_output.put_line(av_test_tab_av.count);
  FOR I IN av_test_tab_av.FIRST..av_test_tab_av.LAST
  LOOP
    dbms_output.put_line('working fine '||av_test_tab_av(i).lv_att1||' '||av_test_tab_av(i).lv_att2);
  END LOOP;
END;

Answer 2

Here's a simple showcase, I've just written that might help you :)

SET SERVEROUTPUT ON;
DECLARE
  TYPE t_some_type IS RECORD (
    the_id  NUMBER
    ,the_name VARCHAR2(1)
  );
  TYPE t_some_type_tab IS TABLE OF t_some_type;

  lt_some_record  t_some_type;
  lt_some_array t_some_type_tab := NEW t_some_type_tab();
BEGIN

  WITH some_values AS (
    SELECT
      DECODE(LEVEL,1,1,2,2,3,3,4,4) AS the_id
      ,DECODE(LEVEL,1,'A',2,'B',3,'C',4,'D') AS the_name
    FROM
      dual
    CONNECT BY LEVEL < 5
  )
  SELECT
    sv.the_id
    ,sv.the_name
  BULK COLLECT INTO -- use this to select into an array/collection type variable
    lt_some_array
  FROM
    some_values sv;

  DBMS_OUTPUT.PUT_LINE(lt_some_array.COUNT);

  WITH some_values AS (
    SELECT
      DECODE(LEVEL,1,1,2,2,3,3,4,4) AS the_id
      ,DECODE(LEVEL,1,'A',2,'B',3,'C',4,'D') AS the_name
    FROM
      dual
    CONNECT BY LEVEL < 5
  )
  SELECT
    sv.the_id
    ,sv.the_name
  INTO -- use this to select into a regular variables
    lt_some_record.the_id
    ,lt_some_record.the_name
  FROM
    some_values sv
  WHERE
    sv.the_id = 1;

  DBMS_OUTPUT.PUT_LINE(lt_some_record.the_id||': '||lt_some_record.the_name);

  -- you can also insert such record into your array type variable
  lt_some_array := NEW t_some_type_tab();
  lt_some_array.EXTEND; -- extend the array type variable (so it could store one more element, than now - which was 0)
  lt_some_array(lt_some_array.LAST) := lt_some_record; -- assign the first element of array type variable

  DBMS_OUTPUT.PUT_LINE(lt_some_array.COUNT||'  '||lt_some_array(lt_some_array.LAST).the_id||': '||lt_some_array(lt_some_array.LAST).the_name);

END;
/

Also, since you want to iterate through your results, you can just use cursor (implicit or explicit) e.g.

DECLARE
  -- cursor declaration
  CURSOR c_some_cursor IS
  WITH some_values AS (
    SELECT
      DECODE(LEVEL,1,1,2,2,3,3,4,4) AS the_id
      ,DECODE(LEVEL,1,'A',2,'B',3,'C',4,'D') AS the_name
    FROM
      dual
    CONNECT BY LEVEL < 5
  )
  SELECT
    sv.the_id
    ,sv.the_name
  FROM
    some_values sv;

BEGIN
  -- using explicit, earlier declared cursor
  FOR c_val IN c_some_cursor
  LOOP
    DBMS_OUTPUT.PUT_LINE(c_val.the_id||': '||c_val.the_name);
  END LOOP;


  -- using implicit, not declared cursor
  FOR c_val IN (
    WITH some_values AS (
      SELECT
        DECODE(LEVEL,1,1,2,2,3,3,4,4) AS the_id
        ,DECODE(LEVEL,1,'A',2,'B',3,'C',4,'D') AS the_name
      FROM
        dual
      CONNECT BY LEVEL < 5
    )
    SELECT
      sv.the_id
      ,sv.the_name
    FROM
      some_values sv
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE(c_val.the_id||': '||c_val.the_name);
  END LOOP;
END;
/