develooper Front page | perl.dbi.users | Postings from December 2017

Oracle encoding query

Thread Next
From:
H.Merijn Brand
Date:
December 6, 2017 14:50
Subject:
Oracle encoding query
Message ID:
20171206155011.0d31d231@pc09.procura.nl
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 ë

-- 
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/

Thread Next


nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About