Home » SQL & PL/SQL » SQL & PL/SQL » Creating associative arrays (Oracle DB, 10.2.0, Linux 2.4)
Creating associative arrays [message #343771] Thu, 28 August 2008 02:02 Go to next message
homersimpson
Messages: 3
Registered: August 2008
Junior Member
Hi,

I'm using PL/SQL.
I would like to create data type I call hash, which is an associative array with varchar2(30) keys:
-- This is my hash table, having strings as keys and very long string as values
CREATE OR REPLACE TYPE hash IS TABLE OF VARCHAR2(1000) INDEX BY VARCHAR2(30);

I cannot create this array outside of a package scope, as I get the following error:
Quote:

PLS-00355: use of pl/sql table not allowed in this context


Only if I remove the INDEX BY... code from the declaration of the hash, can I pass compilation without an error. This is not good for me, as I would like to use strings as keys.

My goal is to use this hash as a type for a data-member of another object - how can I achieve that?

Thanks.
Re: Creating associative arrays [message #343774 is a reply to message #343771] Thu, 28 August 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is indeed not allowed.
What do you mean with:
Quote:
My goal is to use this hash as a type for a data-member of another object

Explain it in details.

Regards
Michel
Re: Creating associative arrays [message #343797 is a reply to message #343774] Thu, 28 August 2008 03:37 Go to previous messageGo to next message
homersimpson
Messages: 3
Registered: August 2008
Junior Member
Thanks for the quick response Michel!

I would like to create an object (let's call it foo) that uses the hash type for a data member - let's call it my_hash. I have a function bar that adds items to my_hash and a function bar2 that reads the contents of my_hash.

How can I do that? I tried to define the hash type as part of creating the foo object, but it failed to compile as well.

Thanks again.
Re: Creating associative arrays [message #344002 is a reply to message #343797] Thu, 28 August 2008 10:03 Go to previous messageGo to next message
szogu
Messages: 21
Registered: July 2008
Junior Member
Quote:
Only if I remove the INDEX BY... code from the declaration of the hash, can I pass compilation without an error



Please read here
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#sthref1030

You will get the answer after reading

Regards,
Re: Creating associative arrays [message #344008 is a reply to message #343797] Thu, 28 August 2008 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want a permanent type to be included in table or pass as a parameter of function in a SQL call or do you want a PL/SQL type?

Post an example of what you want.

Regards
Michel
Re: Creating associative arrays [message #344042 is a reply to message #343771] Thu, 28 August 2008 12:23 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are many points of confusion like the one you have here. For example, let me ask a couple of questions:

Quote:
What is the difference between a SQL command and a SQLPLUS command?


Quote:
What is the difference between a PL/SQL datatype and and Oracle RDBMS datatype?


Your issue is of course not knowing the answer to question#2. No big deal, everyone must learn this one way or another. After you grasp the meaning of #2 you will be disappointed that associative arrays are not part of the Oracle RDBMS datatype repetoir. It is just the way it is.

Good luck, Kevin
Re: Creating associative arrays [message #344622 is a reply to message #344042] Sun, 31 August 2008 02:15 Go to previous message
homersimpson
Messages: 3
Registered: August 2008
Junior Member
Thank you all for your replies and kind help.
Smile
Previous Topic: ROW NUM
Next Topic: Estimation Template for PLSQL routines
Goto Forum:
  


Current Time: Thu Dec 08 14:10:32 CST 2016

Total time taken to generate the page: 0.05021 seconds