| Associative array as type [message #226311] |
Fri, 23 March 2007 04:26  |
henckel
Messages: 9
Registered: March 2006
|
Junior Member |
|
|
We're trying to create a type in the database (Oracle 9i) which is supposed to work as an associative array or a map of elements.
Example:
635427 -> 12345
983756 -> 98765
435412 -> 65432
...
This type should be defined as a database object in order to use it (in form of a parameter) in different procedures/functions of different packages.
We tried to use the following code to create such a type:
CREATE TYPE element_map IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
Unfortunately, this didn't work:
"Warning: ...compilation errors."
What is wrong here or is there any other way to solve this problem?
Thanks for your help in advance!
Best regards
Sven
|
|
|
|
| Re: Associative array as type [message #226318 is a reply to message #226311] |
Fri, 23 March 2007 04:38  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Quote: | This type should be defined as a database object in order to use it (in form of a parameter) in different procedures/functions of different packages.
|
Not true. You need it as a SQL object if you want to use it in SQL. If used in different packages, you can define it in the specification of a package and refer to it using the package_name as prefix.
What errors do you get?
(show error, or select * from user_errors)
|
|
|
|
| Re: Associative array as type [message #226328 is a reply to message #226311] |
Fri, 23 March 2007 04:53 |
henckel
Messages: 9
Registered: March 2006
|
Junior Member |
|
|
Thanks for your quick reply.
I wonder, why it is possible to create a database type such as:
CREATE TYPE number_list IS TABLE OF NUMBER(19);
But it seems to be impossible to create a type like this:
CREATE TYPE element_map IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
Do you have any ideas why this doesn't work?
|
|
|
|
|
|
|
|
| Re: Associative array as type [message #226333 is a reply to message #226329] |
Fri, 23 March 2007 04:59 |
henckel
Messages: 9
Registered: March 2006
|
Junior Member |
|
|
| Michel Cadot wrote on Fri, 23 March 2007 10:55 |
Maybe this is what you want:
SQL> CREATE TYPE element_map IS TABLE OF NUMBER;
2 /
Type created.
Regards
Michel
|
Thanks for your reply, but this is not what I would like to achieve. With this kind of type the index is restricted and you can only use it as a "normal" array.
|
|
|
|
| Re: Associative array as type [message #226335 is a reply to message #226332] |
Fri, 23 March 2007 05:01 |
henckel
Messages: 9
Registered: March 2006
|
Junior Member |
|
|
| tahpush wrote on Fri, 23 March 2007 10:58 | You first example is a nested table
While the other is an index-by table,which you cannot declare global.
|
Thanks for your reply!
Is there any other way to construct an associative array resp. an element map globally?
|
|
|
|
|
|
| Re: Associative array as type [message #226609 is a reply to message #226333] |
Sun, 25 March 2007 17:57 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
| henckel wrote on Fri, 23 March 2007 09:59 | Thanks for your reply, but this is not what I would like to achieve. With this kind of type the index is restricted and you can only use it as a "normal" array.
|
SQL does not understand sparse arrays. I guess they are not very relational, and that is why associative arrays have not been implemented in SQL. Whatever the reason, in the end it's just a fact of life.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
