Re: Arrays in PL/SQL

On Mon, 11 Aug 1997 15:11:24 -0600, ualwayn_at_usgs.gov wrote:
>Can anyone please tell me how to declare and access a two-dimensional
>array in PL/SQL?
>
>Any help will be appreciated.

This depends on what version of Oracle you're running. Prior to version 7.3 you can't do this:

	TYPE Two_Element_Rec_Type IS RECORD
	(element1        datatype,
	 element2       datatype);

	TYPE Array_type IS TABLE OF Two_Element_Rec_Type
	INDEX BY BINARY_INTEGER;

	MyArray    Array_type;

Voila! A two-dimensional array-like structure. In Oracle8, you can do better than this if you need to by using a VARRAY, which allows you to inherently limit the number of elements in the "array".

        TYPE Array_type IS VARRAY (10) OF Two_Element_Rec_Type;

        MyArray Array_type;

Of course, you can also use the former example in Oracle8, although this is no longer a PL/SQL table -- it's now a TABLE type.

Once the variable is created, it is accessed like this:

        MyArray(5).element2 := <some value>;

Pretty simple. I don't know who at Oracle decided that using parentheses was better than using square brackets like other languages use; I always want to use square brackets and then have to fix the compile errors.

There are numerous examples of this type of thing in my book, High Performance Oracle Database Automation.

Jonathan Ingram Received on Tue Aug 12 1997 - 00:00:00 CDT