RE: intermediate table

There are many alternative approaches but since you are asking about using SQL against an array, here is one way using object features:

create or replace type EmpTypeRecord as object
(ename varchar2(14),

 sal number)
/

create or replace type EmpTableType as table of EmpTypeRecord /

Declare
  l_array EmpTableType;
Begin
  select CAST (multiset ( select ename, sal from emp) as EmpTableType)   into l_array
  from dual;
  For B_Rec In
  (Select *
   From TABLE (cast (l_array as EmpTableType))    Where ename = 'KING') Loop

        dbms_output.put_line(b_rec.ename||' '||to_char(b_rec.sal));    End Loop;
End;

The routine first loads up l_array with ename and sal from the EMP table. The second select, through the use of the CAST function, is able to select from l_array, and, apply criteria. You could code this many different ways.

Note, I'm still playing with and trying to learn more about the CAST function, object features, etc. The above is loosely based on a Tom Kyte example from one of the Oracle usenet groups. He has lots of nice examples on this subject.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
Sent: Wednesday, October 03, 2001 11:26 AM To: Multiple recipients of list ORACLE-L

Hi to all !
I have one small problem and i am sure many of u can reply .My problem is :i am storing data from different table into intermediate table but the i want to use sql statements to filter data from that intermediate table, I can access it as array of user defined object . but i want to use sql statement for extracting the data so plz help me out. the below code is just prototype,not actual one whoch is only one table TAX .
declare
type my_rec_type is record (c1 tax.col_lbl%type,c2 tax.subj_carr%type); type my_plsql_table_type is table of my_rec_type index by binary_integer; v_table my_plsql_table_type;
rec my_rec_type ;
num number;
cursor r is select rownum, col_lbl, subj_carr from tax; begin
open r;
loop
FETCH r INTO num ,rec.c1,rec.c2;
EXIT WHEN r%NOTFOUND;
v_table(num).c1 := rec.c1;
v_table(num).c2 := rec.c2;
end loop;
/* I want to use sql statements for fetch record like select rowi from v_table
where condition =conditionname
*/
end;
If there is any alternative for intermediate table let me know. thanx in advance
shishir kumar mishra
shishir_at_agnisoft.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).