Problem assigning varchar to date

[Iqbal_h_a]

Hi All,
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
the error
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.
Thanks.

[jwenting]

That's because l_interval does NOT in fact contain a string representation of a date.
It contains a string representation of a mathematical formula which might resolve into a valid date (or not).

You'll need to parse that formula somehow, and the to_date function will be no help whatsoever in doing so.

[debasisdas]

You need to parse sysdate+1+2/24+20/(24*60) to proper format before processing in database.

[Iqbal_h_a]

You need to parse sysdate+1+2/24+20/(24*60) to proper format before processing in database.

Thank you very much Debasis for your valuable comments. I have tried to parse the string but at one point getting some error. Following is the parse code,

PROCEDURE JOB_EXPORT
IS
    cur CWTYPES.cursorType; 
    
    l_nextdate  DATE;         -- JOB_NEXT_RUN
    l_interval  VARCHAR2(64);
    v_string VARCHAR2(64);
    v_interval VARCHAR2(64);
    v_delimpos PLS_INTEGER;
    v_date DATE:=SYSDATE;
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
       	v_interval := l_interval;
        v_delimpos := instr (v_interval, '+') + 1;
      	v_interval := SUBSTR(v_interval, v_delimpos);
      	v_delimpos := instr (v_interval, '+');
      	WHILE v_delimpos > 0
      	LOOP
      	   v_string := SUBSTR(v_interval, 1, v_delimpos-1);
      	   [B]v_date := v_date + v_string;[/B]
      	   v_interval := SUBSTR(v_interval, v_delimpos+1);
      	   v_delimpos := instr (v_interval, '+');
      	END LOOP;
          [B]v_date := v_date + v_interval;[/B]
end if;
commit;
END JOB_EXPORT;

I am getting the error at line v_date := v_date + v_interval;. Here I am adding parsed value(1 day) to sysdate at the first iteration of loop.
For ex if I am parsing the string sysdate+1+2/24+20/(24*60), on first iteration I am adding 1 to sysdate which is stored in v_date. But here 1 is stored in v_string(varchar2). So I am getting an error while executing the stored procedure. The error thrown is :
Database Error Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Can anyone please throw some light on this.
Thanks in advance.

[Iqbal_h_a]

Hi All,

I have resolved this issue. I have parsed the string till I get the whlole number.

Thank you very much jwenting and debasisdas for your valuable comments.

--Iqbal