AnsweredAssumed Answered

Data insert error not being picked up by try catch.

Question asked by allan.ford577519 on Mar 31, 2017
Latest reply on Apr 11, 2017 by allan.ford577519

Data insert error not being picked up by try catch.

Has anyone else had this issue ?

 

I.e. data flowing to the Insert object, no errors, but no data being inserted ...

 

Relates to dates and date formats ..

Insert example statement generated by BOOMI is as per this, captured via SQL written to file option ..

 
INSERT INTO OFM.WELL_CMPLTN_DAILY_PROD (ALLOCATED_GAS_VOLUME, PRODUCTION_DATE, WELL_COMPLETION_S ) VALUES (101787.299,'20161130 000000.000',700027531);


Oracle DB Error message is :
INSERT INTO OFM.WELL_CMPLTN_DAILY_PROD (ALLOCATED_GAS_VOLUME, PRODUCTION_DATE, WELL_COMPLETION_S ) VALUES (101787.299,'20161130 000000.000',700027531)
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.

 

 

 

Error Message(want Boomi Try to catch Oracle DB insert error as per above text)
Steps TakenHave tried both "Document Errors" and "All Errors" in try config
Business Impactstruggling to build Boomi process ..

 

(I have raised a support ticket also .. Will advise if solution ..)

(I will try some stuff re. date formats ..)  

 

I did get this to work using dd-MON-yyyy format in input value..

e.g.

INSERT INTO OFM.WELL_CMPLTN_DAILY_PROD (ALLOCATED_GAS_VOLUME, PRODUCTION_DATE, WELL_COMPLETION_S ) VALUES (101787.299,'30-NOV-2016',700027531);

 

 

Maybe I should switch to use a "Standard Insert / Update / Delete" and code insert exactly as I want it (and cater for date format as per what is desired also) ... i.e. with ? for in coming field values ..  rather than "Dynamic Insert" ?   I will try this ..

 

Do people use "Standard Insert / Update / Delete" or "Dynamic Insert" ?

 

What have been your experiences ?   issues with date formats and "Dynamic Insert" usage ?

 

Outcomes