Hierarchical Stage Output To Oracle Table

adamfrank321 · Post by **adamfrank321** » Fri Dec 04, 2015 3:30 am

Hi - I'm working with the new Hierarchical Stage in 11.3. I'm generating valid XML files for a small subset of the data but, given the large volume of files I will be creating for the full dataset, I'm looking into dumping the XML I generate into an Oracle table.

The documentation says that I can choose "Large Object" for my output from the Hierarchical Stage so that a downstream process can use it as long as the last stage is "LOB-Aware". Most of the DB connectors are LOB-Aware including the Oracle connector I'm using.

Unfortunately, this is where the documentation stops. It doesn't give the slightest hint on how to actually load any of the data passed by the Hierarchical Stage into Oracle.

I've found bits and pieces elsewhere online... like I believe I need to use a XML locator string or something like that...

Has anyone used the stage in this fashion and can point me in the right direction for dumping this data into a table so I can create what files I need after the fact?

ANY and ALL help is appreciated as I'm stuck like chuck at this point.

Cheers.

eostic · Post by **eostic** » Fri Dec 04, 2015 6:42 am

....there are two things happening in your question....you may need to deal with both, as in your question, but it is worth qualifying this further....the problem you are trying to solve may not be as complex as it looks --- meaning, you may not need the LOB capability here...

1. you mentioned using Oracle because of the large "volume" of files......

2. you mentioned looking into using the "large object" capability.

These can be related, but often are not. If you are creating a large number of xml documents (individual files) and want to store them in individual rows of an Oracle database, great! ...how big is a "single" one? For example, perhaps you have 10,000 xml documents being generated, one per row for a particular Oracle key. If the documents aren't that large, a longvarchar in Oracle might suffice.

Use the "LOB" capability when the size of a SINGLE column (a single output document) being sent to Oracle is bigger than a column in DataStage or in Oracle can handle.

As for actually using the LOB if needed, search and look thru other Oracle and especially MQ threads, where people have to deal with LOBs more frequently and may have experiences to share.

Ernie