Stef Heyenrath Stef Heyenrath - 1 month ago 10
SQL Question

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;

Answer

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