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

User · Accepted Answer

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

User · Answer

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

User · Answer

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 gt  l   1  declare   2    p  array    array      3  begin   4    for i in  select level from dual connect by level  lt  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 gt    1 2 3 4 5 6 7 8 9  PL SQL procedure successfully completed   SQL gt  l   1  declare   2    p  array    array      3  begin   4    select level bulk collect into p from dual connect by level  lt  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 gt    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  lt  10    END p cache

[oracle] Selecting Values from Oracle Table Variable / Array?

Examples related to oracle

Examples related to plsql