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.

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch