View Single Post
  #1 (permalink)  
Old July 1st, 2004, 05:00 PM
manisha.anand manisha.anand is offline
Registered User
Join Date: Jul 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default variable number of fields in record

I have a data file in which the num fields can be different.
"name" numhost "host"
the numhost value decides how many "host" entries are going to be in the record.
"john" 1 "sunhost"
"mary" 2 "sunhost" "sunhost2"

I need to load thhis file in a table in oracle9i.

There are around a million records in the file so i dont want to use the regular perl file processing linewise.

Any insights as to how to achieve this efficiently would be great.

I started thing in terms loading the table using sqlloader and using varrays to collect the hosts .
which is fine , but the final table needs to have the host names separated by the commas, in a single record. and i am not able to think of away of selecting varray data from temp table and concatinating it as a comma separated string and inserting it into the main table.

again the volume of data needs to be taken care of too.

any help would be apreciated.