SQL & PL/SQL

I am using a nested table as column of parent table. I have gone through all examples and SQL*Loader systax diagram to arrive solution but unfornately I could not arrive a correct control file seecification.



<pre>

CREATE OR REPLACE
TYPE CHILD_RECORD AS OBJECT
(
CHILD_NUMBER VARCHAR2(15 CHAR),
CHILD_NAME CHAR(2 CHAR)
);

CREATE OR REPLACE
TYPE CHILD_RECORD_NESTED_TABLE
AS TABLE
OF CHILD_RECORD;


CREATE TABLE PARENT_TABLE (
PARENT_ID NUMBER NOT NULL,
PARENT_NUMBER VARCHAR2(15 CHAR) NOT NULL,
PARENT_FULL_NAME VARCHAR2(300 CHAR) NOT NULL,
PARENT_SHORT_NAME VARCHAR2(35 CHAR) NOT NULL,
CHILD_TABLE CHILD_RECORD_NESTED_TABLE
)

</pre>

sql loader data CSV file consists of

1, 1001. James Adrian Bond, James A Bond, child1, james_child_name
2, 1002. Scott Peter Tiger, Scott P Tiger, child2, scott_child_name


Considering that my child table alway consists of one row as above i need to able to create control
file which helps in inserting the above data CSV file into PARENT_TABLE.

This child table may have more than one row in case of some other set of data.


With my little sql*loader knolwdege I tried using keywords such as TREAT AS, SID, OID to create a control file, but unfornately I am not successful.


Is there any way to load the above CSV file into the PARENT_TABLE?

Thanks for your time in advance

Regards
Charan