PL/SQL TABLE ARRAY SELECT

2 views
Skip to first unread message

ya...@hotmail.com

unread,
May 21, 1999, 3:00:00 AM5/21/99
to
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
ya...@yarch.com


--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

Thomas Kyte

unread,
May 22, 1999, 3:00:00 AM5/22/99
to
A copy of this was sent to ya...@hotmail.com
(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

Reply all
Reply to author
Forward
0 new messages