topic Re: String to BigDecimal - precision error in Talend Studio

String to BigDecimal - precision error

Anonymous — Fri, 19 Apr 2013 09:22:54 GMT

Dear i have a string to convert to a BigDecimal(10,8) related to an oracle column of number (10,8) type.
In my Tmap i can convert with this "new BigDecimal(row9.conv).setScale(8,BigDecimal.ROUND_HALF_UP)"
But i receive an oracle error
ORA-01438: valeur incohérente avec la précision indiquée pour cette colonne
Any ideas ?
Regards

Re: String to BigDecimal - precision error

Anonymous — Fri, 19 Apr 2013 10:29:13 GMT

Hi
For debug, add a tLogRow before tOracleOutput, and check the option 'die on error' on tOracleOutput, print the data on the console and see which data leads to the error.
Shong

Re: String to BigDecimal - precision error

Anonymous — Fri, 19 Apr 2013 10:32:56 GMT

for the data see my print screen (data viewer)
See error below
Exception in component tOracleOutput_4
java.sql.SQLException: ORA-01438: valeur incohérente avec la précision indiquée pour cette colonne
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1062)
Regards

Re: String to BigDecimal - precision error

Anonymous — Fri, 19 Apr 2013 10:48:41 GMT

Hi,
Which talend version do you used? We will take a testing for your case.
Best regards
Sabrina

Re: String to BigDecimal - precision error

Anonymous — Fri, 19 Apr 2013 10:49:53 GMT

Talend Enterprise Data Integration Cluster edition 5.1.1r84309
regards

Re: String to BigDecimal - precision error

Anonymous — Fri, 19 Apr 2013 12:24:40 GMT

Hi,
I have made a testing for the expression, and it is totally correct(see my screenshots).
I suspect something wrong with the DB output, could you give us more information about that? It suggested that use tLogcatcher to catch the java exception from your oracle DB as @shong mentioned.
Best regards
Sabrina

Re: String to BigDecimal - precision error

Anonymous — Fri, 26 Apr 2013 14:54:58 GMT

See my screenShot attached
All oracle number defined as NUMBER(10,4).
You can see my value with two decimal but i still have an error.
regards
Exception in component tOracleOutput_4
java.sql.SQLException: ORA-01438: valeur incohérente avec la précision indiquée pour cette colonne
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1062)
142013-04-26 15:51:28|java.sql.SQLException RA-01438: valeur incohérente avec la précision indiquée pour cette colonne
at datawarehouse.dwh_fact_mre_0_1.dwh_fact_mre.tFileInputDelimited_5Process(dwh_fact_mre.java:4092)
at datawarehouse.dwh_fact_mre_0_1.dwh_fact_mre.tETLMRELog_7Process(dwh_fact_mre.java:1193)
at datawarehouse.dwh_fact_mre_0_1.dwh_fact_mre.tETLMREStart_4Process(dwh_fact_mre.java:4677)
at datawarehouse.dwh_fact_mre_0_1.dwh_fact_mre.tWarn_4Process(dwh_fact_mre.java:8224)
at datawarehouse.dwh_fact_mre_0_1.dwh_fact_mre.tSetGlobalVar_4Process(dwh_fact_mre.java:8090)
at datawarehouse.dwh_fact_mre_0_1.dwh_fact_mre.runJobInTOS(dwh_fact_mre.java:11114)
at datawarehouse.dwh_fact_mre_0_1.dwh_fact_mre.main(dwh_fact_mre.java:10896)

Re: String to BigDecimal - precision error

Anonymous — Sat, 27 Apr 2013 12:33:04 GMT

All oracle number defined as NUMBER(10,4).

Can you please show us the table definition? In your first post, you said the table has a column, number(10,8) type, but now All oracle number defined as Number(10, 4)?
The error indicates inconsistent value with the precision indicated in this column, so I am sure we need to check the precision of the data will be inserted into the number column of table.
Shong

Re: String to BigDecimal - precision error

Anonymous — Mon, 29 Apr 2013 08:41:31 GMT

This is the definition of the table :
CREATE TABLE BAAN_MRE.DW_STOCK_FACT
(
ID NUMBER(8),
ITEM_ID NUMBER(8),
REAL_STOCK NUMBER(10,4),
BLK_STOCK NUMBER(10,4),
ORD_STOCK NUMBER(10,4),
RES_STOCK NUMBER(10,4),
AUD_CREATION_USER VARCHAR2(30 BYTE) NOT NULL,
AUD_CREATION_PGM VARCHAR2(30 BYTE) NOT NULL,
AUD_CREATION_DATE DATE NOT NULL,
AUD_LASTUPD_USER VARCHAR2(30 BYTE) NOT NULL,
AUD_LASTUPD_PGM VARCHAR2(30 BYTE) NOT NULL,
AUD_LASTUPD_DATE DATE NOT NULL
)
I already set the scale of the BigDecimal in my tmap like that :
(tools.translateScientist(inv001.allo)).setScale(2,BigDecimal.ROUND_UP)
This is the code :
public static BigDecimal translateScientist(String val){
BigDecimal myval = new BigDecimal("0");
String someString;
int pos = val.indexOf("e");
if (pos > 0){
someString = val.substring(0,pos) ;
System.out.print(someString);
myval = (new BigDecimal(String.valueOf(someString)).multiply(new BigDecimal("10"))).pow(Integer.parseInt(val.substring(pos + 1)));
}else{
myval = new BigDecimal(val);
}
myval.setScale(2,BigDecimal.ROUND_UP);
return myval;
}
Regards