Skip to Main Content
  • Questions
  • How to use bind variables in date interval functions

Breadcrumb

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:

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)

Is this answer out of date? If it is, please let us know via 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 to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here