Unable to insert a record into the table due to {0}

[rameshrr3]

I have a job that loads an oracle Table in Upsert Mode ( Update Then Insert ) . Table has 2 column composite primary key , but no other constraints . The job aborts unexpectedly with an error message

Code: Select all

 Unable to insert a record into the table due to {0}.

The Message ID for this message is :

Code: Select all

 IIS-DSEE-TOUP-00022,

and is not to be seen in the parallel engine message reference PDF book. ( Probably is an error message specific to ora upsert operator )

When I previously encountered this issue & error message with loading another table , I found that it was related to tablespace being filled up and oracle was not able to insert a row ( either from DS or through Oracle SQL ) , and was able to get it fixed by asking for an increase in tablespace size .

However in this case , The SQL insert works fine from the oracle client and DBA confirmed that no tablespace issues exist, but somehow fails in datastage , since i have 75,000 odd rows in the source Dataset , I cannot try to insert them all one by one manually. So Im guessing it must be a bug with an unsupported Oracle error code , and thats the reason why job aborts without a reject happening to the reject link.

Has anyone encountered this so far and worked around it ?

[rameshrr3]

Just to inform this issue has been resolved.

I compiled the job in Trace Mode( with force sequential and peek settings applied to step thru all rows) and ran it , after a succesful processing for 80 odd rows , The log now contained the same fatal error message followed by the peek info message with data in the row causing the failure , along with the most important info - the Oracle SQL Code 12899 ( inserted value too large for column) .

Code: Select all

DS_Rej_PODtl.lnk_toDS_Peek,0: sqlcode:-12899 PO_LINE_NBR:1 ITEM_DESC:INSTALL SUB PANEL FOR ADDITIONAL CIRCUITS. RUN 80 OF 1/2 EMT CONDUIT AND PULL FIVE #12THHN CONDUCTORS LOCATION_KEY: 0000000000000000000000002460.0000000000 QTY_ORDERED: 0000000000000000000000002624.0000000000 QTY_INVOICED: 0000000000000000000000002624.0000000000 QTY_CANCELLED: 0000000000000000000000000000.0000000000 UNIT_PRICE: 0000000000000000000000000001.0000000000 LINE_COMPLETE_FLG:Y VENDOR_PART_NBR:1 COST_CODE:000 UNIT_OF_MEASURE:LO ....<rest of data >

Since it was freeform text field , I put in transformer derivation to truncate it to 100 Characters ( oracle field length) and was able to work around this. The error resolved on running the job after this.
However Im still surprised that I did not have a reject in the reject link with and SQL code for failed insert instead . Does this qualify as a bug?

[jgreve]

Does this qualify as a bug?

sure