two dimensional arrays in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: two dimensional arrays in PL/SQL

  1. #1
    Join Date
    Jun 2001
    Location
    Maryland
    Posts
    3

    Angry

    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
    Bob, Eater of Squid

  2. #2
    Join Date
    Jun 2001
    Location
    Maryland
    Posts
    3
    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!
    Bob, Eater of Squid

  3. #3
    Join Date
    Apr 2001
    Posts
    118
    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

  4. #4
    Join Date
    Jun 2001
    Location
    Maryland
    Posts
    3
    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
    Bob, Eater of Squid

  5. #5
    Join Date
    May 2012
    Posts
    1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width