Timestamp format

[Anand]

Hi,

Has anyone encountered problems with the format of the timestamp column that is displayed in the SQL Window (or any other window) - in particular it not returning the milliseconds value?

I cannot seem to get the format to show the same values as the NLS_TIMESTAMP_FORMAT (which is the default format that should be used). I do not have an explicit value set for this in my Oracle home part of the registry but checking the NLS session settings the correct value is being returned.

For example the following SQL:

SELECT parameter, value, systimestamp from nls_session_parameters where parameter like '%TIME%'

returns:

NLS_TIME_FORMAT HH24.MI.SSXFF 13/02/2008 11:01:48
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF 13/02/2008 11:01:48
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR 13/02/2008 11:01:48
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR 13/02/2008 11:01:48

I have tried issuing an "ALTER SESSION " command to modify the timestamp format but this made no difference. The only way I can currently get the fractional seconds value to be returned from a timestamp column it to apply the TO_CHAR function to it.

I believe this problem to be completely environmental but cannot see a way to resolve it. I have both 9.2.0.7 and 10.2.0.3 installed locally with the 10g home being the default.

The following is an extract of the Help/support info output:

Homes
OraDb10g_home1 (C:\oracle\product\10.2.0\db_1)
OraHome92 (C:\oracle\ora92)

DLLs
C:\oracle\product\10.2.0\db_1\bin\oci.dll

TNS File
C:\oracle\product\10.2.0\db_1\Network\Admin\tnsnames.ora

Using
Home: OraDb10g_home1
DLL: C:\oracle\product\10.2.0\db_1\bin\oci.dll
OCI: version 9.2
Oracle9i Enterprise Edition Release 9.2.0.7.0

Character Sets
Character size: 1 byte(s)
CharSetID: 31
NCharSetID: 2000
Unicode Support: True
NLS_LANG: ENGLISH_UNITED KINGDOM.WE8MSWIN1252
NLS_CHARACTERSET: WE8ISO8859P1
NLS_NCHAR_CHARACTERSET: AL16UTF16

If anyone has managed to resolve an issue similar to this I would appreciate your feedback.

Thanks,

Anand.

 

[Marco Kalter]

If I run this script in the Command Window:

alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SSXFF';
select systimestamp from dual;
alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SS';
select systimestamp from dual;

I get this output:

alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SSXFF';

Session altered

select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------
13-FEB-2008 14.50.37.687000

alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SS';

Session altered

select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------
13-FEB-2008 14.50.37

This looks okay to me. Can you try the same?

 

[Anand]

Hi Marco,

Thanks for the response. Unfortunately in my case it's worse in the command window (but this may be a column formatting issue like in the Oracle SQL*Plus client).

Here's the output:

Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as homs

SQL>
SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SSXFF';

Session altered
SQL> select systimestamp from dual;

SYSTIMESTAMP
------------
13/02/2008 1
SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SS';

Session altered
SQL> select systimestamp from dual;

SYSTIMESTAMP
------------
13/02/2008 1

SQL>

At least in the SQL window I get the seconds displayed.

Anymore ideas would be welcome.

Incidentally this is happening on what I believe to be the latest version of PL/SQL Developer (7.1.4.1390). Also as I mentioned earlier it is definitely down to my particular environment - other users don't encounter the same issue.

Thanks,

Anand.

 

[Gustavo]

Run

set col 50

and then retry.

 

[Anand]

Hi,

I did try setting the format values for the column but it made no difference. Here's the output for setting it to 50:

Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as homs

SQL> set col 50
SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SSXFF';

Session altered

SQL> select systimestamp from dual;

SYSTIMESTAMP
------------
14/02/2008 0

SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SS';

Session altered

SQL> select systimestamp from dual;

SYSTIMESTAMP
------------
14/02/2008 0

SQL> select to_char(systimestamp,'dd/mm/yyyy hh24:mi:ssxff') from dual;

TO_CHAR(SYSTIMESTAMP,'DD/MM/YY
------------------------------
14/02/2008 09:23:43.642000

SQL>

As you can see from the output using the TO_CHAR function clearly displays all the characters of the timestamp.

Thanks,

Anand.

 

[Marco Kalter]

What happens if you first execute this in the Command Window:

SQL> set datewidth 30

It shouldn't affect anything (since the timestamp is not a date), but my guess is that it will. In that case Oracle is returning the timestamp field as a date field.

 

[Anand]

Hi,

I'm afraid that makes no difference. Here's the output:

Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as homs

SQL> set datewidth 30

SQL> select systimestamp from dual;

SYSTIMESTAMP
------------
14/02/2008 1

SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SSXFF';

Session altered

SQL> select systimestamp from dual;

SYSTIMESTAMP
------------
14/02/2008 1

SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SS';

Session altered

SQL> select systimestamp from dual;

SYSTIMESTAMP
------------
14/02/2008 1

SQL> select sysdate, systimestamp from dual;

SYSDATE                        SYSTIMESTAMP
------------------------------ ------------
14/02/2008 14:21:38            14/02/2008 1

SQL>

Note that the date value returns correctly. This happens on all databases that I connect to (on both 9.2.0.7 and 10.2.0.3 versions). Other people don't have this issue. I will try to un-install and re-install the product when I get a chance to see if that makes any difference but any other ideas would be welcome.

Thanks,

Anand.

 

[Anand]

Looks like a complete re-install did the trick. Works as expected now.

Thanks for you help Marco.

 

[Marco Kalter]

What did you re-install?

 

[Anand]

Hi,

I un-installed PL/SQL Developer and deleted the folder that it was originally installed in. Then re-installed it from the same download I used for my previous install (which was version 7.1.4.1390). This obviously set all the preferences back to the default.

I've since started applying the preferences that I like back in and have been testing this issue each time I make a change and I think I've found the underlying problem. When I set the Session Mode to be "Single Session" I see the problem with the timestamp (as highlighted in this post). Using the "Multi Session" or "Dual Session" options appears to solve the issue. Could this be a bug with the Single Session settings?

I'd appreciate it if you could try the same Marco and verify either way.

Thanks,

Anand.