Home » SQL & PL/SQL » SQL & PL/SQL » Associative array as type
Associative array as type [message #226311] Fri, 23 March 2007 04:26 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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);
This can be used in any procedure/function without the need to declare it inside of a package.


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 #226329 is a reply to message #226311] Fri, 23 March 2007 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe this is what you want:
SQL> CREATE TYPE element_map IS TABLE OF NUMBER;
  2  /

Type created.

Regards
Michel
Re: Associative array as type [message #226332 is a reply to message #226328] Fri, 23 March 2007 04:58 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You first example is a nested table
While the other is an index-by table,which you cannot declare global.
Re: Associative array as type [message #226333 is a reply to message #226329] Fri, 23 March 2007 04:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #226355 is a reply to message #226335] Fri, 23 March 2007 06:07 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Well I think that Frank gave you a good initial answer.

Take a look at this
Re: Associative array as type [message #226609 is a reply to message #226333] Sun, 25 March 2007 17:57 Go to previous message
William Robertson
Messages: 1640
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.
Previous Topic: about date
Next Topic: "function in package return array used in SQL" error ..
Goto Forum:
  


Current Time: Mon Dec 05 03:06:11 CST 2016

Total time taken to generate the page: 0.05824 seconds