Passing Oracle functions as String in Java

Question

I have a query like this..

String query = "UPDATE tbl_customer_policies SET "+
        "start_date = ?," + 
        "next_pay_date = ?,"+
        "maturity_date = ?, " + 
        "modified_at = CURRENT_TIMESTAMP,"+
        "modifier = ?, status = ? " + 
        "WHERE id = ?";

Now in place of the place-holder for start_date I want to pass a string like SYSDATE.

What I am doing now is setting that string in a variable called String startDate = "SYSDATE" and binding it to that place-holder. But I guess it does not seem to work. I get this exception

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

I have to pass Oracle functions like that. How to achieve that?

AFAIK, you can't. It must be in the query itself, and not in its parameters. — JB Nizet
– JB Nizet, Commented Mar 10, 2013 at 8:44
actually i want the db itself to handle date calculations. I am not processing that in java..was trying to execute that logic using db functions only like.. ADD_MONTHS(SYSDATE, 12). With that i didn't have to do that date processing in java. — Praveen Puglia
– Praveen Puglia, Commented Mar 10, 2013 at 8:52

Alex · Accepted Answer · 2013-03-10 08:57:32Z

2

If it will always be sysdate you don't need to parameterize it. Set it directly in the query like this:

String query = "UPDATE tbl_customer_policies SET "+
        "start_date = sysdate," + // or "start_date = ADD_MONTHS(SYSDATE, 12),"
        "next_pay_date = ?,"+
        "maturity_date = ?, " + 
        "modified_at = CURRENT_TIMESTAMP,"+
        "modifier = ?, status = ? " + 
        "WHERE id = ?";

And set all the others parameters like you did before.

answered Mar 10, 2013 at 8:57

Alex

25.7k6 gold badges63 silver badges56 bronze badges

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

2 Comments

Praveen Puglia Over a year ago

it will not be sysdate always. Based on conditions it can change.

Alex Over a year ago

So either you have a set of SQL query that you can change from, or you could maybe crete a stored procedure that can be called from your sql query, but it seems a little overkill..

knagaev · Accepted Answer · 2013-03-11 15:12:22Z

0

Use function TO_DATE (docs) for example

    "UPDATE tbl_customer_policies SET "+
     "start_date = TO_DATE(?, 'DD-MON-RR')," + 
    "next_pay_date = TO_DATE(?, 'DD-MON-RR'),"+

and don't forget pass parameters in format like '27-OCT-98' in this case.

P.S. I misunderstood the question so try to correct my answer.

The described problem is for limitations of PreparedStatement Java class. PreparedStatement object is used for storing a reference to some precompiled (and optimized) SQL statement.
So you have to supply only values of parameters for filling them. Functions must be evaluated before using their results and they aren't able to be placed there.

I guess you can use a workaround with Oracle Structured Types. You can pass them as reference types with PreparedStatement's setRef() method. Using functions can be implemented with a wrapper in constructor of object. I didn't try it but it seems to me it is a possible solution.

edited Mar 11, 2013 at 15:12

answered Mar 11, 2013 at 10:16

knagaev

2,96518 silver badges20 bronze badges

2 Comments

Praveen Puglia Over a year ago

Thats not the answer to my question. Read it again. I know I can pass it using TO_DATE. I am asking whether i can use that function as a string, store it in a variable and pass it.

knagaev Over a year ago

@pjp I'm sorry - I understood that you can't decide the problem with the error ORA-01858. See my second attempt :)

Collectives™ on Stack Overflow

Passing Oracle functions as String in Java

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related