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. :)
First of all you should know, what is
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).
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.