How to Select Into a TABLE OF NUMBER INDEX BY VARCHAR2

Question

I have a stored procedure. Instead of bulk collect into v_value_list, I want it to have a column name as index, like:

v_values['DIST_TABLE.APPRVD'] = val1,
v_values['DIST_TABLE.TOT_CAP'] = val2,
v_values['DIST_TABLE.NEW_CAP'] = val3,

etc. Can we use the Bulk Collect Into the values_table_type (see below) to achieve this?

My procedure:

PROCEDURE pr_capacities_update(id_in IN NUMBER) 
AS
v_value_list SYS.ODCINUMBERLIST;
v_values values_table_type;
TYPE values_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
v_values values_table_type;
BEGIN
    SELECT val
    BULK COLLECT INTO v_value_list
    FROM (
        SELECT val
        FROM DIST_TABLE
        UNPIVOT (val FOR col IN (APPRVD, TOT_CAP, NEW_CAP, EXIST_CAP))
        WHERE DIST_ID = id_in
    );
END pr_capacities_update;

Answer 1

You can't, but a simple FOR-LOOP will take care of it and give the same array fetching benefits

PROCEDURE pr_capacities_update(id_in IN NUMBER) 
AS
v_value_list SYS.ODCINUMBERLIST;
v_values values_table_type;
TYPE values_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
v_values values_table_type;
BEGIN
  for i in (
    SELECT key, value
    FROM ... ) 
  loop
      v_values(i.key) := i.value;
  end loop;
END pr_capacities_update;