Hi Gurus
SQL> SELECT INTERVAL '1' DAY FROM DUAL;
INTERVAL'1'DAY
---------------------------------------------------------------------------
+01 00:00:00
SQL> SELECT INTERVAL '1' HOUR FROM DUAL;
INTERVAL'1'HOUR
---------------------------------------------------------------------------
+00 01:00:00
javascript:void(0);
SQL> SELECT INTERVAL '1' MINUTE FROM DUAL;
INTERVAL'1'MINUTE
---------------------------------------------------------------------------
+00 00:01:00
I want to design one procedure which will contain three input parameters for make the above sql statement dynamic
can you please help with below code error.
QL> CREATE OR REPLACE PROCEDURE SP_INTERVAL(DAT TIMESTAMP,INTRVL VARCHAR2,TERM VARCHAR2)
2 AS
3 V_OUT TIMESTAMP;
4 BEGIN
5
6
7 EXECUTE IMMEDIATE 'SELECT :1 + INTERVAL ''2'' DAY FROM DUAL' INTO V_OUT USING DAT;
DBMS_OUTPUT.PUT_LINE('Hi '||DAT);
DBMS_OUTPUT.PUT_LINE('Hi '||DAT);
END;
/ 8 9 10 11 12 13
Procedure created.
SQL> EXECUTE SP_INTERVAL(SYSDATE,'1','DAY');
Hi 14-JUL-14 01.15.21.000000 PM
Hi 16-JUL-14 01.15.21.000000 PM
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE SP_INTERVAL(DAT TIMESTAMP,INTRVL VARCHAR2,TERM VARCHAR2)
AS
V_OUT TIMESTAMP;
BEGIN
EXECUTE IMMEDIATE 'SELECT :1 + INTERVAL '':2'' DAY FROM DUAL' INTO V_OUT USING DAT,INTRVL;
DBMS_OUTPUT.PUT_LINE('Hi '||DAT);
DBMS_OUTPUT.PUT_LINE('Hi '||V_OUT);
END;
/ 2 3 4 5 6 7 8 9 10 11 12 13
Procedure created.
SQL> EXECUTE SP_INTERVAL(SYSDATE,'1','DAY');
BEGIN SP_INTERVAL(SYSDATE,'1','DAY'); END;
*
ERROR at line 1:
ORA-01867: the interval is invalid
ORA-06512: at "SP_INTERVAL", line 7
ORA-06512: at line 1
SQL>
Thanks