Possible Date Assignment Error ORA-01858: A non-numeric character was found where a n…

微信扫一扫,分享到朋友圈

Possible Date Assignment Error ORA-01858: A non-numeric character was found where a n…

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 pt.creation_date
? CHAR
, VARCHAR2
or DATE
?

Don’t use CHAR
or VARCHAR2
columns for dates as otherwise all sorts of other junk could end up in your date column: for example 31-SEP-14
, tomorrow
, n/a
, asdf
, all of which will cause problems later on during processing. Your error may be caused by dirty data in this column.

If pt.creation_date
is a DATE
, there is no need to call to_date
on it, as that will force Oracle to convert the date to a string and then back to a date. This is unnecessary.

Also, to_date('26-SEP-2014', 'DD-MON-YY')
isn’t entirely correct as you are using a four-digit year 2014
in your date string and a two-digit year YY
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');

v_prj_start
is already a DATE
, 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 ( 'DD_MON_YY'
).

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 'trunc(v_prj_completion)'
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 t
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 to_date
on a DATE
value, so

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));

Always use to_char
with a date format picture, e.g. to_char(l_start_date, 'DD-MON-YY')
.

Finally, as a general point, use four-digit years ( YYYY
) instead of two-digit years where possible. A 2-digit year 14
will be interpreted as 2014, and 15
as 2015, but how much further can you go before 2-digit years start being interpreted as in the last century? Perhaps 50
will be interpreted as 1950?

微信扫一扫,分享到朋友圈

Possible Date Assignment Error ORA-01858: A non-numeric character was found where a n…

新iPhone价格创新高 台网友称比1个月工资还高买不起

上一篇

台黑客扬言删小扎账户后:脸书被黑5000万人资料外泄

下一篇

你也可能喜欢

Possible Date Assignment Error ORA-01858: A non-numeric character was found where a n…

长按储存图像,分享给朋友