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

Oracle encoding query

Thread Next
H.Merijn Brand
December 6, 2017 14:50
Oracle encoding query
Message ID:
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,


the special characters are stored elsewhere, indicated by the field diac

The field land_u contains the extended land *with* diacriticals, like


in UTF-8 encoding. Note that this is possible because of US7ASCII

what is stored in the database is


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


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   Perl Monger
using perl5.00307 .. 5.27   porting perl5 on HP-UX, AIX, and openSUSE

Thread Next Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at | Group listing | About