How to store multiple rows in a variable in pl/sql function?

I’m writing a pl/sql function. I need to select multiple rows from select statement:

    SELECT pel.ceid
    FROM pa_exception_list pel
    WHERE trunc(pel.creation_date) >= trunc(SYSDATE-7)

if i use:

SELECT pel.ceid
INTO v_ceid

it only stores one value, but i need to store all values that this select returns. Given that this is a function i can’t just use simple select because i get error, “INTO – is expected.”

Answer

You can use a record type to do that. The below example should work for you

DECLARE
    TYPE v_array_type IS VARRAY (10) OF NUMBER;
        var v_array_type;
BEGIN
    SELECT x
    BULK COLLECT INTO
        var 
    FROM (
            SELECT 1 x
            FROM dual
            UNION
            SELECT 2 x
            FROM dual
            UNION
            SELECT 3 x
            FROM dual
        );
        FOR I IN 1..3 LOOP
              dbms_output.put_line(var(I));
            END LOOP;

END;

So in your case, it would be something like

select pel.ceid 
  BULK COLLECT INTO <variable which you create>
  from pa_exception_list
 where trunc(pel.creation_Date) >= trunc(sysdate-7);