Ideas on how to load multi-value fields into Oracle object table
Posted on 2010-11-16
I am looking to design and build some data loading routines, however, the data being loaded has been extracted from a Pick database, which has multi-value fields.
The supplied data will be in a csv file and I'm told there will only be a maximum of 2 levels of multi-valuing. So a particular field may have lots of delimited values, each of which may also have many delimited values. The delimiters will be specific non-printable characters.
The obvious way to load this data would be to define an external table to match the csv file layout, then use pl/sql to split out the multi-value fields, which will be stored in child tables i.e. in relational tables, rather than object tables. The net result will be a row created in a "parent" table for each record in the file and for multi-value fields, a row in a child row, and (for the 2nd level of multi-value) a row in a grand-child table (this will be related to the child, which itself is related to the parent).
On the surface, this looks like a lot of string manipulation and procedural code. Are there any alternative ways of doing this?
I've done a little research and determined that sql*loader will load into nested tables, but I think it will only do this to one level. Unfortunately, external tables don't support nested tables or varrays even in version 11g.