Selecting Values from Oracle Table Variable / Array ?

Question

Following on from my last question (http://stackoverflow.com/questions/1573326/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

In Oracle, the PL/SQL and SQL engines maintain some separation. When you execute a SQL statement within PL/SQL, it is handed off to the SQL engine, which has no knowledge of PL/SQL-specific structures like INDEX BY tables.

So, instead of declaring the type in the PL/SQL block, you need to create an equivalent collection type within the database schema:

CREATE OR REPLACE TYPE array is table of number;
/

Then you can use it as in these two examples within PL/SQL:

SQL> l
  1  declare
  2    p  array := array();
  3  begin
  4    for i in (select level from dual connect by level < 10) loop
  5      p.extend;
  6      p(p.count) := i.level;
  7    end loop;
  8    for x in (select column_value from table(cast(p as array))) loop
  9       dbms_output.put_line(x.column_value);
 10    end loop;
 11* end;
SQL> /
1
2
3
4
5
6
7
8
9

PL/SQL procedure successfully completed.

SQL> l
  1  declare
  2    p  array := array();
  3  begin
  4    select level bulk collect into p from dual connect by level < 10;
  5    for x in (select column_value from table(cast(p as array))) loop
  6       dbms_output.put_line(x.column_value);
  7    end loop;
  8* end;
SQL> /
1
2
3
4
5
6
7
8
9

PL/SQL procedure successfully completed.

Additional example based on comments

Based on your comment on my answer and on the question itself, I think this is how I would implement it. Use a package so the records can be fetched from the actual table once and stored in a private package global; and have a function that returns an open ref cursor.

CREATE OR REPLACE PACKAGE p_cache AS
  FUNCTION get_p_cursor RETURN sys_refcursor;
END p_cache;
/

CREATE OR REPLACE PACKAGE BODY p_cache AS

  cache_array  array;

  FUNCTION get_p_cursor RETURN sys_refcursor IS
    pCursor  sys_refcursor;
  BEGIN
    OPEN pCursor FOR SELECT * from TABLE(CAST(cache_array AS array));
    RETURN pCursor;
  END get_p_cursor;

  -- Package initialization runs once in each session that references the package
  BEGIN
    SELECT level BULK COLLECT INTO cache_array FROM dual CONNECT BY LEVEL < 10;
  END p_cache;
/

Answer 2

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

Answer 3

The sql array type is not neccessary. Not if the element type is a primitive one. (Varchar, number, date,...)

Very basic sample:

declare
  type TPidmList is table of sgbstdn.sgbstdn_pidm%type;
  pidms TPidmList;
begin
  select distinct sgbstdn_pidm
  bulk collect into pidms
  from sgbstdn
  where sgbstdn_majr_code_1 = 'HS04'
  and sgbstdn_program_1 = 'HSCOMPH';

  -- do something with pidms

  open :someCursor for
    select value(t) pidm
    from table(pidms) t;
end;

When you want to reuse it, then it might be interesting to know how that would look like. If you issue several commands than those could be grouped in a package. The private package variable trick from above has its downsides. When you add variables to a package, you give it state and now it doesn't act as a stateless bunch of functions but as some weird sort of singleton object instance instead.

e.g. When you recompile the body, it will raise exceptions in sessions that already used it before. (because the variable values got invalided)

However, you could declare the type in a package (or globally in sql), and use it as a paramter in methods that should use it.

create package Abc as
  type TPidmList is table of sgbstdn.sgbstdn_pidm%type;

  function CreateList(majorCode in Varchar, 
                      program in Varchar) return TPidmList;

  function Test1(list in TPidmList) return PLS_Integer;
  -- "in" to make it immutable so that PL/SQL can pass a pointer instead of a copy
  procedure Test2(list in TPidmList);
end;

create package body Abc as

  function CreateList(majorCode in Varchar, 
                      program in Varchar) return TPidmList is
    result TPidmList;
  begin
    select distinct sgbstdn_pidm
    bulk collect into result
    from sgbstdn
    where sgbstdn_majr_code_1 = majorCode
    and sgbstdn_program_1 = program;

    return result;
  end;

  function Test1(list in TPidmList) return PLS_Integer is
    result PLS_Integer := 0;
  begin
    if list is null or list.Count = 0 then
      return result;
    end if;

    for i in list.First .. list.Last loop
      if ... then
        result := result + list(i);
      end if;
    end loop;
  end;

  procedure Test2(list in TPidmList) as
  begin
    ...
  end;

  return result;
end;

How to call it:

declare
  pidms constant Abc.TPidmList := Abc.CreateList('HS04', 'HSCOMPH');
  xyz PLS_Integer;
begin
  Abc.Test2(pidms);
  xyz := Abc.Test1(pidms);
  ...

  open :someCursor for
    select value(t) as Pidm,
           xyz as SomeValue
    from   table(pidms) t;
end;