topic Re: tOracleSP ORA-01858: a non-numeric character was found where a numer in Talend Studio

tOracleSP ORA-01858: a non-numeric character was found where a numer

Anonymous — Mon, 19 May 2014 13:02:47 GMT

Oracle 11g
Talend Version: 5.4.1
Build id: r111943

tOracleSP_2 component calling store procedure from package rdm : rdm.p_dm_load_talend
get error
ORA-01858: a non-numeric character was found where a numeric was expected
Form Oracle (Pl-sql developer)
Declare
Begin
rdm.p_dm_load_talend;
End;
works OK
Previosly I called other Oracle SP OK.
Here Full error messge :
Exception in component tOracleSP_2
java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "TLRRDM.RDM", line 63
ORA-06512: at line 1

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:79)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
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:1030)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:944)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3482)
.-------------------+------+--------+----------+-------+-------+-------+--------+--------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+----.
| tLogRow_1 |
|=------------------+------+--------+----------+-------+-------+-------+--------+--------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+---=|
|moment |pid |root_pid|father_pid|project|job |context|priority|type |origin |message |code|
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3856)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1373)
at tlr.dm_load_0_1.DM_load.tOracleInput_2Process(DM_load.java:2638)
at tlr.dm_load_0_1.DM_load.tOracleInput_1Process(DM_load.java:1837)
at tlr.dm_load_0_1.DM_load.tOracleConnection_2Process(DM_load.java:1267)
at tlr.dm_load_0_1.DM_load.tChronometerStart_1Process(DM_load.java:1113)
at tlr.dm_load_0_1.DM_load.tJava_2Process(DM_load.java:3436)
at tlr.dm_load_0_1.DM_load.runJobInTOS(DM_load.java:7360)
at tlr.dm_load_0_1.DM_load.main(DM_load.java:7102)
|=------------------+------+--------+----------+-------+-------+-------+--------+--------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+---=|
|2014-05-19 14:58:04|e8FdUM|e8FdUM |e8FdUM |TLR |DM_load|PROD |6 |Java Exception|tOracleSP_2|java.sql.SQLDataException RA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "TLRRDM.RDM", line 63
ORA-06512: at line 1
|1 |
'-------------------+------+--------+----------+-------+-------+-------+--------+--------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+----'
Picture attached
I tried - change settings of ToracleSP ( and connection) and package re-compling
Any ideas welcome - both from Talend (or even Oracle side)

Re: tOracleSP ORA-01858: a non-numeric character was found where a numer

Anonymous — Mon, 19 May 2014 13:32:05 GMT

It would be great to see the source code of your stored procedure.
I guess the problem is inside the stored procedure because the Oracle error message points to a line number inside the procedure.
A typical situation to tapp into this trap is the union or a join. If some conditions returns values and your test case does not meet these conditions it is possible to get different types from different sources. A very good example is a product number or a customer number which are often stored as varchar2 or as numeric (the same value with different types in different tables).

Re: tOracleSP ORA-01858: a non-numeric character was found where a numer

Anonymous — Tue, 20 May 2014 12:14:48 GMT

It would be great to see the source code of your stored procedure.
I guess the problem is inside the stored procedure because the Oracle error message points to a line number inside the procedure.
A typical situation to tapp into this trap is the union or a join. If some conditions returns values and your test case does not meet these conditions it is possible to get different types from different sources. A very good example is a product number or a customer number which are often stored as varchar2 or as numeric (the same value with different types in different tables).

Thanks you are rgiht - problem was oin Oracle package (not in Talend)
Constant in package
GL_PACKAGE_DATE CONSTANT DATE := TO_DATE('19-O5-2014', 'DD-MM-YYYY');
contained O instead of 0
in sql-developer it work from 2-nd time
because this constant was not used in called procedure.
But 1-st time it generated Error : ORA-01858
Question is resolved

Re: tOracleSP ORA-01858: a non-numeric character was found where a numer

Anonymous — Tue, 27 Oct 2015 14:13:58 GMT

Hello,
I'm using Talend Open Studio 6.0.
I'm pulling a value of DATETIME format from Mysql table using tMysqlInput component. Further, I'm assigning that value to a context variable using tJavaRow component. After OnComponentOK trigger from tJavaRow component, I pull out values from tOracleInput which are greater than the value I just assigned to the context variable.
When the execution reaches tOracleInput, it is throwing an error-

connecting to socket on port 3997
connected
Exception in component tOracleInput_3
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1231)
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2616)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:653)
    at sd.exe4_0_1.Exe4.tOracleInput_3Process(Exe4.java:2473)
    at sd.exe4_0_1.Exe4.tMysqlInput_1Process(Exe4.java:793)
    at sd.exe4_0_1.Exe4$2.run(Exe4.java:4810)
disconnected
Please let us know if this can be solved anyway or if I am missing something out.