Why is Oracle eating my string?

Question

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?

Answer 1

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.

Answer 2

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;