Pass time interval via variable in Oracle SQL

Question

This query returns the value based on given interval time 10.

SELECT TO_CHAR((TO_DATE('23:20:20','HH24:MI:SS') - INTERVAL '10' MINUTE), 'HH24:MI:SS') 
FROM DUAL;

Output:

23:10:20

I want to pass this minute interval via variable using below code:

declare
test1 varchar(30);
begin
test1:=18;
SELECT TO_CHAR((TO_DATE('23:20:20','HH24:MI:SS') - INTERVAL ||test1|| MINUTE), 'HH24:MI:SS') 
 FROM DUAL;
 end;

But its not working - error is

PL/SQL: ORA-00904: "MINUTE": invalid identifier

Please assist me on this. Thanks!

Answer 1

You can't do that as it has to be a string literal, not a variable. But you can use the numtodsinterval() function instead:

declare
  test1 number;
begin
  test1:=18;
  SELECT TO_CHAR(TO_DATE('23:20:20','HH24:MI:SS')
   - NUMTODSINTERVAL(test1, 'MINUTE'), 'HH24:MI:SS')
  INTO <something>
  FROM DUAL;
end;
/

SQL Fiddle, including a really simple function version.

Answer 2

Try this:

DECLARE
   l_val      NUMBER;
   l_result   VARCHAR2( 20 );
BEGIN
   l_val := 10;

   SELECT TO_CHAR( ( TO_DATE( '23:20:20', 'HH24:MI:SS' ) - INTERVAL '1' MINUTE * l_val ), 'HH24:MI:SS' ) INTO l_result FROM DUAL;

   DBMS_OUTPUT.put_line( l_result );
END;

Output will be:
23:10:20