Associative arrays in Oracle PL/SQL as parameters, use with Java

[googledude]

Is it possible to pass Oracle associative arrays as PL/SQL parameters ?
I have basically gone through a bunch of articles on Oracle's site,
google, search engine. I have yet to find any decent examples of using
associative arrays as parameters.

I am shocked/surprised that a company of the size of oracle has such
poor search tools on their web site with tons of irrelevant
information. I also tried to look through the O'Reilly books on PL/SQL
(3rd edition). The book has no contextual coverage on my problem (it is
loaded with elementary type information and examples). As you can tell,
I am frustrated and can't find any in-depth coverage on this issue.

1. Can I use associative arrays (specifically the ones indexed by
varchar2) as parameters between various pl/sql functions/procedures ?

2. What, if any, corresponding data structures and constructs can be
used on the java side to map to associative arrays (NO, i am not
interested in using varrays and/or nested tables).

3. Anyone care to share any examples that use these features ?

Thanks humbly for your help in advance!

 

[Sybrand Bakker]

Is it possible to pass Oracle associative arrays as PL/SQL parameters ?
I have basically gone through a bunch of articles on Oracle's site,
google, search engine. I have yet to find any decent examples of using
associative arrays as parameters.

I am shocked/surprised that a company of the size of oracle has such
poor search tools on their web site with tons of irrelevant
information. I also tried to look through the O'Reilly books on PL/SQL
(3rd edition). The book has no contextual coverage on my problem (it is
loaded with elementary type information and examples). As you can tell,
I am frustrated and can't find any in-depth coverage on this issue.

1. Can I use associative arrays (specifically the ones indexed by
varchar2) as parameters between various pl/sql functions/procedures ?

2. What, if any, corresponding data structures and constructs can be
used on the java side to map to associative arrays (NO, i am not
interested in using varrays and/or nested tables).

3. Anyone care to share any examples that use these features ?

Thanks humbly for your help in advance!

1 Anything with a type definition can be passed within pl/sql

2 that I don't know.

 

[Mark Bole]

1 Anything with a type definition can be passed within pl/sql

2 that I don't know.

PL/SQL associative arrays are relatively new, only since version 9.2,
which might be why examples are not abundant.

Not a Java programmer, but java.util.Hashtable class ought to get you
started on question 2.

http://java.sun.com/j2se/1.4.2/docs/api/java/util/Hashtable.html

-Mark Bole

 

[DA Morgan]

Is it possible to pass Oracle associative arrays as PL/SQL parameters ?
I have basically gone through a bunch of articles on Oracle's site,
google, search engine. I have yet to find any decent examples of using
associative arrays as parameters.

I am shocked/surprised that a company of the size of oracle has such
poor search tools on their web site with tons of irrelevant
information. I also tried to look through the O'Reilly books on PL/SQL
(3rd edition). The book has no contextual coverage on my problem (it is
loaded with elementary type information and examples). As you can tell,
I am frustrated and can't find any in-depth coverage on this issue.

1. Can I use associative arrays (specifically the ones indexed by
varchar2) as parameters between various pl/sql functions/procedures ?

2. What, if any, corresponding data structures and constructs can be
used on the java side to map to associative arrays (NO, i am not
interested in using varrays and/or nested tables).

3. Anyone care to share any examples that use these features ?

Thanks humbly for your help in advance!

1. Yes.

2. Don't know but if not you could always move the values to a
ref cursor or array.

3. http://www.psoug.org
click on Morgan's Library
click on Associative Arrays

 

[Mark Bole]

PL/SQL associative arrays are relatively new, only since version 9.2,
which might be why examples are not abundant.

Not a Java programmer, but java.util.Hashtable class ought to get you
started on question 2.

http://java.sun.com/j2se/1.4.2/docs/api/java/util/Hashtable.html

-Mark Bole

Make that class java.util.Map instead, which actually ensures unique
keys. Consider also Perl instead of Java if you have a choice (not
really knowing what you want to do, but if it depends heavily on
associative arrays, Perl is really good at that).

-Mark Bole