functionplsqlvarray

PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got USER_NAME.VARCHAR_ARRAY


The below is a function that I am creating to accept an array of varchar2 items and return the internal pk of that record which is a NUMBER for each record. I am struggling to get the syntax right to pass an array of type VARCHAR_ARRAY to the simple sql query in the cursor and return the variable of type NUMBER_ARRAY. the Error is on line 8,42 i.e FROM table_name WHERE column_name IN VARCHAR_ARRAY which was passed to the function. Please help me with this error as I am learning plsql.

  create or replace TYPE VARCHAR_ARRAY AS VARRAY(1000000) OF VARCHAR2(1000);
   /

  create or replace TYPE NUMBER_ARRAY AS VARRAY(1000000) OF NUMBER;
   /

  create or replace Function GET_PRODUCT_ID_ARR(V_PRODUCT_NUM_ARR IN VARCHAR_ARRAY)
  RETURN NUMBER_ARRAY 
  IS
     product_id_list number_array := number_array(); 
     CURSOR c1
     IS 
     SELECT cat_map_id 
     FROM mn_cat_map WHERE product_num IN (V_PRODUCT_NUM_ARR) and catalog_type = 'INT';
  v_output NUMBER;   
  BEGIN
      OPEN c1; 
      LOOP
          fetch c1 into product_id_list;
          EXIT WHEN c1%notfound;
          product_id_list.extend;
          product_id_list(product_id_list.count)  := v_output;
         dbms_output.put_line('Product ('||v_output ||'):'||product_id_list(v_output));
      END LOOP;
  Close c1;
  RETURN product_id_list;
  END;
  /

Solution

  • There is two issues:

    1. You have to cast varray to table:

      CURSOR c1
      IS 
      SELECT cat_map_id 
      FROM mn_cat_map 
      WHERE product_num IN (select column_value from table(V_PRODUCT_NUM_ARR))
        and catalog_type = 'INT';
      
    2. Add bulk collect after fetch:

      LOOP
        fetch c1 bulk collect into product_id_list limit 100;
        EXIT WHEN c1%notfound;
        product_id_list.extend;
        product_id_list(product_id_list.count)  := v_output;
        dbms_output.put_line('Product ('||v_output ||'):'||product_id_list(v_output));
        END LOOP;
      

    If you write limit 100, each loop will put 100 records in product_id_list. You can omit limit clause, in this case you will get all records in one fetch.

    EDIT
    How to see results:

    declare 
      myarray varchar_array; 
      my_num_array number_array;
    begin 
      myarray := varchar_array(); 
      myarray.extend(2);
      myarray(1) := '151043'; 
      myarray(2) := '2895'; 
      my_num_array := GET_PRODUCT_ID_ARR(myarray);
      for i in 1 .. my_num_array.count loop
        dbms_output.put_line(my_num_array(i)); 
      end loop; 
    end;
    /