sqloracle-databasevarray

How to define a dictionary like structure in Oracle PL SQL?


How to define in PL/SQL the following structure:

A list of strings which contains multiple rows.

Example:

'User A'
    -->
        (1)
            -->
                1
        (2)
            -->
                2


'User B'
    -->
        (1)
            -->
                0
        (2)
            -->
                9

The integers are defined as:

TYPE number_arry IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

How to define the whole structure ?

I want to fill this structure from this table:

RowId | User_A | User_B  
------+--------+--------
1     |      1 |      0
2     |      2 |      0
3     |      3 |      9

With these statements:

CURSOR c1
        IS
            SELECT User_A, User_B FROM my_table;

OPEN c1;
    LOOP
        FETCH c1
          BULK COLLECT INTO
                my_dict('User A'),
                my_dict('User B')

        LIMIT 1000;

        EXIT WHEN c1%NOTFOUND;
    END LOOP;

Solution

  • You could use the following structure:

    SQL> CREATE TABLE my_table AS
      2     SELECT 1 user_a, 0 user_b FROM dual
      3     UNION ALL SELECT 2, 0 FROM dual
      4     UNION ALL SELECT 3, 9 FROM dual;
    
    Table created
    
    SQL> DECLARE
      2     CURSOR c1 IS
      3        SELECT User_A, User_B FROM my_table;
      4     TYPE number_arry IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
      5     TYPE dictionary_type IS TABLE OF number_arry INDEX BY VARCHAR2(30);
      6     l_dico dictionary_type;
      7  BEGIN
      8     OPEN c1;
      9     LOOP
     10        FETCH c1 BULK COLLECT
     11           INTO l_dico('User A'), l_dico('User B') LIMIT 1000;
     12        EXIT WHEN c1%NOTFOUND;
     13     END LOOP;
     14     CLOSE c1;
     15  END;
     16  /
    
    PL/SQL procedure successfully completed