Does a nested table retain order while not being stored?

Question

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. :)

Answer 1

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).

Answer 2

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.