How Do i Load Data into a Nested Table in Oracle?

rachit82 · Post by **rachit82** » Thu Oct 23, 2008 1:46 pm

I need to load data into a nested table in Oracle. The columns are:

Key A B C D

The key column is the only normal column. A,B,C,D are all objects and are part of the nested table.

I contacted IBM and they said DataStage 7.5.2 cannot do this. Object Based Database concepts are atleast 10yrs old. So there must be someone who must have worked on this.

When i do a view data, Oracle OCI gives error msg that unknown data types. DataStage column definitions also consider the Objects in Each CompleX Column as Unkown Data Types.

Please advice.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 23, 2008 3:31 pm

Welcome aboard. DataStage is also more than 10 years old. How would you load data into a nested table using sqlplus? Use the same SQL as user-defined SQL in an OCI stage. View Data will almost certainly not work, for the same reasons that a standard SELECT statement in sqlplus will not work. There are "tricks" to extracting from nested tables (unless you use a database that more fully understands the nested table model, such as UniVerse or UniData).

rachit82 · Post by **rachit82** » Thu Oct 23, 2008 3:58 pm

Thats the funny part. My Source Database is UniVerse. I am trying to put the denormalized data into Oracle Nested Tables to resolve the normalization problem. If i do the normalization, i am blowing up 28 million records to 133 million records in Oracle. The Load time is 24 hours and i need options.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 23, 2008 4:45 pm

I'm surprised that 133 million records are taking that long. How are you loading them? Any reason not to use sqlldr (Oracle bulk load stage)?

Do the data have to be normalized into Oracle, or would you be happy with, say, comma-delimited strings? In that case simply convert the value marks of multi-valued fields into commas, either during extraction or using a Convert() function in DataStage.

rachit82 · Post by **rachit82** » Thu Oct 23, 2008 7:13 pm

DataStage server does not have enough space for Staging tables for sql loader. Plus one of the old timers read somewhere that Oracle may do away with SQL Loader and he wont sign off on my project for that reason.

I am normalizing the data on datastage server and have changed the minimum modulus and large record size for optimum performance. But OCI stage can do only this much. i feel as if i am hitting a brick wall.

I did put the data into ORacle without normalizing with * instead of VM. But i could not go forward and normalize it. This data is crucial as it is coming from the transactional database and downstream applications are heavily dependent on this data.

chulett · Post by **chulett** » Thu Oct 23, 2008 8:03 pm

rachit82 wrote:Plus one of the old timers read somewhere that Oracle may do away with SQL Loader and he wont sign off on my project for that reason.

That's just crazy talk.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 23, 2008 9:42 pm

If Oracle do do away with sqlldr it will only be to replace it with something faster/better. Would you like us to start some counter-rumour that you can challenge your old-timer with - maybe that no-one over 35 will be permitted to work in IT after 2012 so as to guarantee jobs for young people?

rachit82 · Post by **rachit82** » Fri Oct 24, 2008 4:17 am

I asked IBM and they said DataStage cannot load data into nested tables. If we need this functionality, we need to upgrade to 8.1. So that settles the matter. They did not seem open to any work around or patches. I am going with the SQL Loader and i have gathered some support from the DBAs. We should be fine.

Thanks a lot for the inputs.