Oracle PLSQL : Muti-level Nested Tables
Posted on 2011-03-02
I have some complex multi-level nested tables that needs to be handled. Below is the sample structure:
TYPE phone IS RECORD (country_code NUMBER,
TYPE phone_tab IS TABLE OF phone;
TYPE address IS RECORD (address_id number,
TYPE address_tab IS TABLE OF address;
TYPE person IS RECORD (person_id NUMBER,
1) Considering there are 3 tables in the database with data (PERSON_TAB, ADDRESS_TAB & PHONE_TAB), how to create or load the data into the variable v_person?
(I tried with BULK Collect, but costantly getting an error "PLS-00597: expression 'v_person' in the INTO list is of wrong type").
2) In scenarios like some address doesnt have any phone numbers, the address_phones should be NULL. How to assign a complete nested table as NULL within a nested table ?