Home » SQL & PL/SQL » SQL & PL/SQL » Associative Arrays and nested tables
Associative Arrays and nested tables [message #352752] Thu, 09 October 2008 05:51 Go to next message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Dear Oracle Specialists!

Recently I found out that oracle accepts two ways of defining an Associative Arrays:
For example the follwing type will be accepted:

type emp_number is table of number
index by binary_integer ;

But the same definition is also sucessful without the index.

type emp_number is table of number;

What is the difference between these both syntaxes? Is the second one the definition of a nested table or does oracle have an default index optionally?

I coudn't find anything about it in the documentation.

Thanks in advance for your answer!

Dagmar
Re: Associative Arrays and nested tables [message #352753 is a reply to message #352752] Thu, 09 October 2008 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first one is an associatiave array, it can be indexed by something that is not a number.
The second one is a collection.

See:
Database PL/SQL User's Guide and Reference
Chapter 5 Using PL/SQL Collections and Records

Regards
Michel
Re: Associative Arrays and nested tables [message #352760 is a reply to message #352753] Thu, 09 October 2008 07:05 Go to previous messageGo to next message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Dear Michel,

thanks for the information.

The documentation divides collections into two types: varrays and nested tables. Am I right when I use the term NESTED TABLE for this type?

Best regards!
Smile

Dagmar
Re: Associative Arrays and nested tables [message #352765 is a reply to message #352760] Thu, 09 October 2008 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you are right.

Regards
Michel
Re: Associative Arrays and nested tables [message #352775 is a reply to message #352752] Thu, 09 October 2008 09:16 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
the distinction from a coder's perspective is this:

1) an associative array is a plsql memory construct. Only code inside plsql can see it.

2) nested tables and varrays are database defined collections. SQL can see them readily because their datatype is defined within the database.

The long and short of it is, associative arrays are also called PLSQL ASSOCIATIVE ARRAYS because you will only see them defined indisde plsql code.

Thus sql can't really use them directly. For example, there is no easy way to convert an associative array to a set of rows that sql can select from whereare collections (nested tables and varrays) are native database concepts and sql in Oracle has no issue mapping these to sets of rows via a simple use of CAST.

This basically makes collections queryable and associative arrays not queryable.

Hope this helps. Kevin

Previous Topic: ORA-00936: missing expression
Next Topic: Issue with BULK COLLECT and NO_DATA_FOUND
Goto Forum:
  


Current Time: Sat Dec 10 22:40:33 CST 2016

Total time taken to generate the page: 0.03837 seconds