2 dimensional array in pl/sql

[dbasupuser]

Have anyone used 2 dimensional array in pl/sql and could you please provide the usage. How to declare and assign the values to the array.

[bensr]

It is not possible to do that.
For some purposes, there are work-arounds, but that depends on
what you want.
Please be some more specific.

[dbasupuser]

OK to be more specific:

My input table is

ID | QTY | DATE
------------------
IDx QTx DTx

The output table should look like

Week1 | Week2 |........ | Weekx
--------------------------------------
IDx|QTx|IDx|QTx| .......|IDx|QTx

I don't know how I can do it without using 2 dimensional array and don't want to use pro-c.

I don't expect you to write codes just the approch should be enough if you can think of any other way. The only other way I can think of is creating x number of cursors and then inserting to the table.

[chrisrlong]

Originally posted by dbasupuser
and don't want to use pro-c.

Ugh - why in the world would you?

So, how many weeks do you have to display?

Do you have a table that translates dates to weeks?

What is the ID that you plan to display for each week, since there is an ID for every day?

When you say 'output table', do you simply mean resultset or do you have an actual table?

Provide as many details as possible, please.

- Chris

[bensr]

Here some hints:
The output table should look like

Week1 | Week2 |....... | Weekx
--------------------------------------
IDx| QTx | QTx | .......| QTx


Input
1 25 W1
1 30 W2
1 34 W3
1 18 W4

Be aware of the limits of a pl/sql table(binary_integer):
Its magnitude range is -2**31 .. 2**31

Say start at 1
then store Id 1 in the range 1 - 53
Id 2 in the range 54 - 106
etc.

When you suppose you have more room
then make it easier
then store Id 1 in the range 1 - 99
Id 2 in the range 100 - 199
etc.

Now you can store the values as
my_plsql_table(IDx * 100 + weekx) := QTx;

week := mod(Index,100);
ID := floor(Index/100);

If the output-table is a physical table you have to
map every value to a column anyhow, so you can just do that immediate
and create a pl/sql-table as a output_table%rowtype