NLS_DATE_FORMAT and Debugging

[Kevin Seymour]

Hi,

When debugging through code PL/SQL developer does not seem to set NLS_DATE_FORMAT like it does for an SQL window correctly. Mousing over, or adding to the watch list, a date variable displays in the standard (terrible) default dd-mon-yy format. Explicitly executing dbms_stession.set_nls in the test window before debugging resolves the issue. Is this by design, a missed setting, or a bug?

Thanks,

Kevin

 

[Marco Kalter]

PL/SQL Developer will not set the NLS_DATE_FORMAT for any window, unless you have entered it in the AfterConnect.sql script, on the plsqldev.exe command line (plsqlev.exe env:nls_date_format=dd-mm-yyyy), or in the params.ini file.

You can also set it in the Oracle registry of course.

 

[Hilarion]

I think that the issue Kevin is really reporting is the difference between behaviour of result grid of SQL Window and the result grid of SQL executed in debug session.
In the first case you get DATE values formatted according to PL/SQL Developer preference settings, which are called "NLS Options", which are independent of Oracle session NLS settings (the options name if a bit confusing as it may suggest that it affects session NLS settings which it doesn't).
In the second case the DATE values are formatted according to Oracle session NLS settings and not PL/SQL Developer preference settings.

As you suggested, Kevin can solve the issue by ensuring that Oracle session NLS settings are the same as PL/SQL Developer preference settings. This can be done by all the means you suggested and he could probably also choose "Oracle format" option in the "NLS Options" of PL/SQL Developer to make sure that SQL Window will also follow the Oracle session NLS settings.