How to test PL/SQL procedure with varray input

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.

I’m used to having a statement at the end like exec event_attendees(3,9); work correctly, but it gave me an error.

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?

This is what I have so far:

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 > 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;

Answer

You need to construct a VARRAY first. You can re-construct it with different values and re-call your stored procedure. Try the following.

declare
  l_param pe_varray;
begin
  l_param := pe_varray(3, 9);
  event_attendees(l_param);
  l_param := pe_varray(0, -5);
  event_attendees(l_param);
end;