Home » SQL & PL/SQL » SQL & PL/SQL » Table Store In Array ...
Table Store In Array ... [message #39321] Sat, 06 July 2002 15:01 Go to next message
jagjeet singh
Messages: 16
Registered: October 2001
Junior Member
hello .. Sir .

i have 9i on my system ...

i have a table "TM_COMPONENT_DETAIL" 25 colunms and having 5000 records ...

TM_COMPONENT_DETAIL

COMP_CD COMP_DESC VAL
------- --------- --------
A AAAAAAA 54684373
B AAAAAAA 54684373
C AAAAAAA 45612465
D AAAAAAA 65423646
E AAAAAAA 44353243
F AAAAAAA 53465546
G AAAAAAA 43342313
H AAAAAAA 21597227
I AAAAAAA 42234387
J AAAAAAA 52236456
K AAAAAAA 52236456
L AAAAAAA 52236456

in a procedure i have to query it many times .. and responce time is very slow ...
sir i want to store this into a array .... i have tow options

like ...1

-----------------------------------
declare
v_coun pls_integer := 0 ;
v_tab_type tm_component_detail%rowtype;
type vt_tab_type is table of v_tab_type index by binary_integer ;
v_tab vt_tab_type;
begin
for i in ( select * from tm_component_detail ) loop
v_count := v_count + 1 ;
v_tab(v_count).comp_cd := i.comp_cd ;
end loop;
end; ---- its not good
------------------------------------
or
------------------------------------
declare
v_arr_comp_cd dbms_sql.varchar2_table ;
v_arr_comp_desc dbms_sql.varchar2_table ;
v_arr_comp_val dbms_sql.varchar2_table ;

begin
select comp_cd,comp_desc,val bulk collect into v_arr_comp_cd,v_arr_comp_desc,v_arr_comp_val
from <
>
end;

later i can access this .. but i can't declare 95 cursors ....

--------------------------------------------------------------------
is there any way ... like

-------------------
declare
type v_tab_type is table or tm_component_detail%rowtype
index by binary_integer ;
v_tab v_tab_type ;
begin
select * bulk collect into v_tab ......
end;
----------------------
Sir if there is any other way .. then pls tell me ....
if i use ref curosr then .. i have to open it many time ..... same condition like ..1 example ...

and sir i made this ... is it ok ...

-----***************
declare
type ary is table of varchar2(30) index by binary_integer ;
v_array ary;
a varchar2(30) := &tab ;
b varchar2(100) ;
aa varchar2(1000);
n number := 0 ;
begin
if a<10 then a:= '0'||a; elsif length(a)>2 then a:=substr(a,1,length(a)-3)||'0'||substr(a,-3,3); end if;
--------
for i in 1..trunc((length(a)+1) / 2 ) loop
n := n - 2 ;
select decode(nvl(length(substr(a,n,2)),0),0,'0'||substr(a,n+1,1),substr(a,n,2)) into v_array(i) from dual;
end loop;
-------
for i in 1..v_array.count loop
a := v_array(i);

if a between 11 and 19 then
--
select decode(a,11,'Eleven',12,'Twelve',13,'Thirteen',14,'Fourteen',15,'Fifteen',16,'Sixteen',17,'Seventeen',
18,'Eighteen',19,'Nineteen')||' '||
decode(a,0,null,decode(i,1,'Only',2,'Hundred',3,'Thousand',4,'Lakh',5,'Crore',6,'Arab',7,'Kharab',8,'Neel'))
into b from dual;
else
select decode(substr(a,1,1),1,'Ten',2,'Twenty',3,'Thirty',4,'Fourty',5,'Fifty',6,'Sixty', 7,'Seventy',
8,'Eighty',9,'Ninty')||' '||
decode(substr(a,2,1),1,'One',2,'Two',3,'Three',4,'Four',5,'Five',6,'Six',7,'Seven',8,'Eigh',9,'Nine')||' '||
decode(a,0,null,decode(i,1,'Only',2,'Hundred',3,'Thousand',4,'Lakh',5,'Crore',6,'Arab',7,'Kharab', 8,'Neel'))
into b from dual;
end if;
aa := b||' '||aa;
end loop;
dbms_output.put_line(aa);
end;
/
--------------------********************
Re: Table Store In Array ... [message #39347 is a reply to message #39321] Tue, 09 July 2002 09:52 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Usually the data returned from a full table scan is not kept in the buffer cache (else it would be full in no time). Try caching the table (see sql reference, or try adding some query criteria to make oracle cache the data). You should not need to store the data in a pl/sql table. If the data is very static and you don't expect changes to it, then try populating the pl/sql table (held in a package so that it is there for the whole session). Here is an example of querying a pl/sql table.
create or replace type myTableType as table of number;

declare
        l_x     myTableType :=
                 myTableType( 8, 5, 3, 7,  9, 1, 2, 6, 4  );

begin
        dbms_output.put_line( 'Unsorted' );
        for x in ( select a.column_value  val
            from THE ( select cast( l_x as mytableType )
                         from dual ) a )
        loop
                dbms_output.put_line( x.val );
        end loop;

        dbms_output.put_line( 'Sorted Desc' );
        for x in ( select a.column_value  val
            from THE ( select cast( l_x as mytableType )
                         from dual ) a
           order by a.column_value desc )
        loop
                dbms_output.put_line( x.val );
        end loop;

        dbms_output.put_line( '---------' );
                
        for x in ( select sum( a.column_value ) val
               from THE ( select cast( l_x as mytableType )
                            from dual ) a
                 )
        loop
                dbms_output.put_line( x.val );
        end loop;

end;

Unsorted
8
5
3
7
9
1
2
6
4
Sorted Desc
9
8
7
6
5
4
3
2
1
---------
45
Previous Topic: reverse view
Next Topic: Optimising the nested loop.
Goto Forum:
  


Current Time: Thu Mar 28 14:30:17 CDT 2024