Bug in NLS_TIMESTAMP_FORMAT in Editor run

Toad for Oracle
1

Hi,

Toad 12.5.0.55 x64

In Toad:

alter session set NLS_TERRITORY=‘CROATIA’;
alter session set NLS_DATE_LANGUAGE=‘CROATIAN’;
alter session set NLS_LANGUAGE=‘CROATIAN’;
alter session set NLS_TIMESTAMP_FORMAT=‘YYYY.MM.DD_HH24.MI.SSXFF’;
alter session set NLS_TIMESTAMP_TZ_FORMAT=‘YYYY.MM.DD_HH24.MI.SSXFF’;

select systimestamp from dual;

col PARAMETER for a30
col value for a40
select parameter, value from nls_session_parameters order by 1 ;

F5(run as script):

Session altered.
Session altered.
Session altered.
Session altered.
Session altered.

SYSTIMESTAMP

03.APR.2014 11:13:16,250640 +02

1 row selected.

PARAMETER VALUE

NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY kn
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE CROATIAN
NLS_DUAL_CURRENCY kn
NLS_ISO_CURRENCY CROATIA
NLS_LANGUAGE CROATIAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS ,.
NLS_SORT CROATIAN
NLS_TERRITORY CROATIA
NLS_TIME_FORMAT HH24:MI:SSXFF
**NLS_TIMESTAMP_FORMAT YYYY.MM.DD_HH24.MI.SSXFF **
**NLS_TIMESTAMP_TZ_FORMAT YYYY.MM.DD_HH24.MI.SSXFF **
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

17 rows selected.

Format is wrong.

In SQL*Plus:

SQL> alter session set NLS_TERRITORY=‘CROATIA’;

Session altered.

SQL> alter session set NLS_DATE_LANGUAGE=‘CROATIAN’;

Session altered.

SQL> alter session set NLS_LANGUAGE=‘CROATIAN’;

Session altered.

SQL> alter session set NLS_TIMESTAMP_FORMAT=‘YYYY.MM.DD_HH24.MI.SSXFF’;

Session altered.

SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT=‘YYYY.MM.DD_HH24.MI.SSXFF’;

Session altered.

SQL>
SQL>
SQL>
SQL> select systimestamp from dual;

SYSTIMESTAMP

2014.04.03_11.15.11,236001

SQL>
SQL> col PARAMETER for a30
SQL> col value for a40
SQL>
SQL> select parameter, value from nls_session_parameters order by 1 ;

PARAMETER VALUE

NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY kn
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE CROATIAN
NLS_DUAL_CURRENCY kn
NLS_ISO_CURRENCY CROATIA
NLS_LANGUAGE CROATIAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS ,.
NLS_SORT CROATIAN
NLS_TERRITORY CROATIA
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT YYYY.MM.DD_HH24.MI.SSXFF
NLS_TIMESTAMP_TZ_FORMAT YYYY.MM.DD_HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

17 rows selected.

SQL>

Format is OK.

Interesting is that whether script or as sql is run, bug persist.

2

The output obeys the date format setting in Options on the Data Grid|Data page.

On 04/03/2014 05:22 AM, Damir Vadas wrote:

Bug in NLS_TIMESTAMP_FORMAT in Editor run

Thread created by Damir Vadas
Hi,

Toad 12.5.0.55 x64

In Toad:

alter session set NLS_TERRITORY='CROATIA';

alter session set NLS_DATE_LANGUAGE='CROATIAN';

alter session set NLS_LANGUAGE='CROATIAN';

alter session set NLS_TIMESTAMP_FORMAT='YYYY.MM.DD_HH24.MI.SSXFF';

alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY.MM.DD_HH24.MI.SSXFF';

select systimestamp from dual;

col PARAMETER for a30

col value for a40

select parameter, value from nls_session_parameters order by 1 ;

F5(run as script):

Session altered.

Session altered.

Session altered.

Session altered.

Session altered.

SYSTIMESTAMP

03.APR.2014 11:13:16,250640 +02

1 row selected.

PARAMETER VALUE

NLS_CALENDAR GREGORIAN

NLS_COMP BINARY

NLS_CURRENCY kn

NLS_DATE_FORMAT DD.MM.RR

NLS_DATE_LANGUAGE CROATIAN

NLS_DUAL_CURRENCY kn

NLS_ISO_CURRENCY CROATIA

NLS_LANGUAGE CROATIAN

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NUMERIC_CHARACTERS ,.

NLS_SORT CROATIAN

NLS_TERRITORY CROATIA

NLS_TIME_FORMAT HH24:MI:SSXFF

**NLS_TIMESTAMP_FORMAT YYYY.MM.DD_HH24.MI.SSXFF **

**NLS_TIMESTAMP_TZ_FORMAT YYYY.MM.DD_HH24.MI.SSXFF **

NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

17 rows selected.

Format is wrong.

In SQL*Plus:

SQL> alter session set NLS_TERRITORY='CROATIA';

Session altered.

SQL> alter session set NLS_DATE_LANGUAGE='CROATIAN';

Session altered.

SQL> alter session set NLS_LANGUAGE='CROATIAN';

Session altered.

SQL> alter session set NLS_TIMESTAMP_FORMAT='YYYY.MM.DD_HH24.MI.SSXFF';

Session altered.

SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY.MM.DD_HH24.MI.SSXFF';

Session altered.

SQL>

SQL>

SQL>

SQL> select systimestamp from dual;

SYSTIMESTAMP

2014.04.03_11.15.11,236001

SQL>

SQL> col PARAMETER for a30

SQL> col value for a40

SQL>

SQL> select parameter, value from nls_session_parameters order by 1 ;

PARAMETER VALUE

3

Hi,

  1. -The output obeys the date format setting in Options on the Data Grid|Data page.

It is rather different then one get in result

When I run a query (each line with Ctrl+F9) here is the result:

I have option:

  1. I’m not using date but timestamp type.

  2. I use F5 (run as script) so I think that previous option has no meaning at all .

  3. When I uncheck (Execute scripts in Toad session result is the same-wrong.

Still open issue.

4

Am I missing something or I do not understand as resolved, while it is not…

?

5

Bumps?

6

Damir,

We’re in code freeze right now for the 12.5 release so this won’t be addressed for a little while. In the mean time, you can always use the “execute in SQL*Plus” option from the script button.

Greg

7

@Gregory,

Thy for explanation. SQL*Plus was never a problem just wanted to help finding new bug.