1 Reply Latest reply: Mar 7, 2013 1:34 PM by Frank Kulash RSS

    assign interval

    pollywog
      BANNER
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for HPUX: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      is there a way to assign an interval datatype variable from a table?
      CREATE TABLE mytable
      AS
         (SELECT '4' num_of_days FROM DUAL);
      
      DECLARE
         myinteverval   INTERVAL DAY TO SECOND;
      BEGIN
         myinterval := INTERVAL '4' DAY;  --happy
      
         SELECT num_of_days INTO myinteverval FROM mytable;
      
         myinteverval := interval myinteverval day; -- not happy
      end;
        • 1. Re: assign interval
          Frank Kulash
          Hi,
          pollywog wrote:
          BANNER
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          PL/SQL Release 11.2.0.3.0 - Production
          CORE     11.2.0.3.0     Production
          TNS for HPUX: Version 11.2.0.3.0 - Production
          NLSRTL Version 11.2.0.3.0 - Production
          is there a way to assign an interval datatype variable from a table?
          CREATE TABLE mytable
          AS
          (SELECT '4' num_of_days FROM DUAL);
          
          DECLARE
          myinteverval   INTERVAL DAY TO SECOND;
          BEGIN
          myinterval := INTERVAL '4' DAY;  --happy
          The reason why it's happy is that
          INTERVAL '4' DAY
          is a perfectly formed INTERVAL literal. By itself
          '4'
          would be a VARCHAR2 interval, but it is not by itself; immediately before the first single-quote is the keyword INTERVAL, which means the expression is an INTERVAL literal, and an INTERVAL literal must contain a number in single-quotes; not any kind of string expression.
          SELECT num_of_days INTO myinteverval FROM mytable;
          
          myinteverval := interval myinteverval day; -- not happy
          end;
          Use the NUMTODSINTERVAL function:
          myinterval := NUMTODSINTERVAL (4, 'DAY');