PL/SQL TABLE ARRAY SELECT
ya...@hotmail.com
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
ya...@yarch.com
--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---
Thomas Kyte
(if that email address didn't require changing)
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
tk...@us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation