Map-like object type in PL/SQL?

Question

Hi there,

I want to write a map-like object type in PL/SQL. What I mean is a key-value-pair list, where a value can be another key-value-pair list. Simple, or so I thought. Here are the two simplified

CREATE OR REPLACE TYPE TKey AS OBJECT
(
    name varchar2(240),
    value_text varchar2(2000),
    value_map TMap
)

CREATE OR REPLACE TYPE TMap AS TABLE OF TKey

Maybe not so simple, because now I have a "Chicken or the egg"-problem. If I put TKey in first, he will complain that TMap is not defined. If I put in TMap first, he will complain that TKey is not defined. If I put TKey in and leave out the value_map line, then add the TMap type, then try to replace the TKey type, he will not allow it.

Am I out of luck? Is such a construct not possible in PL/SQL?

Thanks in advance

Clarification: What I want is something, that can give me this: A map containing the keys a, b, and c. The value of a is the varchar "hello", the value of b is the varchar "world", and the value of c is another map, with the keys x and y, and the value of x being "what's" and the value of y being "up".

This is what it would look like in Java:

Map<String, Object> map = new HashMap<String, Object>();
map.set("a", "Hello");
map.set("b", "World");
Map<String, Object> child = new HashMap<String, Object>();
child.set("x", "What's");
child.set("y", "up");
map.set("c", child);

Now I know that something like "object" as being able to store anything is not possible. What I need is an object that can store a list of objects being of the same type as this object. So basically, a tree, yeah.

Answer 1

You can use associative arrays. From the PL/SQL User guide:

Understanding Associative Arrays (Index-By Tables)

Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique. For example, key values might come from the primary key of a database table, from a numeric hash function, or from concatenating strings to form a unique string value.

For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:

Example 5-1 Declaring Collection Types

DECLARE  TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64);
BEGIN
  country_population('Greenland') := 100000; -- Creates new entry
  country_population('Iceland') := 750000;   -- Creates new entry
-- Looks up value associated with a string
  howmany := country_population('Greenland');
  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value 
-- Returns 'Antarctica' as that comes first alphabetically.
  which := continent_population.FIRST;
-- Returns 'Australia' as that comes last alphabetically.  which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
  howmany := continent_population(continent_population.LAST);
END;
/

Answer 2

Perhaps you need to think more relationally :)

Instead of storing a TMap (value_map) inside the TKey type, just store value_map_name, which can then be used to lookup another entry in the table.

CREATE OR REPLACE TYPE TKey AS OBJECT(
 name varchar2(240),
 value_text varchar2(2000),
 value_map_name varchar2(240));

Then you can use associative arrays (as per mamboking's answer) to store them.

Answer 3

You're trying to squeeze a key-value paradigm into a relational DBMS which doesn't make logical sense. It'd be much easier to go pure relational:

CREATE TABLE key_value AS
(
    key varchar2(240),        -- PRIMARY KEY
    value_text varchar2(2000)
);

CREATE TABLE key_hierarchy AS
(
    child_key varchar2(240), -- PRIMARY KEY, FOREIGN KEY to key_value.key
    parent_key varchar2(240) -- FOREIGN KEY to key_value.key
);

And that's it! If you want to change later on that a child can have many parents, just change the PK constraint (beauty of relational DBMSs)