Re: Oracle encoding query

"H.Merijn Brand" <[email protected]> Wed, 6 Dec 2017 16:12:53 +0100
Newsgroups	gmane.comp.lang.perl.modules.dbi.general
Message-ID	<[email protected]>
On Wed, 6 Dec 2017 15:50:11 +0100, "H.Merijn Brand"
<[email protected]> wrote:

> I have an Oracle databse with NLS_CHARACTERSET US7ASCII
> 
> I have a table "land", like
> 
> create table land (
>   c_land               number     (4) not null,
>   zoekarg              varchar2   (5),
>   land                 varchar2  (40),
>   diac                 number     (1),
>   d_in                 number     (8),
>   d_end                number     (8),
>   opm                  varchar2 (150),
>   mut                  number     (1),
>   icao                 varchar2   (3),
>   land_u               varchar2  (80)
>   );
> 
> In there the field "land" has the content *without* special characters,
> like
> 
>   Zuidrhodesie
> 
> the special characters are stored elsewhere, indicated by the field diac
> 
> The field land_u contains the extended land *with* diacriticals, like
> 
>   Zuidrhodesië
>              ^
> 
> in UTF-8 encoding. Note that this is possible because of US7ASCII
> 
> what is stored in the database is
> 
>   Zuidrhodesi\303\253
> 
> using perl to extract that makes displaying those values easy, but I
> have no idea how I can get programs like SQL Developer to show that
> content the way it is intended
> 
> Is there an oracle function I could use to convert byte-encode UTF-8
> to something SQL developer "understands"
> 
>   select utf8_bytes_to_utf16_for_sql_developer (land_u) from land;
> 
> any hint is welcome. Currently SQL Developer will show
> 
>   Zuidrhodesi��
> 
> where the trailing �'s are both \x0fffd (\N{REPLACEMENT CHARACTER}),
> which is not really helpful
> 
> I have been playing with several variants of
> 
>   select convert (land_u, 'AL16UTF16', 'UTF8') from land where c_land = 7072;
> 
> but I didn't get SQL Developer to show the ë

 select utl_encode.text_encode (land_u) from land where c_land = 7072;

=>

 Zuidrhodesi=C3=AB

so SQL developer *does* see the individual bytes as they are stored

I can get the expected display with

 select utl_i18n.raw_to_nchar (utl_i18n.string_to_raw (land_u), 'utf8') from land where c_land = 7072;

which experiences as rather overcomplicated, esp if I need to do this
for all _u fields in the query :(

-- 
H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
using perl5.00307 .. 5.27   porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/        http://www.test-smoke.org/
http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/
signature.asc (application/pgp-signature, 473 B)