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

One Star

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

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.SQLDataExceptionSmiley SurprisedRA-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)
Seventeen Stars

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

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).
One Star

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

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
One Star

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

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog