AnsweredAssumed Answered

Anyone else have problems with E-Business create/update operations?

Question asked by abupp137129 on May 18, 2017

Hello Boomiland,


We are attempting to call an EBS API, which includes over 4 hundred fields in the imported profile.  Many of these fields are not required when making the call, and so have no mapping.  When we run the process with the request profile's "Respect Min Occurs?" option unchecked, we get an 'E' response from Oracle indicating an error.  When we check that option, then we get another error:


Internal Exception: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: number precision too large

ORA-06512: at line 8

In the .dat file found in the atom's data directory, the following data can be found for line 8:


:8 \=> 999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

What we think's happening (see the attached Boomi 1394774628.dat.pmeta file for the full details of the above error) has to do with the handling of fields defined by the API which have default values specified in the Oracle API's declaration. What we have discovered through tracing the call in Oracle is that unless a given field is assigned a value in a Boomi map, the resulting XML document will not include an element for that field, but the resulting PL/SQL code generated by the operation will include a reference to that field, but give a null value, thus resulting in the 'E' response. 


On the other hand, when the "Respect Min Occurs?" option's checked, it causes the operation to try to set value of an unmapped field to the default value found in the field's declaration.  However, in our case this fails because the value is apparently too big for the "Numeric" type is uses internally.


If you're wondering what the point is of a default value that equals the number: 999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000, then I'm told by our Oracle developer it's a common approach used by Oracle as a kind of "flag" that when found by subsequent processing will cause it to be ignored by any validations, and as well as not overwrite existing data.  


I should note that in Oracle this default value is specified as "9.99E125".  When this value is set as the default for the field referred to above as "line 8", then the error which comes back then refers to the next field that also uses this default. S, we do have a work-around, but it's not a very attractive one because the are literally hundreds of fields which would then have to have their default values set manually in the map.


Are we the only Boomi users which have run into this problem?  We strongly suspect that this problem is a bug in the Boomi E-business implementation.  


If anyone has any suggestions for a way to fix this without manually setting hundreds of default values or writing a wrapper for the Oracle API which handles the null values for these fields, that would much appreciated.  We have tried changing the problematic fields from "Numeric" to "Character" type, but this did not work, since apparently the field's Oracle definition overrides that setting when populating the SQL bind values.