Error loading longvarchar to oracle clob

[rcanaran]

viewtopic.php?t=130465
viewtopic.php?t=112286

I've already looked at the 2 above.

Error message is :
oreLoadTable,0: Array execute failed for insert:
insert is: INSERT
INTO
BAL_RECON
(BAL_RECON_ID, SRC_TYP_CD, SRC_TXT, SUBJ_TYP_CD, SUBJ_SUB_TYP_CD, OBJ_ROOT_ID, REC_STATE_CD, SRC_SYS_TYP_CD)
VALUES
( :BAL_RECON_ID, :SRC_TYP_CD, :SRC_TXT, :SUBJ_TYP_CD, :SUBJ_SUB_TYP_CD, :OBJ_ROOT_ID, :REC_STATE_CD, :SRC_SYS_TYP_CD)
sqlcode is: -1003
esql complaint: ORA-01003: no statement parsed

I am sending an xml message into SRC_TXT. The source XML is in a LongVarchar with a length of 100000000 (100M).

Stage preceding the Oracle Enterprise Stage is a transformer.

SRC_TXT is a CLOB in an Oracle table.

I believe the longest message is currently about 500-600 Kbytes. I have no problem if I change the longvarchar length to about 750K. If I set it to 1 million or above, I get the stated error. I've even tried leaving the longvarchar unbounded, but I still get the error.

The documentation says that Longvarchar is the equivalent of a CLOB and that its max size is 2Gb.

Any ideas on how I can get a larger size message to load? There ARE some messages that are 2GB loading via MQ, but if I have to perfom balance an reconciliation on those, then I will need to load larger messages than 500-600 Kbytes, obviously.

[rcanaran]

Is there an option, perhaps in "advanced connection options" or in the DS server or project that needs to be set/updated for the DS-to-Oracle connection to handle CLOBs correctly ?

[rcanaran]

According to the IBM support rep, accessing a CLOB via the Oracle EE Stage is NOT supported.

They suggested I try the ODBC or DRS Stage. I tried the DRS Stage with the DBMS set to "Oracle" and this worked very well. :D

Apparently this is posted in an APAR, but I don't even know wheter or not I have access to the APARs for DataStage.

[chulett]

Interesting that it works via the DRS stage set to Oracle, when that allegedly uses the same native drivers that an Oracle stage would use. But hey, working is working.

Please mark your post as Resolved using the button at the top of the page.