1

enter image description here

Hi all, I've uploaded a photo of the way I see dates in PL/SQL Developer, I see question marks instead of the full date (need to look like "02-Aug-2013 13:50:21.663" for instance, as seen on other computers)...

If someone have any idea what on my preferences makes it look like this - I'd love to know... Thanks!

UPDATE: NLS Parameters:

NLS Parameters

NLS DB Parameters:

NLS DB Parametes

Veverke
  • 9,208
  • 4
  • 51
  • 95
Bramat
  • 979
  • 4
  • 24
  • 40
  • Can you try to run the same query typing only the month? (extract month from date) – ROMANIA_engineer Sep 03 '14 at 06:30
  • Run the query in SQL*Plus, do you still have this problem? – neshkeev Sep 03 '14 at 06:45
  • I don't think your NLS_CHARACTERSET supports Hebrew characters; doesn't it need to be IW8MSWIN1255? (Or UTF). The month value is controlled by NLS_DATE_LANGUAGE, which is inherited from NLS_TERRITORY, so you could change those in your client. If you're connecting to a DB set us as 'American' then making your client match may be necessary to make everything look 'right'. Your PC's locale settings may be affecting the defaults too. – Alex Poole Sep 03 '14 at 09:02
  • Please show us the stored procedure. Maybe you are doing something wrong there. –  Sep 03 '14 at 11:02

3 Answers3

2

The issue seems to be due to mismatch between NLS parameters in client and database. For such questions, always remember to post the OS details, DB version up to 4 decimals.

To check the the parameter values:

SELECT * FROM v$nls_parameters;
SELECT * FROM nls_database_parameters;

  1. Set the NLS_DATE_LANGUAGE in client exactly same as that of Database. In your case, NLS_DATE_LANGUAGE in database is AMERICAN.

  2. Set the environment variable for NLS_LANG as :

[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

so, set it as AMERICAN_AMERICA.WE8MSWIN1252

  1. Another observation is that, your other NLS parameters also needs to be changed in client to match with that of Database. Else, you will find similar issues every now and then, just not with DATE.
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • I've uploaded the 2 photos in the main message – Bramat Sep 03 '14 at 07:30
  • 1
    Bramat, so it is pretty clear now that the locale NLS parameters doesn't match with the Database NLS paramaters. To be precise, NLS_DATE_LANGUAGE is different. So when you display the date column fetched from DB in your client, the ENGLISH is shown as junk in HEBREW. The most important thing is that WE8MSWIN1252 characterset does not support Hebrew characters. You need to set the correct characterset to support HEBREW. And then, edit your environment variable to match it. – Lalit Kumar B Sep 03 '14 at 09:27
  • @Bramat: did this solve your problem? Please mark it as the answer to make it clearer for anyone who ends up reading your post. – Veverke Jul 21 '15 at 10:16
1

I would like to leave here a solution that has nothing to do with the national language settings, and, as the post that lead me to this itself states, 9 out of 10 cases of problems like yours (I was having just the same) - are fixed following this solution, instead.

I have been messing with the NLS for a while to no avail. I then just ended up in this post and this was what solved my problem.

In a short, if the font chosen to display SQL results is one that does not support the language you are trying to display in, then it will not work. In my case, needing to display Hebrew, I was able to do so simply by picking Tahoma as the display font, as suggested, again, by the post aforementioned.

Here is a screenshot showing the procedure:

Preferences -> User Interface -> Fonts -> Grid -> Select

enter image description here

Veverke
  • 9,208
  • 4
  • 51
  • 95
0

You may try changing 'Date Format', 'Timestamp Format', 'Timestamp TZ Format' to "DD-MON-YYYY HH24:MI:SS" (minus quotes) or in the format you want.

Access the preferences from Tools->Preferences->Database->NLS

user1639485
  • 808
  • 3
  • 14
  • 26