Skip to Main Content
  • Questions

  • How to use bind variables in date interval functions

Breadcrumb

We're taking a break this week as Connor and Chris will both be at CloudWorld, the premier Oracle conference of 2024. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, Vivek.

Asked: April 28, 2017 - 5:52 pm UTC

Last updated: November 23, 2023 - 11:54 am UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

I am trying to write an sql, that takes two inputs at run time, if I am passing null then it looks at a database table and gets values from it.
To make the NULL checks and then assign values from database, I implemented the logic as its given here https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531682100346333508

But I am not able to use the bind variables in the Date and Interval feilds.

Here is a shorter version of the code
var v_interval varchar2(5)
var v_time varchar2(30)
/

exec :v_interval := '1'
exec :v_time := to_char(systimestamp,'DD-MON-YY HH24:MI:SS:FF')

select :v_time,:v_interval from dual
/

select systimestamp + interval '1' hour from dual
/

select systimestamp + interval :v_interval hour from dual
/

exit


The error I get is 
select systimestamp + interval :v_interval hour from dual
                               *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


Can we use bind variable here, or is it totally wrong?

Thanks a lot for your help.
--Vivek

with LiveSQL Test Case:
https://livesql.oracle.com/apex/f?p=590:1:10950283587850::NO:::

and Connor said...

This is a limitation of SQL Plus, not bind variables in general - the datatypes of the binds are limited:

SQL> variable x date
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]



But you can workaround it with the date/time functions

SQL> var v_interval varchar2(5)
SQL> exec :v_interval := '1'

PL/SQL procedure successfully completed.

SQL> select systimestamp + numtodsinterval(:v_interval,'HOUR') from dual;

SYSTIMESTAMP+NUMTODSINTERVAL(:V_INTERVAL,'HOUR')
---------------------------------------------------------------------------
29-APR-17 11.16.05.771000000 AM +08:00


Rating

  (4 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Vivek, May 04, 2017 - 8:35 pm UTC

Thank You Connor for taking out time and also giving out the the solution, that saved the day for me.

Connor McDonald
May 05, 2017 - 1:45 am UTC

glad we could help

A reader, August 21, 2017 - 9:20 pm UTC

great answer. helped me a lot. thanks!

Pass "sysdate - interval '13' month" as a parameter to PL/SQL code

Joe, November 20, 2023 - 6:02 am UTC

Can you please provide the way to pass 'interval '13' month as parameter to PL/SQL script.

I have a control table which has a column called range_of_time and it stores string 'SYSDATE-INTERVAL '13' month'. This is just for one table entry and i could have different interval time for another table.

I am reading this column value and pass it to PL/SQL script for comparing dates. to filter records older than 13 months for compression or archiving.
 
I was testing this simple PL/SQL script but could not make it work.
Can you please provide how to do this.

--
declare
v_range_of_time VARCHAR2(100);
v_output date;
begin
v_range_of_time := 'sysdate - interval q''[13]'' month' ;
dbms_output.put_line('Range_of_time: '||v_RANGE_OF_TIME) ;
select sysdate - v_range_of_time into v_output from dual ;
end;
/


declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 7

Chris Saxon
November 20, 2023 - 1:27 pm UTC

I'm unsure exactly what you're trying to do here. Using INTERVAL types is probably a good start though:

declare
v_range_of_time interval year to month;
v_output date;
begin
v_range_of_time := interval '13' month;
dbms_output.put_line('Range_of_time: '||v_RANGE_OF_TIME) ;
select sysdate - v_range_of_time into v_output from dual ;
dbms_output.put_line('Result: '||v_output) ;
end;
/

Range_of_time: +01-01
Result: 20-OCT-2022 13:26


Or you can use NUMTOYMINTERVAL similar to how Connor shows above.

Interval types

Joe, November 23, 2023 - 3:24 am UTC

Thanks Chris. Using interval type solved my problem.
Chris Saxon
November 23, 2023 - 11:54 am UTC

Great, thanks for letting us know
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here