How should date-based queries in Oracle be parameterised

Question

ResultSet rs=st.executeQuery(
    "select j.vc_jo_no,
            j.dt_jo_date,
            p.vc_product_name 
     from mst_jobcard j,
          mst_prod p 
     where j.vc_product_code=p.vc_product_code 
     and   j.dt_jo_date=to_char("+tdate+","+"'"+dd-mm-yy+"'"+")
    ");

In my specified query it should display the records based on the date parameter that is being passed in the above query. the vc_jo_no,dt_jo_date are taken from mst_jobcard table and vc_product_name is taken from mst_prod table. i have joined the tables. please help me in how to use the to_char function for date. when i specify the format i.e dd-mm-yy in the to_char function it gives error. please help..

Answer 1

So you're running:

executeQuery("select … where … j.dt_jo_date=to_char("+tdate+","+"'"+dd-mm-yy+"'"+")");

First thing, try put that string in a variable, output it and see if it's in the correct format.

Have you tried:

"… to_char("+tdate+",'dd-mm-yy'")

it seems you're doing unnecessary acrobatics with that string.

And finally, take a look at "Oracle to_char usage" by Burleson Consulting. It was the first link off of Google. Some say Google's a good place to look first.

Answer 2

If you're going to pass the date as a string Oracle needs it surrounded by single quotes. Also, the "dd-mm-yy" doesn't look right to me. Try this:

ResultSet rs=st.executeQuery(
"select j.vc_jo_no,
        j.dt_jo_date,
        p.vc_product_name 
 from mst_jobcard j,
      mst_prod p 
 where j.vc_product_code=p.vc_product_code 
 and   j.dt_jo_date=to_char('"+tdate+"','dd-mm-yy')
");

That said, this method of passing parameters is effectively NOT passing the parameter - it will cause the database to parse a different query for every different date requested, which is likely to cause a scalability issue.

The better approach would be to bind the parameter, using whatever method is provided by ResultSet to bind a variable. You may find you can even bind a date variable natively without having to convert it to a string.