Re: Accessing a complete collection

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Jun 2001 05:29:58 -0700
Message-ID: <9gks8602oes@drn.newsguy.com>

In article <_5eX6.147862$Be4.45245145_at_news3.rdc1.on.home.com>, "Paul says...
>
>"So I defined a table in memory to be like a database table structure."
>
>TYPE SomeTyp IS TABLE OF database_table%ROWTYPE
> INDEX BY BINARY_INTEGER;
>
>Some_tab SomeTyp;
>---------------------------
>" And I can add records into the memory table easily enough "
>
>ind := 0
> open my_cursor (seq,ordinal,v_element_list(z), state);
> loop
> fetch my_cursor into Some_tab(ind+1);
> exit when my_cursor%notfound;
> ind := ind +1;
> end loop;
> close my_cursor;
>"And I can see that the data is being stored in the memory table"
>
>for z in 1 .. IND
> loop
> dbms_output.put_line(to_char(z)||' '||
> Some_tab(z).name||' '||
> Some_tab(z).condition||' '||
> Some_tab(z).state
> );
>
> end loop;
>
>Finally The Question...
> Is there a method of accessing only certain elements of the memory table
>without accessing each individual row..
>
> ie... a cursor that will
> select * from memory_table
> where condition
>
>" instead of "
>
> for z in 1 .. element_counter
> loop
> for t in element_counter+1 .. Some_tab.count
> loop
> if Some_tab(t).element_order = z then
> dbms_output.put_line(Some_tab(t).name);
> end if;
> end loop;
> end loop;
>
>

Yes, but not the way you've coded the table type.

Instead of using a PLSQL type as you have, we must use a SQL type so that SQL can see it. We create a scalar object type (to support composite records) AT THE SQL level (eg: do not put this in a package):

tkyte_at_TKYTE816> create type myScalarType as object   2 ( a int, b varchar2(25), c date )
  3 /

Type created.

And then a table type of that scalar type:

tkyte_at_TKYTE816> create type myTableType as table of myScalarType   2 /

Type created.

Now we can use it in plsql & sql like this:

tkyte_at_TKYTE816> declare
  2 data myTableType := myTableType();   3 begin

  4      for i in 1..10
  5      loop
  6          data.extend;
  7          data(i) := myScalarType( i, 'row ' || i, sysdate+i );
  8      end loop;

  9
 10

 11      for x in ( select *
 12                   from  TABLE( cast( data as myTableType ) )
 13                  where a between 3 and 5
 14                  order by c )
 15      loop
 16          dbms_output.put_line( x.a || ',' || x.b || ',' || x.c );
 17      end loop;

 18 end;
 19 /

3,row 3,21-JUN-01
4,row 4,22-JUN-01
5,row 5,23-JUN-01

PL/SQL procedure successfully completed.

This is the 8.1 syntax, there is an 8.0 syntax that differs (uses the THE keyword) but in light of the lack of a version, I'll assume the latest greatest.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jun 18 2001 - 07:29:58 CDT