plsql - Selecting Values from Oracle Table Variable / Array?

Question

Following on from my last question (Table Variables in Oracle PL/SQL?)...

Once you have values in an array/table, how do you get them back out again? Preferably using a select statement or something of the like?

Here's what I've got so far:

declare
    type array is table of number index by binary_integer;
    pidms array;
begin
    for i in    (
                select distinct sgbstdn_pidm
                from sgbstdn
                where sgbstdn_majr_code_1 = 'HS04'
                and sgbstdn_program_1 = 'HSCOMPH'
                )
    loop
        pidms(pidms.count+1) := i.sgbstdn_pidm;
    end loop;

    select *
    from pidms; --ORACLE DOESN'T LIKE THIS BIT!!!
end;

I know I can output them using dbms_output.putline(), but I'm hoping to get a result set like I would from selecting from any other table.

Thanks in advance, Matt

Answer 1

You might need a GLOBAL TEMPORARY TABLE.

In Oracle these are created once and then when invoked the data is private to your session.

Oracle Documentation Link

Try something like this...

CREATE GLOBAL TEMPORARY TABLE temp_number
   ( number_column   NUMBER( 10, 0 )
   )
   ON COMMIT DELETE ROWS;

BEGIN 
   INSERT INTO temp_number
      ( number_column )
      ( select distinct sgbstdn_pidm 
          from sgbstdn 
         where sgbstdn_majr_code_1 = 'HS04' 
           and sgbstdn_program_1 = 'HSCOMPH' 
      ); 

    FOR pidms_rec IN ( SELECT number_column FROM temp_number )
    LOOP 
        -- Do something here
        NULL; 
    END LOOP; 
END; 
/