Bug in NLS_TIMESTAMP_FORMAT in Editor run

damir.vadas_250 · April 3, 2014, 2:21am

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.

mstaszew · April 3, 2014, 7:14am

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

damir.vadas_250 · April 4, 2014, 2:04am

Hi,

-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:

I’m not using date but timestamp type.
I use F5 (run as script) so I think that previous option has no meaning at all .
When I uncheck (Execute scripts in Toad session result is the same-wrong.

Still open issue.

damir.vadas_250 · April 7, 2014, 9:41am

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

?

damir.vadas_250 · April 9, 2014, 10:13am

Bumps?

GTDG · April 9, 2014, 10:23am

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

damir.vadas_250 · April 9, 2014, 11:10am

@Gregory,

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