Map-like object type in PL/SQL?
Solution 1
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.
Solution 2
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; /
Solution 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)
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
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.
-
Admin almost 15 yearsThat is not entirely the same problem. My problem is the hierarchical structure. What I need is a map with not one fixed value type but two, one of them being child maps. In other words, I want to store a map in a map.
-
Admin almost 15 years@tuinstoel see clarification above.
-
Admin almost 15 yearsI think you are right, I am probably on the wrong track here, alltogether.
-
Olivier Refalo over 10 yearsYou didn't get the pb, he wants a type - not tables. He could use this structure to call a SP for instance.
-
vadipp almost 9 yearsIt seems impossible to declare such type globally. I'm getting
PLS-00355: use of pl/sql table not allowed in this context
. If I remove theINDEX BY VARCHAR2(64)
from the declaration, it compiles. -
Vignesh_A almost 4 yearsCan you share how to insert, get , delete , edit values inside this object with an example. It would be much helpful. thanks
-
Jeffrey Kemp almost 4 years@Vignesh_A you could start here: tutorialspoint.com/plsql/plsql_object_oriented.htm