Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: error only displays from SQL*Plus in Linux

    I have a PACKAGE BODY variable defined as

    Code:
    due_date     DATE := TO_DATE('01-APR-' || TO_CHAR(SYSDATE, 'YYYY'));
    When the PACKAGE is executed from SQL*PLus in Windows or TOAD, the PACKAGE runs without error. When the PACKAGE is run from SQL*Plus on the server (LINUX) I get the error

    Code:
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
    ORA-06512: at "PRETAX.UT2309_PG", line 22
    ORA-06512: at line 4
    Why the conditional behavior?

    The fix is to define the TO_DATE() with a format, but I am still curious what caused the last couple of hours of searching.
    -Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect it is due to a different default NLS date/time format between the two systems.
    (It has nothing directly to do with Windoze vs. Linux; it is just two different systems).

    If you do
    SQL> SELECT SYSDATE FROM DUAL;
    on both systems, I bet the results will be formatted differently.

    HTH & YMMV!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Sorry, I always inevitably mis-state the details:

    The instance is on LINUX, but I have TOAD on my Windows XP desktop, as well as SQL*Plus. Everythings pointed at the same instance. But you are right that the format for the date differs. So why would that be?

    Windows
    Code:
    SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 2 15:29:10 2007
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    02-APR-07
    
    SQL> show parameter NLS_DATE_FORMAT
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    nls_date_format                      string      MM/DD/RR
    LINUX
    Code:
    SQL*Plus: Release 10.1.0.3.0 - Production on Mon Apr 2 15:29:26 2007
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    Enter password: 
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> select sysdate from dual;
    
    SYSDATE
    --------
    04/02/07
    
    SQL> show parameter NLS_DATE_FORMAT
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    nls_date_format                      string      MM/DD/RR
    Is there something that sets the date format for each instance of SQL*Plus?

    -Chuck

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If I am having a good day, I can only spell N-L-S & today is not a good day.
    I don't know enough to explain the details.
    Your problem description just smelled to me like a default DATE format difference.
    >Is there something that sets the date format for each instance of SQL*Plus?
    AFAIK, it is NOT directly related to SQL*Plus, but IS directly client related.
    In your case your client s/w (SQL*Plus) is inheriting from the OS its NLS DATE format.
    Exactly how, what & why I honestly don't know.
    You may be able to find the actual answer either on MetaLink or in the Globalization Manual.
    I don't have the time or inclination to dig deeper for a more detailed answer.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I would bet on NLS_LANG being different on the two machines.

    Here is the NLS parameters priority :

    1. SQL (as in TO_CHAR, TO_DATE... with a format )
    2. ALTER SESSION
    3. NLS_LANG
    4. Server init parameter
    5. Default

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The bottom line of this entire thread is what people have been saying over and over and over. NEVER rely on the NLS settings, always provide a format mask when converting from character to date.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by beilstwh
    NEVER rely on the NLS settings, always provide a format mask when converting from character to date.
    I couldn't agree more!

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    I couldn't agree more!
    I agree too.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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