Oracle not merging after TZ_OFFSET ()

It seems to be a bug (1937516, which I don't see, also 9861391, which refers to the underlying error 17291158, which I also don't see) combined with client behavior. In SQL * Plus or SQL Developer this looks fine - like:

select '[' || tz_offset('Europe/Amsterdam') || ']' from dual;

'['||TZ_O
---------
[+01:00 ]

      

        
        
        
      

    

with what appears to be extra space; although copying and pasting from a SQL Developer table where it displays the same also loses the closing bracket:

'['||TZ_O
---------
[+01:00

      

        
        
        
      

    

Dropping the generated value shows the problem:

select dump('[' || tz_offset('Europe/Amsterdam') || ']') from dual;

DUMP('['||TZ_OFFSET('EUROPE/AMSTERDAM')||']')
---------------------------------------------
Typ=1 Len=9: 91,43,48,49,58,48,48,0,93

      

        
        
        
      

    

Note the 0 byte, which is a null character and is causing confusion.

Since the offset format is fixed, you can avoid this with a simple substr()

:

select '[' || substr(tz_offset('Europe/Amsterdam'), 1, 6) || ']' from dual;

'['||SUB
--------
[+01:00]

select dump('[' || substr(tz_offset('Europe/Amsterdam'), 1, 6) || ']') from dual;

DUMP('['||SUBSTR(TZ_OFFSET('EUROPE/AMSTERDAM'),1,6)||']')
---------------------------------------------------------
Typ=1 Len=8: 91,43,48,49,58,48,48,93

      

        
        
        
      

    

... or options replace()

or trim()

mentioned in comments; or anything else that removes the rogue character; even:

rtrim(tz_offset('Europe/Amsterdam'), chr(0))

      

        
        
        
      

    

Since the bugs are not visible in MoS, I'm not sure when (or if) it was fixed. It looks like it exists in 11.2.0.0 and I see a thing in 11.2.0.4 - it could be fixed in 12c. I don't see it in 12.1.0.2.