Creating and Using Array in ORACLE

[Hexcot]

Hi,

Can anyone tell me how to create a two dimensional ( or n - dimensional array ) in ORACLE. ?

Actually here is the brief, why i want to create it.

I am writing a ORACLE procedure, which returns a tabular structure of records. Let say (80 row X 8 column) table.

Now, First 10 row for all 8 column data is used for the rest 70 rows calculation. This 10 row's data are populating using functions.

So I want to store all the 10 row X 8 Column = 80 Cell data in to a array, and I want to use this 80 cell data for the rest calculation. This will reduce the frequence call of the same function.

Can any one tell me how to get create/use this two dimensional array, so I will call all the function one time and fill the array with value. Later on I will use the array for calculation of rest of the cell-calculation.

I read that something call VArray and PL/SQL Table.
I do not know how to create/use VArray in 2-dimensional form ?
If i use PL/SQL table then I can not have instant retrival of cellvalue using row-index and col-index.

Pls some suggestion..

Thanx in advance for help..

 

[sem]

type tEmpList is table of emp%rowtype index by binary_integer;


This is a declaration of an array of rows, I think it's enough for your specific task.

You may declare a variable of this type:

vEmpList tEmpList;

You may reference specific cell as

vEmpList(index).empno

Though you can not reference that cells "by position"

BTW, how does your procedure return "tabular structure of records"? And what application are you going to use for the "rest of the cell-calculation"?

Regards, Dima

http://seminihin.narod.ru