Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Initializing Nested Table Of Records Type
Vidhu
post Dec 25 2016, 06:07 AM
Post #1


Advanced Member
***

Group: Members
Posts: 42
Joined: 11-March 15
From: India
Member No.: 50,402



Hi,

It's known that in order to assign values to nested table we need to allocate it and then initialize it as shown below

TYPE nestedtbl_test1 IS TABLE OF VARCHAR2(25);

nestedtbl_elements1 nestedtbl_test1 := nestedtbl_test1();

BEGIN
nestedtbl_elements1.extend;
nestedtbl_elements1(1) := 'element1';
nestedtbl_elements1.extend;
nestedtbl_elements1(2) := 'element2';
END;

However when I tried to create a nested table of record type as shown below, I didn't have to create a constructor in order to initialize it when i used cursor to fetch the records.

TYPE bulk_rec_type IS RECORD (
emp_id employee.emp_id%TYPE,
dept_id employee.dept_id%TYPE
);
TYPE bulk_select_table IS TABLE OF bulk_rec_type;
v_bulk_select bulk_select_table;

cursor records is

select emp_id, dept_id from employee;

BEGIN
OPEN records;
LOOP
FETCH C_BULK_SELECT BULK COLLECT INTO v_bulk_select

Can somebody explain, why in this case I didn't have to create a constructor, while in the first example I'd had to.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Dec 26 2016, 06:23 AM
Post #2


Advanced Member
***

Group: Members
Posts: 57
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Mate,

You must always initialize the collection types and EXTEND them to allocate space for storing the values. But when you use BULK COLLECT, Oracle internally takes care of this and that is why you dont have to explicitly perform those actions.

Hope this helps!


Thank you,

Boobal Ganesan
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd January 2017 - 03:00 AM