CODEWITHSUNDEEP
javaoracle-databasefunctionpackage
Travis
Travis

Reputation: 1095

String found, numeric expected in Oracle function called from Java

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

Upvotes: 1

Views: 399

Answers (3)

Sameer Sawla
Sameer Sawla

Reputation: 729

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.

Upvotes: 1

rgettman
rgettman

Reputation: 178253

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";

Upvotes: 1

erencan
erencan

Reputation: 3763

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)

Upvotes: 1

Related Questions