String found, numeric expected in Oracle function called from Java

Question

EDIT

Putting this here in case it helps others. My problem was a failure in understanding how PreparedStatement works. I had believed that the "?"...setInt() syntax was a simple substitution that constructs an SQL statement, interprets it and sends that to the DB, which is not the case. The answers below explain in detail the problem.

ORIGINAL QUESTION

I'm having some trouble getting an Oracle package function call to execute from within a Java app. I am receiving the below error:

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

I believe I have constructed the call correctly, and the only place I'm using a string is for a date field (not a numeric one). The function has the signature:

function f_get_mr_target(a_plan_id number,a_machine number,a_shift_id number,a_shift_dt date) return number;

My java code invoking the function is as follows:

public Double checkMRTarget(int planId, int machineNum, int shiftId, String date)
{
    //Instantiate the return value
    Double mrTarget = null;

    //Get the MR target
    try
    {
        //Ready the connection
        con = nativeDataSource.getConnection();

        //The query string
        String sql = "select pkg_bcs.f_get_mr_target(?,?,?,?) target from dual";

        //Prepare the query
        stmt = null;
        stmt = con.prepareStatement(sql);
        stmt.setInt(1, planId);
        stmt.setInt(2, machineNum);
        stmt.setInt(3, shiftId);
        stmt.setString(4, date);

        //Execute the query
        ResultSet rs = stmt.executeQuery();

        //Extract the value from the result set
        mrTarget = rs.getDouble("target");
    }
    catch (Throwable e)
    { 
        System.out.println("Error getting mrTarget: " + e); 
    }
    finally
    { closeDBConnections(); }

    //Return the value
        return mrTarget;
}

Con is a public Connection object shared by all other methods in the class. Stmt is a PreparedStatement object, also shared. The parameters are passed as follows:

planId = 986548
machineNum = 5227
shiftId = 10
date = "trunc(sysdate)"

I've verified that running

select pkg_bcs.f_get_mr_target(986548, 5227, 10, trunc(sysdate)) target from dual;

works just fine in SQLDeveloper. As far as I can tell, it's getting a number where it expects a number

What makes you think you can pass the string trunc(sysdate) to a parameter that expects a Date type? Parameter substitution is strongly typed, you cannot just pass arbitrary SQL text for a Date parameter. — Jim Garrison
– Jim Garrison, Commented Dec 3, 2013 at 17:17
It was evidently a misunderstanding of how the PreparedStatement works. Thanks. — Travis
– Travis, Commented Dec 3, 2013 at 17:22

rgettman · Accepted Answer · 2013-12-03 17:19:07Z

1

You've called setString, so Java sent a String that Oracle can't implicitly convert into a DATE.

You can convert it to a java.sql.Date, java.sql.Time, or java.sql.Timestamp by first parsing the date with a SimpleDateFormat, and creating the appropriate object, and calling setDate, setTime, or setTimestamp instead of setString.

Alternatively, you can get Oracle to convert it by calling to_date in your JDBC SQL:

// Your date format may vary.
String sql = "select pkg_bcs.f_get_mr_target(?,?,?,to_date(?, 'YYYY-MM-DD')) target from dual";

answered Dec 3, 2013 at 17:19

rgettman

179k30 gold badges282 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

erencan · Accepted Answer · 2013-12-03 17:15:18Z

1

the 4th parameter which is date do not work with String. It waits for a Date object.

Here is your method signature, pass a Date object instead of String.

public Double checkMRTarget(int planId, int machineNum, int shiftId, Date date)

answered Dec 3, 2013 at 17:15

erencan

3,7935 gold badges34 silver badges52 bronze badges

Comments

Sameer Sawla · Accepted Answer · 2013-12-03 17:24:04Z

1

Substituting a string datatype to a Date parameter is a catastrophe. It will surely the ORA-01858 exception in this case (in context of you code).

Parameter substitution demands exact binding which serves its purpose of being strongly typed. Please convert the fourth parameter i.e. the string parameter into a date object. & then implement what you wish to. It should work fine then.

Also, trunc(sysdate) in SQL query does not return a string to the SQL client. Rather it returns the date(internal conversion). This is designed such that the parser recognize the date type efficiently and consistently.

answered Dec 3, 2013 at 17:24

Sameer Sawla

7396 silver badges21 bronze badges

Collectives™ on Stack Overflow

String found, numeric expected in Oracle function called from Java

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related