title: How to test PL/SQL procedure with varray input
body: <p>I've made a procedure that uses a varray as input, and while everything compiles correctly, I can't figure out how exactly to test/execute the procedure that actually works.
  The varray takes in at least two values: the first is an event ID (eid), and any other after are person IDs (pid). Both are numbers with max 38 digits.</p>
  <p>I'm used to having a statement at the end like exec event_attendees(3,9); work correctly, but it gave me an error.</p>
  <p><a href="https://i.stack.imgur.com/Je8ew.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Je8ew.png" alt="Screenshot of error message from running this statement" /></a></p>
  <p>I'd like to make different test cases to make sure each of the if/else parts work (one with an invalid eid, one with an invalid pid, one with an eid and pid combination already part of the table, and one which should cause no errors). Why doesn't the exec statement I tried work, and how can I test multiple different values in my varray?</p>
  <p>This is what I have so far:</p>
  <pre><code>set serveroutput on;
  create or replace type pe_varray as varray(10) of number(38);
  /
  
  create or replace procedure event_attendees(combo pe_varray)
  is
    eid_valid_check int;
    pid_valid_check int;
    combo_exist_check int;
  begin
    select count(*) into eid_valid_check from event where eid = combo(1);
    if eid_valid_check = 0 then
      dbms_output.put_line('Event does not exist');
    else
      for i in 2..combo.count loop
        select count(*) into pid_valid_check from people where pid = combo(i);
        if pid_valid_check = 0 then
          dbms_output.put_line('Person does not exist');
        else
          select count(*) into combo_exist_check from Person_Event where eid = combo(1) and pid = combo(i);
          if combo_exist_check &gt; 0 then
            dbms_output.put_line('No need to insert');
          else
            insert into Person_Event values(combo(1), combo(i));
            dbms_output.put_line('Attendee(s) for event with id ' || combo(1) || ' added');
          end if;
        end if;
      end loop;
    end if;
  end;
  </code></pre>