I’m getting the error ORA-01858: a non-numeric character was found where a numeric was expected, which I think is because the date assignment in my code isn’t correct. Can someone have a look at my code snippet below and let me known the correct way of assigning the date if it’s wrong please?
Declare v_task_start DATE; v_task_completion DATE; v_prj_start DATE; v_prj_completion DATE; l_start_date DATE; l_completion_date DATE; v_task_id NUMBER; v_prj_id NUMBER; CURSOR c_tasks_to_update IS SELECT pt.TASK_ID, pt.Project_id AS Task_Prj_Id, pt.start_date AS Task_start, pt.Completion_date AS Task_Completion, ppa.start_date AS Project_start, ppa.completion_date AS Project_completion INTO v_task_id, v_prj_id, v_task_start, v_task_completion, v_prj_start, v_prj_completion FROM pa_tasks pt, pa_projects_all ppa WHERE pt.project_id = ppa.project_id AND pt.created_by = 1623 and to_date(pt.creation_date,'DD-MON-YY') = to_date('26-SEP-2014', 'DD-MON-YY'); BEGIN FOR ctask_update in c_tasks_to_update LOOP v_task_start := ctask_update.Task_start; v_task_completion := ctask_update.Task_Completion; v_prj_start := ctask_update.Project_start; v_prj_completion := ctask_update.Project_completion; IF ((v_task_start v_prj_start) and (v_task_completion v_prj_completion)) THEN DBMS_OUTPUT.put_line( 'Task Start date is not equal to Project start date, Task completion date is not equal to Project completion date ' ||v_task_start||' '||v_prj_start||' '||v_task_completion ||' '|| v_prj_completion); l_start_date := to_date(trunc(v_prj_start),'DD_MON_YY'); /*SELECT START_DATE FROM PA_PROJECTS_ALL INTO l_start_date FROM pa_projects_all WHERE project_id = v_prj_id;*/ l_completion_date := to_date('trunc(v_prj_completion)','DD_MON_YY'); /*SELECT completion_date FROM PA_PROJECTS_ALL INTO l_completion_date FROM pa_projects_all WHERE project_id = v_prj_id;*/ DBMS_OUTPUT.put_line( 'Task start date to be updated to '|| to_char(l_start_date)); DBMS_OUTPUT.put_line( 'Task completion date to be updated to '|| to_char(l_completion_date)); End;
I don’t get to see the output statements
Task start date to be updated to:xxxx
. So the exception is occurring here.
There are a number of issues I can see here. Firstly, the following line:
and to_date(pt.creation_date,'DD-MON-YY') = to_date('26-SEP-2014', 'DD-MON-YY');
What is the type of
columns for dates as otherwise all sorts of other junk could end up in your date column: for example
, all of which will cause problems later on during processing. Your error may be caused by dirty data in this column.
, there is no need to call
on it, as that will force Oracle to convert the date to a string and then back to a date. This is unnecessary.
isn’t entirely correct as you are using a four-digit year
in your date string and a two-digit year
in your date format picture. However, although this looks odd, Oracle doesn’t seem to have a problem with it.
The next problem is here:
l_start_date := to_date(trunc(v_prj_start),'DD_MON_YY');
is already a
, so there’s no need to convert it to a date. You are doing an unnecessary conversion from date to string and back again. It is probably sufficient to just write
l_start_date := trunc(v_prj_start);
Also I’m not sure why you are using underscores in your date format picture (
The next problem I see is this line:
l_completion_date := to_date('trunc(v_prj_completion)','DD_MON_YY');
This could well be the line that is generating your error. In fact, it is guaranteed to fail with exactly the error message you report. This is because the string
isn’t a valid date in the format you specified. Oracle is expecting a number at the start of your date string and you have given it the letter
instead. I imagine you intended to write the following line instead:
l_completion_date := to_date(trunc(v_prj_completion),'DD_MON_YY');
However, once again, don’t use
l_completion_date := trunc(v_prj_completion);
may well be enough.
Further on down there are two lines similar to the following:
DBMS_OUTPUT.put_line( 'Task start date to be updated to '|| to_char(l_start_date));
with a date format picture, e.g.
Finally, as a general point, use four-digit years (
) instead of two-digit years where possible. A 2-digit year
will be interpreted as 2014, and
as 2015, but how much further can you go before 2-digit years start being interpreted as in the last century? Perhaps
will be interpreted as 1950?