I am struck with following problem. Please help me out.
I have defined a Oracle procedure as follows.
PROCEDURE JOB_EXPORT IS cur CWTYPES.cursorType; l_nextdate DATE; -- JOB_NEXT_RUN l_interval VARCHAR2(64); BEGIN OPEN cur FOR SELECT JOB_NEXT_RUN, JOB_INTERVAL FROM JOB_EXPORT_CDRS WHERE ACTIVE_STATUS=1 AND JOB_INTERVAL IS NOT NULL LOOP FETCH cur INTO l_nextdate,l_interval EXIT WHEN cur%NOTFOUND; if l_nextdate is NULL then l_nextdate:=SYSDATE; DBMS_OUTPUT.PUT_LINE(': nextdate is NULL, use SYSDATE as default'); else l_nextdate:=to_date('l_interval','dd-Mon-yyyy HH24:Mi:SS'); end if; commit; END JOB_EXPORT;
Here l_interval contains the value "sysdate+1+2/24+20/(24*60)". i.e. added 1 day, 2 hours and 20 minutes to the sysdate and put it in l_interval which is of varchar2. When I tried to assign this value to l_nextdate I am getting
ORA-01858: a non-numeric character was found where a numeric was expected
The above error is getting thrown during run time and not during compile time.
Any help is greatly appreciated.