Re: PL/SQL TABLE ARRAY SELECT

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 22 May 1999 01:44:23 GMT
Message-ID: <3748097f.5471527@newshost.us.oracle.com>

A copy of this was sent to yarch_at_hotmail.com (if that email address didn't require changing) On Fri, 21 May 1999 21:15:56 GMT, you wrote:

>How can I create a temporary table in a stored procedure that I can
>INSERT and SELECT from..or better yet..
>select from an array that I've created...
>
>Here's my situation...I created an array using a pl/sql table...
>
>TYPE miketabtype IS TABLE OF number(10) INDEX BY BINARY INTEGER;
>
>miketable miketabtype;
>
>I filled it with customer numbers and now I need to use all the
>customer numbers in a subselect that's in an IN clause...like this
>
>select ...
>from ...
>where
> cust_no IN (select * from miketable));
>
>but select doesn't work with a pl/sql table.....i want to do it all
>in one shot...not loop thru and call it one by one. i can't create
>a table beforehand....dba won't let me....and i would prefer not to
>build the sql dynamically and submit it with DBMS_SQL.parse and
>DBMS_SQL.execute....
>
>I would appreciate any suggestions...
>Thanks
>Mike
>yarch_at_yarch.com
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

2 examples here. #1 for Oracle8.0 and up, #2 for any release.

#1

SQL> REM instead of putting a type in a spec, do this:
SQL> 
SQL> create or replace type myTableType as table of number;

  2 /

Type created.

SQL> 
SQL> REM here is an example of selecting from a local variable that is a pl/sql
SQL> REM table filled in at run time.
SQL> 
SQL> declare
  2      l_x    myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );

  3
  3 begin

  4      for x in ( select *
  5                   from all_users
  6                  where user_id in
  7                  ( select *
  8                      from THE ( select cast( l_x as mytableType )
  9                                   from dual ) a ) )
 10      loop
 11          dbms_output.put_line( x.username );
 12      end loop;

 13 end;
 14 /
SYSTEM PL/SQL procedure successfully completed.

SQL> 
SQL> REM now, we want to "select * from PLSQL_FUNCTION()" not from a table:
SQL> REM this shows how to 'insert into VAR select' as well...
SQL> 
SQL> create or replace function getMyTableType return myTableType
  2  as
  3      l_x    myTableType;
  4  begin
  5          select cast( multiset( select user_id from all_users where rownum <
10 )
  6                                   AS myTableType )
  7            into l_x
  8            from dual;
  9  
  9      return l_x;

 10 end;
 11 /

Function created.

SQL> 
SQL> 
SQL> REM here we go... selecting from it:
SQL> 
SQL> select * from all_users where user_id in

  2 ( select *
  3 from THE ( select cast( getMyTableType() as mytableType )

  4                         from dual ) a

  5 )
  6 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 01-SEP-97
SYSTEM                                  5 01-SEP-97
DBSNMP                                 17 01-SEP-97
TRACESVR                               19 01-SEP-97

...

#2

create a 'pure' function to return the i'th element of the table (get_data below)

create a 'pure' function to return the count of the elements in the table (get_cnt below)

find a real table bigger then your plsql table (typically all_objects fits the bill). we will use rownum on this table to index into the plsql table and get access to it in sql.

SQL> create or replace package my_pkg
  2 as
  3 type myTableType is table of number index by binary_integer;   4

  4      myArray myTabletype;
  5      cnt        number;
  6  
  6      procedure fill_data;
  7  
  7      function get_data( x in number ) return number;
  8      pragma restrict_references(get_data,wnds);
  9      function get_cnt return number;
 10      pragma restrict_references(get_cnt,wnds,wnps);

 11 end;
 12 /

Package created.

SQL>
SQL> create or replace package body my_pkg   2 as
  3
  3 function get_data( x in number ) return number   4 is
  5 begin
  6 return myArray(x);
  7 end;
  8
  8 function get_cnt return number
  9 is
 10 begin
 11 return cnt;
 12 end;
 13
 13 procedure fill_data
 14 is
 15 begin

 16      for i in 1 .. 10 loop
 17          myArray(i) := i;
 18      end loop;
 19      cnt := 10;

 20 end;
 21
 21
 21 end;
 22 /

Package body created.

SQL>
SQL> exec my_pkg.fill_data

PL/SQL procedure successfully completed.

SQL>
SQL> select * from all_users
  2 where user_id in ( select my_pkg.get_data(rownum)

  3                       from all_objects
  4                      where rownum <= ( select my_pkg.get_cnt from dual ) )

  5 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 01-SEP-97

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 21 1999 - 20:44:23 CDT