Insert function parameter in select part of sql query

Question

Oracle PL/SQL function that takes a string as a parameter.

What is the correct syntax for including this parameter in the Select portion of a statement.

The function is :

create or replace function GetHorizonTime(HorizonWindowMinutes IN VARCHAR2) 
return timestamp
IS
  RETVALUE TIMESTAMP;
BEGIN
  SELECT SYSDATE + Interval '''||HorizonWindowMinutes||''' MINUTE INTO RETVALUE
    FROM DUAL;
  RETURN RETVALUE;
END;
/

SELECT GetHorizonTime('720') FROM DUAL;

The select statement should translate to:

SELECT SYSDATE + Interval '720' MINUTE INTO RETVALUE
    FROM DUAL;

Why are you passing in a string? I would expect that HorizonWindowMinutes should be a number. Then you'd pass that number to the numtodsinterval function rather than constructing an interval literal. You could still do that if HorizonWindowMinutes is a string that only has numeric data using implicit conversion but that wouldn't generally make sense. — Justin Cave
– Justin Cave, Commented Jul 7, 2015 at 19:23
string was the latest incarnation trying to get it to work. I originally started with integer. Thanks, wasn't aware of the numtodsinterval function. — sweetfa
– sweetfa, Commented Jul 7, 2015 at 19:24

Invalidsearch · Accepted Answer · 2015-07-07 19:26:26Z

3

  create or replace function GetHorizonTime(HorizonWindowMinutes IN NUMBER) 
   return timestamp
   IS
     RETVALUE TIMESTAMP;
BEGIN
  RETURN (SYSDATE + HorizonWindowMinutes/1440);
END;
/

answered Jul 7, 2015 at 19:26

Invalidsearch

4113 silver badges11 bronze badges

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

Comments

Justin Cave · Accepted Answer · 2015-07-07 19:32:21Z

2

Use the numToDSInterval function rather than constructing an interval literal

create or replace function GetHorizonTime(HorizonWindowMinutes IN NUMBER) 
  return timestamp
IS
BEGIN
  return SYSDATE + numtodsinterval( HorizonWindowMinutes, 'minute' );
END;
/

If you're working with timestamp data types, I would generally add an interval to the current_timestamp or localtimestamp rather than to a date like sysdate. If you don't need fractional seconds or time zones, on the other hand, it would seem sufficient to just return a date rather than a timestamp.

answered Jul 7, 2015 at 19:32

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Comments

Wernfried Domscheit · Accepted Answer · 2015-07-07 20:05:24Z

1

Another way of doing it is this one:

SELECT systimestamp  + HorizonWindowMinutes * Interval '1' MINUTE 
INTO RETVALUE
FROM DUAL;

answered Jul 7, 2015 at 20:05

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Insert function parameter in select part of sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related