Distinct Values in Array in PLSQL

Question

I want to store distinct values from a column present in a table into array. I am getting below error. How can I achieve this?

select count(distinct(Name)) into n from table;      

create or replace type array_type is varray(100) of varchar2(20); 

for i in 1 .. n loop
    name_array.extend;
    select distinct(Name) into name_array(i) from table order by name asc;
end loop;   

Error :
 ORA-01422: exact fetch returns more than requested number of rows

Table :

Answer 1

You can do SELECT DISTINCT value BULK COLLECT INTO to get distinct values and store them into an collection.

Try:

CREATE TABLE test_table (
  name VARCHAR2(20),
  city VARCHAR2(250)
);

INSERT INTO test_table (name, city) VALUES ('A', 'X');
INSERT INTO test_table (name, city) VALUES ('B', 'Y');
INSERT INTO test_table (name, city) VALUES ('A', 'Z');
INSERT INTO test_table (name, city) VALUES ('C', 'K');
INSERT INTO test_table (name, city) VALUES ('D', 'P');
INSERT INTO test_table (name, city) VALUES ('A', 'Q');
INSERT INTO test_table (name, city) VALUES ('D', 'R');
INSERT INTO test_table (name, city) VALUES ('C', 'S');

COMMIT;

CREATE OR REPLACE TYPE array_type IS VARRAY(100) OF VARCHAR2(20);

DECLARE
  v_name_array array_type := array_type();
BEGIN
  SELECT DISTINCT NAME
    BULK COLLECT
    INTO v_name_array
    FROM test_table
   ORDER BY NAME ASC;
  -- Show resulting values in collection
  FOR i IN 1 .. v_name_array.count
  LOOP
    dbms_output.put_line(v_name_array(i));
  END LOOP;
END;
/