8

i currently try to execute the following query on an Oracle DB

select tzname || ' (UTC'|| tz_offset(tzname) || ')' from v$timezone_names

It not seems to be very complicated. Just the name of the timzone and the UTC offset in braces. But when i execute the query with PL/SQL Developer on windows it always eats up the last brace.

So I went to sqlplus and executed it there and now i get my last brace but also an additional whitespace before the last brace as an extra goody.

I've tried it with nested to_char() and trim() but nothing changes. I also tried it on different DBs but it's always the same.

Does anybody know if there is a problem with tz_offset and string concatenation?

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
Marcel
  • 123
  • 6

3 Answers3

7

Issuing the following query:

select dump(tz_offset(tzname)) from v$timezone_names;

You get results like these:

Typ=1 Len=7: 43,48,49,58,48,48,0
Typ=1 Len=7: 43,48,49,58,48,48,0
Typ=1 Len=7: 43,48,49,58,48,48,0
Typ=1 Len=7: 43,48,49,58,48,48,0
Typ=1 Len=7: 43,48,49,58,48,48,0
Typ=1 Len=7: 43,48,49,58,48,48,0
...

This shows that tz_offset() returns null-terminated strings (maybe a bug). So for your query, Oracle is returning

"Africa/Algiers (UTC+01:00\0)" // Note \0 -> null character
"Africa/Cairo (UTC+03:00\0)" // Note \0 -> null character
...

Having that in mind, I guess that PL/SQL Developer interprets \0 as end-of-string (maybe another bug, SQL strings are not null-terminated) and so it does not bother writing the rest of the string, so you lose the trailing brace. SQL*PLus chooses instead to print a whitespace instead of that null and then proceeds with the rest of the string, printing the closing brace.

As a workaround, you can replace tz_offset(...) with replace(tz_offset(...), chr(0)). This will delete nulls from whatever tz_offset(...) returns.

gpeche
  • 21,974
  • 5
  • 38
  • 51
  • Looks like a bug specific to PL/SQL Developer. Oracle SQL Developer shows the \0 as a little square and shows any characters appended to the string. – Jeffrey Kemp Jun 02 '11 at 01:34
  • @Jeffrey Kemp yes, but I think `tz_offset()` should not return null-terminated strings in any case. – gpeche Jun 02 '11 at 10:21
  • this is SQL, strings don't need a terminator - a chr(0) at the end of a string, while useless, is perfectly legal – Jeffrey Kemp Jun 26 '11 at 06:35
1

It works with substring but that doesn't really answer your question why it is happening :-):

select tzname || ' (UTC'|| substr(tz_offset(tzname),1,6) || ')' from v$timezone_names;

Shepherdess
  • 651
  • 6
  • 7
0

I was creating a JSON ajax resource that returns timestamps that need to include the timezone offset... that trailing control character was really annoying me, I trim if off as follows:

regexp_replace(tz_offset('Canada/Mountain'),'[[:cntrl:]]','')

Rick0Shea
  • 1