multi dimensional tables (arrays)

[jad]

is there any way of creating a multi dimensional array in oracle 8 pl/sql ...

like:

type an_array is table of integer index by binary integer;
type bn_array is table of an_array index by binary_integer;

if not is there a way around it?

Thanks

 

[carp]

You can define a record type and then create a PL/SQL table of records.

 

[jad]

type AN_Index is table of integer index by binary_integer;
type AN_Record is RECORD (
IND AN_Index
);
type BN_Index is table of AN_Record index by binary_integer;

comes up with a

PLS-00507: a PLSQL Table may not contain a table or a record with composite fields

any help?

 

[jee]

You tried to declare a table of records made up of tables. What you want is simply a table of records. Try this:

TYPE AN_Record IS RECORD (
        IN1 NUMBER
        IN2 NUMBER
        IN3 NUMBER
    );
TYPE AN_Index IS TABLE OF AN_Record INDEX BY BINARY_INTEGER;

 

[jad]

but then it isn't exactly multi-dimensional ... i've got about 80x90 ...

a lot of them are nulls, but the range is complete ...

 

[jee]

That's true - PL/SQL just doesn't support multi-dimensional arrays - because you don't usually run into the need for those when dealing with a relational database. What you have are rows (records) and columns (fields) - and the TABLE and RECORD constructs in PL/SQL allow you to deal with those things. I know that this no help when your situation is different from what is "usual". You could look into using collections and/or nested tables, but these are still basically 1-dimensional.

 

[jad]

i think i'm gonna have to hash the 2 id's together ...

select power(10,length(max(an_id))+1) into v_multiplier from an_table;

for each_row in c_cursor loop
v_hash := each_row.bn_id * v_multiplier + each_row.an_id;

...

end loop;

ugly, but it'll probably work out ok. as long as i never have more than a total of about 9 digits in the 2 keys ... and i think i'll leave the company and travel to mars before then.