Oracle sql: using bind variable for dates

Question

Here is a simple working query without bind variables:

select * from table1 where time_stamp > sysdate - INTERVAL '1' day;

where time_stamp is of type DATE.

I should be able to input any number of days in the above query using bind variable.

So I tried the following and does not seem to work:

select * from table1 where time_stamp > sysdate - INTERVAL :days day;

I tried entering the numeric input both as 10 and '10',for eg. You get ORA-00933 error on 10g.

Dave Costa · Accepted Answer · 2010-05-13 19:05:09Z

10

The string INTERVAL '1' day in your original query is an interval literal, i.e. it is evaluated by the parser to a single value. You can't replace part of it with a bind variable.

If you instead use NUMTODSINTERVAL( 1, 'DAY' ), then 1 is an integer literal which you should be able to replace with a bind variable.

answered May 13, 2010 at 19:05

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

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

Collectives™ on Stack Overflow

Oracle sql: using bind variable for dates

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related