DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-1847 mystery

  1. #1
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46

    Question

    I have a SQL statement that receives the following:

    1 update xiarap0 x1
    2 set blproc = 'E'
    3 where bliden = ''
    4 and blfld07 = 'TGI'
    5 and nvl(x1.blproc,' ') = ' '
    6 and to_date(blfld18,'DD/MM/YYYY') < trunc(sysdate)
    7 and exists (select 'X'
    8 from xiarap0 x2
    9 where x2.blfld01 = x1.blfld01
    10 and nvl(x2.blfld26,' ') = ' '
    11 and nvl(x2.blfld27,' ') = ' '
    12 and nvl(x2.blfld28,' ') = ' '
    13 and x2.bliden = ''
    14* and nvl(x2.blproc,' ') = ' ')
    SQL> /
    update xiarap0 x1
    *
    ERROR at line 1:
    ORA-01847: day of month must be between 1 and last day of month
    ORA-02063: preceding line from TRAXPROD

    HOWEVER, if you move the "and to_date" (line 6) to immediately following the WHERE clause, it runs fine! :

    1 update xiarap0 x1
    2 set blproc = 'E'
    3 where to_date(blfld18,'DD/MM/YYYY') < trunc(sysdate)
    4 and bliden = ''
    5 and blfld07 = 'TGI'
    6 and nvl(x1.blproc,' ') = ' '
    7 and exists (select 'X'
    8 from xiarap0 x2
    9 where x2.blfld01 = x1.blfld01
    10 and nvl(x2.blfld26,' ') = ' '
    11 and nvl(x2.blfld27,' ') = ' '
    12 and nvl(x2.blfld28,' ') = ' '
    13 and x2.bliden = ''
    14* and nvl(x2.blproc,' ') = ' ')
    SQL> /

    0 rows updated.

    To add to the mystery, this error only occurs on the prod DB...when we run the 1st statement above against the TEST DB, it runs fine. The optimizer is CHOOSE and the table and indexes are analyzed onboth prod & test DBs; we've compared all date- and NLS-related params between test and prod and they're identical in both DBs and environ/app levels. And more info, we found that the explain plans between test and prod were different, and when we added a HINT to use a certain index on the 1st statement above, it runs fine on the prod DB. Not sure WHY an index hint would affect what seems to be a date format error. Any takers, gurus?
    Tim Hussar

  2. #2
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46

    Additional info:

    FYI, the table field that is queried with the TO_DATE function is a VARCHAR2 field, not a DATE field.
    Tim Hussar

  3. #3
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    It's because you have an invalid date somewhere in blfld18. SQL isn't very smart about erroneous data - as soon as it hits something that doesn't meet the correct format it bombs out.

    I suggest you run some PL/SQL against blfld18 along the following lines:

    function is_date(p_char varchar2) return date
    is
    v_ret_date date;
    begin
    v_ret_date := to_date(p_char, 'DD/MM/YYYY');
    return v_ret_date;
    exception when others then
    return null;
    end;

    Then just do a query:

    select * from xiarap0
    where is_date(blfld18)
    is null
    /


  4. #4
    Join Date
    May 2000
    Posts
    58
    Tim,
    Here is my guess. In rule based ( index hint ) the where clause failed before it even hit the condition
    [to_date(blfld18,'DD/MM/YYYY') < trunc(sysdate) ] and so the query ran fine though there are some dates with formating errors. I am not sure if this is an accepted behaviour of the optmizer or a bug ( undocumented feature in Oracle's terminlogy ) ) .

    Here is another wierd behaviour of the optmizer ( Oracle 8.1.6). The same sql gives different answers after I create a primary key.
    Solution from Oralce Tech support : Use Rule hint

    SQL> desc t1
    Name Null? Type
    ------------------------------- -------- ----
    TRADE NUMBER(38)
    SLOC NUMBER(38)
    CLOC NUMBER(38)

    SQL> desc t1
    Name Null? Type
    ------------------------------- -------- ----
    WAREHOUSE NUMBER(38)
    LOC NUMBER(38)

    SQL> select * from t1
    2 /

    TRADE SLOC CLOC
    ---------- ---------- ----------
    10 121 122
    11 123 124

    SQL> select * from t2
    2 /

    WAREHOUSE LOC
    ---------- ----------
    7 121
    8 122
    62 144

    SQL> SELECT aw.warehouse "System",
    2 cw.warehouse "Customer",
    3 lx.trade
    4 FROM t2 aw, t2 cw, t1 lx
    5 WHERE lx.trade in(10) AND
    6 cw.loc = lx.cloc AND
    7 aw.loc = lx.sloc
    System Customer TRADE
    ---------- ---------- ----------
    7 8 10

    SQL> ALTER TABLE T1
    2 ADD(CONSTRAINT T1_PK002 PRIMARY KEY(TRADE));

    Table altered.

    SQL> SELECT aw.warehouse "System",
    2 cw.warehouse "Customer",
    3 lx.trade
    4 FROM t2 aw, t2 cw, t1 lx
    5 WHERE lx.trade in(10) AND
    6 cw.loc = lx.cloc AND
    7 aw.loc = lx.sloc
    8 /

    System Customer TRADE
    ---------- ---------- ----------
    7 62 10

  5. #5
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46
    I had done that before, and yes there are nulls. The app uses these fields flexibly e.g. string could be a date, literal,etc. So I agree that that is why the error occurs. The question is, why does is not occur when moving the TO_DATE function to the 1st line of the WHERE clause?? It shouldn't matter where the line is, it should always error out.

    More FYI: both the test and prod DBs are v7.3.4
    Tim Hussar

  6. #6
    Join Date
    May 2000
    Posts
    58
    It looks like ( i just found out from running the below sqls)
    that the where clause is evaluated from Left to right in cost based and right to left in rule based. This might explain the strange behaviour.

    select * from t
    X Y
    ---------- ----------
    11/10/2001 0
    30/10/2001 0
    34/1/2001 0

    COST BASED
    1 select 1 from t
    2 where
    3 to_date(x,'dd/mm/yyyy') > sysdate and
    4* y=2
    SQL> /
    to_date(x,'dd/mm/yyyy') > sysdate and
    *
    ERROR at line 3:
    ORA-01847: day of month must be between 1 and last day of month

    1 select 1 from t
    2 where
    3 y=2 and
    4* to_date(x,'dd/mm/yyyy') > sysdate
    SQL> /

    no rows selected

    RULE BASED
    1 select /*+ rule */ 1 from t
    2 where
    3 y=2 and
    4* to_date(x,'dd/mm/yyyy') > sysdate
    SQL> /
    to_date(x,'dd/mm/yyyy') > sysdate
    *
    ERROR at line 4:
    ORA-01847: day of month must be between 1 and last day of month

    1 select /*+ rule */ 1 from t
    2 where
    3 to_date(x,'dd/mm/yyyy') > sysdate and
    4* y=2
    SQL> /

    no rows selected

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    I thing Oracle works in RULE mode and
    - in first case use FILTER for all (or many) rows
    - in second case you have subset without bad value in selected rows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width