On Wed, 6 Dec 2017 15:50:11 +0100, "H.Merijn Brand" <h.m.br...@xs4all.nl> 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/
pgpWFrCnVaEKc.pgp
Description: OpenPGP digital signature
