| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to create a 2-D array inside a package
In article <3BBA7DAF.5708_at_maxinter.net>, C says...
>
>Thomas Kyte wrote:
>>
>> In article <3BB46380.E6DD5A3F_at_hollomey.com>, Markus says...
>> >
>> >2 things:
>> >a. which version of oracle?
>> >
>> >if 8i or higher:
>> >RTFM (read the f****** manual)
>>
>> I always thought that was "read that fine manual"
>>
>> I think you mean 9I or higher as collections of collections (what they are
>> looking for) is a new 9i feature, not available in 8i and before. In 9i, you
>> can:
>>
>> create type array1_type as table of number
>> /
>> create type array2_type as table of array1_type
>> /
>> create type array3_type as table of array2_type
>> /
>>
>> and then:
>>
>> declare
>> l_array1 array1_type := array1_type();
>> l_array2 array2_type := array2_type();
>> l_array3 array3_type := array3_type();
>> begin
>> l_array1 := array1_type( 1, 2, 3 );
>>
>> l_array2 := array2_type( l_array1, l_array1 );
>>
>> l_array3 := array3_type( l_array2, l_array2, l_array2, l_array2 );
>>
>> insert into t values ( 1, l_array1, l_array2, l_array3 );
>>
>> for i in 1 .. l_array3.count
>> loop
>> for j in 1 .. l_array3(i).count
>> loop
>> for k in 1 .. l_array3(i)(j).count
>> loop
>> dbms_output.put_line
>> ( 'l_array3(' || i || ',' || j || ',' || k || ') = ' ||
>> l_array3(i)(j)(k) );
>> end loop;
>> end loop;
>> end loop;
>> end;
>> /
>>
>> having in effect a multi-dimensional array.
>> >on "Collections and Records"
>> >there you might find everything you need
>> >
>> >C Chang wrote:
>> >
>> >> I like to use the input data to create a run time 2-D array. The only
>> >> thing I know is to something like
>> >>
>> >> variable_ref(count) which is a 1D array.
>> >>
>> >> Can Any guru give a tip? Thanks
>> >>
>> >> CC
>> >
>>
>> --
>> 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
>Unfortunetely, We are still using Oracle 8i here. So does that mean
>there is no way to create the 2D array?
>
>CC
You can have a collection of object types or a table of records which is sort of like a 2 dim array in that it has "rows" and "columns".
Other then that, no, there are no native 2 dim arrays prior to 9i.
You can sort of "mimick" them with a single dimension array and your own "indexing" scheme. For example:
create or replace package my_array
as
procedure dim( p_dim1 in number default NULL,
p_dim2 in number default NULL,
p_dim3 in number default NULL,
p_dim4 in number default NULL );
pragma restrict_references( dim, wnds);
function getval( p_idx1 in number default NULL,
p_idx2 in number default NULL,
p_idx3 in number default NULL,
p_idx4 in number default NULL ) return number;
pragma restrict_references( getval, wnds, rnds );
procedure setval( p_value in number,
p_idx1 in number default NULL,
p_idx2 in number default NULL,
p_idx3 in number default NULL,
p_idx4 in number default NULL );
pragma restrict_references( setval, wnds, rnds );
procedure remove;
pragma restrict_references( remove, wnds );
pragma restrict_references( my_array, wnds, rnds, wnps, rnps );
end my_array;
/
create or replace package body my_array
as
type nArray is table of number index by binary_integer;
g_dim1 number default 0; g_dim2 number default 0; g_dim3 number default 0; g_dim4 number default 0; g_dim2_3 number; g_dim3_4 number; g_dim2_3_4 number; g_ndim number default 0;
g_array nArray;
procedure dim( p_dim1 in number default NULL,
p_dim2 in number default NULL,
p_dim3 in number default NULL,
p_dim4 in number default NULL )
is
g_dim1 := nvl(p_dim1,0);
g_dim2 := nvl(p_dim2,0);
g_dim3 := nvl(p_dim3,0);
g_dim4 := nvl(p_dim4,0);
g_dim2_3 := g_dim2 * g_dim3;
select decode( p_dim1, NULL, 0,
decode( p_dim2, NULL, 1,
decode( p_dim3, NULL, 2,
decode( p_dim4, NULL, 3, 4 ) ) ) )
into g_ndim
from dual;
function idx_of( p_idx1 in number default 0,
p_idx2 in number default 0,
p_idx3 in number default 0,
p_idx4 in number default 0 ) return number
is
l_idx number;
begin
if ( g_nDim = 1 ) then
l_idx := p_idx1;
elsif ( g_nDim = 2 ) then
l_idx := p_idx1 * g_dim2 +
p_idx2;
elsif ( g_nDim = 3 ) then
l_idx := p_idx1 * g_dim2_3 +
p_idx2 * g_dim3 +
p_idx3;
elsif ( g_nDim = 4 ) then
l_idx := p_idx1 * g_dim2_3_4 +
p_idx2 * g_dim3_4 +
p_idx3 * g_dim4 +
p_idx4;
function getval( p_idx1 in number default 0,
p_idx2 in number default 0,
p_idx3 in number default 0,
p_idx4 in number default 0 ) return number
is
return g_array( idx_of(p_idx1,p_idx2,p_idx3,p_idx4) ); end;
procedure setval( p_value in number,
p_idx1 in number default NULL,
p_idx2 in number default NULL,
p_idx3 in number default NULL,
p_idx4 in number default NULL )
is
g_array( idx_of(p_idx1,p_idx2,p_idx3,p_idx4) ) := p_value; end;
procedure remove
is
l_empty nArray;
begin
g_array := l_empty;
end;
end;
/
gives you a single "up to 4 dimensional" array using a single dimension array. You can use it like this:
set serveroutput on size 1000000
declare
dim_1 number default 19;
dim_2 number default 8;
begin
my_array.dim( dim_1, dim_2 );
for i in 1 .. dim_1 loop
for j in 1 .. dim_2 loop
my_array.setval( i*j, i, j );
end loop;
for i in 1 .. dim_1 loop
for j in 1 .. dim_2 loop
dbms_output.put_line( 'my_array.getval('||i||','||j||') = ' ||
my_array.getval( i, j ));
end loop;
-- 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 CorpReceived on Wed Oct 03 2001 - 09:14:02 CDT
 |
 |