VARRAY Example 01

/*
  Demo the varray.
  
*/

-- create a varray with the maximum size 6.
create or replace type options_type as varray(6) of varchar2(30);
/

-- create a table with varray
-- You don't have to create another table to store the varray. 
create table questions(q_id number, options options_type);

-- insert values
insert into questions values(1, options_type('Option A', 'Option B', 'Option C',
'Option D'));
commit;

select * from questions;

-- Use PL/SQL codes to insert values to questions table.
set serveroutput on
declare
 options questions.options%type;
begin
  options := options_type('A', 'B', 'C', 'D', 'E', 'F');
  -- Insert a new question.
  insert into questions values (2, options);
  
  -- Print out the rows in the Question table
  for question in (select * from questions) loop
    dbms_output.put(question.q_id || ' ');
 -- Print out the options in a question.
    for i in 1..question.options.count loop
        dbms_output.put(question.options(i) || ' ');
    end loop;
    dbms_output.put_line(' ');
  end loop;
end;
/
rollback;