1

I have a PLSQL function that populates and returns a nested table:

select distinct id bulk collect into my_nested_table
from user
order by id;
return my_nested_table;

According to the docs nested tables are multisets and have no inherent ordering.

Can I nevertheless assume that the nested table returned from the function above will be ordered by id(as the select statement implies) and retain that order as long as I don't store it in the DB?

Providing a link to documentation is a plus. :)

Ravi
  • 30,829
  • 42
  • 119
  • 173
Roland
  • 7,525
  • 13
  • 61
  • 124

2 Answers2

4

First of all you should know, what is NESTED TABLE

According to Oracle Doc

Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows

It's one-column table, which has behaviour of array, but they are unbounded (size can increase dynamically). Moreover, initially NESTED TABLE are dense in nature but later they became sparse (once you remove any element from it).

enter image description here

Ravi
  • 30,829
  • 42
  • 119
  • 173
  • Can I assume that the `bulk collect into` statement will insert into the nested table at consecutive subscripts starting at 1? – Roland Jun 17 '15 at 09:11
  • yes. That is what happened. It will start the subscript from 1. If it was not initialized – Ravi Jun 17 '15 at 09:12
  • 1
    When filling a collection with BULK COLLECT, indices will always start at 1, and the collection will always be dense - regardless of whether it was initialized before or not – Frank Schmitt Jun 17 '15 at 10:55
0

you can relies on order of collection until it holds in pl/sql and does not perform any add or delete element further to collection.

vishnu sable
  • 328
  • 1
  • 7