CODEWITHSUNDEEP
oracle-databaseoracle11g
Alex Art.
Alex Art.

Reputation: 8781

Oracle - concatenating string with result of utl_raw.cast_to_varchar2 function

I am trying to concatenate a string to a result of utl_raw.cast_to_varchar2 function (which is also a string). It is supposed to be transparent, but I wasn't able to append anything to a result of utl_raw.cast_to_varchar2.

Here is an example:

select  utl_raw.cast_to_varchar2((nlssort('"' || CITY_NAME || ', ' || STATE_CODE || '"', 'nls_sort=binary_ai'))) || ' test' 
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual)

I expect the result to be "new york, ny" test but I only get "new york, ny"

Upvotes: 2

Views: 1405

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

This is a combination of what may be considered a bug - but which might actually be vital to how nlssort is used internally by Oracle - and how your client handles strings. In SQL Developer for instance, this appears to work as expected when as a statement or as a script, but I can't copy and paste the results from the worksheet or the query result grid.

Using the dump() function you can see the characters that make up the result:

select dump(utl_raw.cast_to_varchar2((nlssort('"' || CITY_NAME || ', ' || STATE_CODE || '"',
  'nls_sort=binary_ai'))) || ' test', 1016) as dumped_result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=1 Len=20 CharacterSet=AL32UTF8: 22,6e,65,77,20,79,6f,72,6b,2c,20,6e,79,22,0,20,74,65,73,74
                                                                              ^

I've marked a ^ marker to highlight a 0 that appears in that output, between the cast nlssort() result and the test you're adding. Or a bit more clearly without the concatenation:

select dump(utl_raw.cast_to_varchar2(nlssort('ABC')), 1016) as dumped_result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=1 Len=4 CharacterSet=AL32UTF8: 41,42,43,0

Or even better:

select dump(nlssort('ABC'), 1016) as dumped_result from dual;

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=23 Len=4: 41,42,43,0

The nlssort() call is adding a null byte, shown as 0 in the dump output, to the result. You client is seeing that as the end of the string, so even though Oracle is actually concatenating the strings, you just can't see the results.

You can remove the null, e.g. with rtrim(), before concatenating:

select rtrim(utl_raw.cast_to_varchar2(nlssort('"' || CITY_NAME || ', ' || STATE_CODE || '"',
  'nls_sort=binary_ai')), chr(0)) || ' test' as result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

RESULT                                                                                              
----------------------------------------------------------------------------------------------------
"new york, ny" test

or with the shorter original string:

select dump(rtrim(utl_raw.cast_to_varchar2(nlssort('ABC')), chr(0)), 1016) as dumped_result
from (select 'New York' as CITY_NAME, 'NY' as STATE_CODE from dual);

DUMPED_RESULT                                                                                       
----------------------------------------------------------------------------------------------------
Typ=1 Len=3 CharacterSet=AL32UTF8: 41,42,43

which you can see now has no trailing null character.

Upvotes: 4

Related Questions