ORA-01858: a non-numeric character was found where a numeric was expected

[leonardo ginting]

hi Expert

i have an issue about how to call store procedure from database and i got this error when i passing paramater to the store procedure

ORA-01858: a non-numeric character was found where a numeric was expected

and this is my code

CallableStatement cs = con.prepareCall("{ call myProcedure(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }");
                        cs.setInt(1, variable1);
                        cs.setInt(2, variable2);
                        cs.setInt(3,  new Integer(endSuffix));
//                        cs.setInt(3, 5);
//                        cs.registerOutParameter(3, Types.NUMERIC);
                        System.out.println("Casting endSuffix = " + Integer.parseInt(endSuffix));
                        cs.setString(4, variable4);
                        cs.setLong(5, variable5);
                        cs.setLong(6, variable6);
                        cs.setString(7, variable7);
                        cs.setString(8, variable8);
                        cs.setInt(9, variable9);
                        cs.setString(10,variable10);

                        cs.execute();

and the error pointed at paramater 3 = cs.setInt(3, new Integer(endSuffix));

and this the code from store procedure
create or replace PROCEDURE myProcedure ( param1 IN NUMBER , param2 IN NUMBER , param3 IN NUMBER , param4 IN VARCHAR2 , param5 IN NUMBER , param6 IN NUMBER , param7 IN VARCHAR2 , param8 IN VARCHAR2 , param9 IN NUMBER , param10 IN VARCHAR2) IS x NUMBER; var1 VARCHAR2(20); var2 NUMBER; var3 NUMBER; var4 NUMBER; var5 DATE; var6 NUMBER; BEGIN x := 1; var3 := param3; -- another Code ...

actually i thought that i've setted the parameter as Integer but why could i got this error?

[Martin Vashko]

Please show us the full error message. What exactly "pointed at paramater 3 = cs.setInt(3, new Integer(endSuffix));"? I believe you've misdiagnosed the problem.

The ORA-01858 error message indicates a problem in date/time conversion. You might want to check the code of your stored procedure for these conversions. Furthermore, the error message should contain the PL/SQL stack trace which could point you to the line in your procedure where the error occurred.

[leonardo ginting]

Hi Martin Thanks for replied

any way here is the Exception

Caused by: java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MYDB.MYPROCEDURE", line 24
ORA-06512: at line 1

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
...

so are this exception comes up because error when inserting Date?

[Martin Vashko]

It must be something else. If you were not passing in an int/Integer, the setInt() call in Java would fail.

Could you take the full source code of the procedure right from the database (using eg. the SQL Developer) and post it here?

Also, make sure there aren't multiple copies of the procedure, such as overloaded ones, and that you're not by mistake calling a procedure from another schema than you intend to.

[leonardo ginting]

hi Martin,,

thanks for helping me by the way, and you're right the exception happened because of The Date i've fixed it and it works,,
thanks for Gave me the solution

once more thanks

gb

[Martin Vashko]

You're welcome!