This content has been marked as final. Show 1 reply
pollywog wrote:The reason why it's happy is that
is there a way to assign an interval datatype variable from a table?
BANNER Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production PL/SQL Release 220.127.116.11.0 - Production CORE 18.104.22.168.0 Production TNS for HPUX: Version 22.214.171.124.0 - Production NLSRTL Version 126.96.36.199.0 - Production
CREATE TABLE mytable AS (SELECT '4' num_of_days FROM DUAL); DECLARE myinteverval INTERVAL DAY TO SECOND; BEGIN myinterval := INTERVAL '4' DAY; --happy
is a perfectly formed INTERVAL literal. By itself
INTERVAL '4' DAY
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.
Use the NUMTODSINTERVAL function:
SELECT num_of_days INTO myinteverval FROM mytable; myinteverval := interval myinteverval day; -- not happy end;
myinterval := NUMTODSINTERVAL (4, 'DAY');