Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: NLS date, 8.1.7 client and 10g backend

    Greetings,

    I've got a client that recently got around to upgrading from 8.1.7 database to 10g. Our application will not work with any client versions higher than 8.1.7. So, it is a 10g back end (Unix) and a 8.1.7 client (Windows). Most everything is working with the exception of dates in certain parts of the application. Our C calls execute alter session set nls_date_format='mm/dd/yyyy' when connecting. However, it does not stick. If I enter that same command from SQL Plus there is no error but select sysdate from dual returns dd-mon-yy. This works in a 9 or higher client but I can't use it (I have no control over that, not my idea, etc)

    I have tried registry settings, glogin settings, oracle.ini setting and nothing makes a difference. I've searched and searched the Internet but found nothing that works. Any suggestions?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    set nls_date_format='mm/dd/yyyy' at DB level via initialization parameter
    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 2002
    Location
    Sunny Florida
    Posts
    121
    May not be an option if we are on a shared instance.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it works for me.
    Code:
    Enter user-name: user1/user1
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    28-JAN-11
    
    SQL> alter session set nls_date_format='mm/dd/yyyy' ;
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ----------
    01/28/2011
    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
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I notice you did not include the banner that displays the client version. What is it?

    BTW I am smart enough to do the test you displayed. It does not work for me!

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Anyone? A query of nls_session_parameters shows that the format is mm/dd/yyyy but a select sysdate shows the date in Oracle's default format. I've tried everything from registry settings to environment variables and nothing is working. I've got another client running the same version of the back end and of the client. Actually several and none have this particular problem. Because of this I have no reason to think it is a bug of some sort between the 8.1.7 client and the 10g server.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Actually several and none have this particular problem.
    then root cause is local to either client or server involved
    & we have nothing to offer you except condolences.
    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.

  8. #8
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I'd like to thank all of you for contributing nothing and Anacedent the most. I don't actually recall ever getting an answer to a question here, ever. I'd like to let you all know that I have figured out not what the cause of the issue is but where it is. It is within 10g somewhere. I loaded 10.2.0.4.0 on my machine and the 8.1.7 client. I could recreate the issue. I then came upon additional problems with 10g that I solved but a major portion involved having to recreate a sample database because the DBCA util has problems. So, the issue is not with the client software but with the server.

    So, again, thank you for nothing and when I have issues with Oracle in the future this dump will not be a place I seek help with. You can count on the fact that I will not return so ban me, lock the account, etc. I'll never know as I do not waste my time with the useless. Bye.

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Give anacedent some credit, he/she was trying to help you out, reason probably nodobdy dig in. I am glad to hear you solved your problem, and if you dont mind, since this is a public forum, you can share your solution to the problem with us, so people like us that get here "only" to get answers, could actually benefit from it.

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Oh, and by the way, just for the sake of it, I have actually made a test (with VMWare) with a 8.1.7 client running on WindozeXP with 10g as a backend (Solars 10 x86) and I have no such problems as yours, so indeed, it could have been a problem with your environment, as anacedent pointed out.

Posting Permissions

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