CODEWITHSUNDEEP
oracle-database
Tech guy
Tech guy

Reputation: 19

Confusion regarding Nested Table Extend in oracle 10g

It might v very basic question but i just got confused with usage of Nested table in oracle 10g.. Do we really need to initialize them and extend before inserting or it is not required in 10g version. I was reviewing some old code and found that they are declaring Nested table in a package and then inserting values in them using bulk collect and there is no initialization(constructor) or extend method used before inserting values in nested variable.. The piece of code is working correctly. Please confirm what i am missing here?

Upvotes: 1

Views: 692

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132590

You do not need to initialise or extend a nested table when using bulk collect, but you do need to when explicitly adding rows in PL/SQL code.

For bulk collect:

declare
   l_tab some_table_type;
begin
   select a, b, c
   bulk collect into l_tab
   from mytable;
end;

For PL/SQL:

declare
   l_tab some_table_type;
begin
   l_tab := some_table_type(); -- Initialise before use
   for r in 
   ( select a, b, c
     from mytable
   )
   loop
      l_tab.extend(); -- Extend before adding row
      l_tab(l_tab.count) := r;
   end loop;
end;

I guess you could say that bulk collect does the initialising and extending for you.

Upvotes: 2

Related Questions