3

I'm puzzled about the correct use of bind variables with dates in Oracle. This isn't within the database or when using PL/SQL, but rather when interacting with Oracle across an OCI interface, where the date needs to be passed in as a string using the to_date function.

I would have thought the right approach to ensure the proper use of bind variables is to do the following:

to_date(:my_date, :my_date_format)

However, I've seen approaches where the date format isn't done using binds, so I'm a little confused.

Can anyone confirm this or suggest the best approach?

Improve this question
2
  • Can you describe what you want to do and what is the error? Commented Jan 26, 2012 at 15:26
  • @FlorinGhita, there's no error. Imagine a web page, where a user chooses a date. It gets passed into a report that returns data based on the value of that date. The data is generated by a SQL query with a WHERE clause that filters on the date. I want to ensure that WHERE clause is using bind variables. Commented Jan 26, 2012 at 15:42

2 Answers 2

Reset to default
2

The answer to your question is it depends...

If you're dynamically creating your date_format then you ought to use a bind variable to make yourself SQL-injection safe. If you're not dynamically creating the date-format then it's already hard-coded and there's very little point.

select to_date(:my_date,'yyyymmdd') from dual

is safe anyway but:

select to_date(:my_date,:my_date_format) from dual

should really be a bind.

This is all assuming that :my_date is not a column, in which case it cannot be a bind variable at all.

If you're binding :my_date though you're passing a static date to Oracle and not using a column then can't OCI work this out for you without going to Oracle ( I don't know for sure, never used it ).

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

Comments

1

Is the date format a constant? Or does it change at runtime?

Normally, you know what format the string is (at least expected) to be in so the date format would be a constant. If something is a constant, it is not necessary to make it a bind variable, it can just be hard-coded as part of the statement. In this case, it wouldn't matter either way but there are cases where you'd rather the value be hard-coded in the SQL statement because you want to give the optimizer more information (think of a column with highly skewed data where you're always looking for a particular hard-coded value).

On the other hand, if the date format changes at runtime because someone is passing both the string representation of the date and the format the string is in to your procedure, it would make sense for the date format to be a bind variable.

Improve this answer

2 Comments

Thanks Justin. That date format won't change. It makes sense what you're saying about hard-coding it. Apart from that though, is using the bind variable with the to_date the right way to go?
@BrianFenton - Bind variables should be used where the data changes at runtime. If the date format doesn't change, I wouldn't use a bind variable for it. In this case, however, it wouldn't matter either way from a functional or performance standpoint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.