two dimensional arrays in PL/SQL

Is is possible to create two dimensional arrays in PL/SQL? I have tried several strategies to no avail. The last thing I've tried is creating object type of varray's like so:

CREATE OR REPLACE TYPE
tSQL_COL
IS
VARRAY(200)
OF
VARCHAR2(1000);

CREATE OR REPLACE TYPE
tSQL_SET
IS
object
(
SELECT_ITEM tSQL_CO := tSQL_COL(),
FROM_ITEM tSQL_CO := tSQL_COL(),
WHERE_ITEM tSQL_CO := tSQL_COL(),
ORDER_ITEM tSQL_CO := tSQL_COL(),
);

Then in a package I declare:

TYPE SQL_ITEM_SET IS VARRAY(200) OF tSQL_SET;

However the tSQL_SET type is always invalid. Is there another way to create a 2x array or something else I'm missing?

TIA

OK, the object I first tried was coming up "invalid" due to a typo (DOH!) not shown in the example. Once fixed the typo I get:

PLS-00535: A VARRAY type may not contain a NESTED TABLE, VARRAY or LOB

when attempting to create the TYPE in pl/sql which seems to confirm my fear that 2x arrays (dynamic at both levels) are impossible in PL/SQL.

Argghh!

Have you considered modeling this as a table and using a nested table type in your PL/SQL?

If I properly inferred what you are trying to accomplish from your example, I think that you are just wanting to keep track of the information of up to 200 SQL statements. Each individual SQL statement is stored in portions corresponding to the SELECT, FROM, WHERE and ORDER BY clauses and may have up to 200 entries of each.

I would suggest that you define a type similar to the following:


    CREATE OR REPLACE TYPE tSQL_SET AS OBJECT(
        SQL_ID NUMBER,
        SELECT_ITEM VARCHAR2(1000),
        FROM_ITEM VARCHAR2(1000),
        WHERE_ITEM VARCHAR2(1000),
        ORDER_ITEM VARCHAR2(1000) );

    CREATE OR REPLACE TYPE SQL_ITEM_SET AS TABLE OF tSQL_SET;


The SELECT, FROM, WHERE and ORDER items are synonomous to what you had before. A row of these in the table would be the same as one row in one dimension of your original tSQL_COL array.

The SQL_ID column allows you to group these items together, so the new tSQL_SET object is equivalent to your total original tSQL_COL array, without an arbitrary bound of 200 elements.

The new SQL_ITEM_SET type is the same as what you had before, but it does not have an arbitrary bound of 200 elements either, and it doesn't contain nested datatypes that will cause you problems.

HTH,

Heath

Great minds think alike ;)

I ended up implementing it almost exactly as you described, but as a set of VArray of RECORDS. (the records for each type of clause element is slightly different, e.g. selects have aliases) In this way I was able to contain all the type creation in the package itself, no external scripts needed.

I was hoping for dynamic arrays within dynamic arrays, oh well.

Thanks

I know this is an old thread but thanks squidman. Your reply set me on the right track. Except I used a table of records.