Re: Oracle encoding query
On Wed, Dec 06, 2017 at 04:53:36PM +0100, H.Merijn Brand wrote: > On Wed, 6 Dec 2017 15:34:17 +, Tim Bunce > wrote: > > > A view might be useful. Or perhaps define your own function to wrap the > > expression. > > That worked very well! > > CREATE OR REPLACE FUNCTION diac_u (v VARCHAR2) RETURN NCHAR IS > BEGIN > RETURN utl_i18n.raw_to_nchar (utl_i18n.string_to_raw (v), 'utf8'); > END; > / > > select diac_u (land_u) from land where c_land = 7072; You might want to add DETERMINISTIC before the IS so the function can be used more efficiently in some cases. Tim.
Re: Oracle encoding query
On Wed, 6 Dec 2017 15:34:17 +, Tim Bunce wrote: > On Wed, Dec 06, 2017 at 04:12:53PM +0100, H.Merijn Brand wrote: > > On Wed, 6 Dec 2017 15:50:11 +0100, "H.Merijn Brand" > > > > > > 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 :( > > A view might be useful. Or perhaps define your own function to wrap the > expression. > > Tim. That worked very well! CREATE OR REPLACE FUNCTION diac_u (v VARCHAR2) RETURN NCHAR IS BEGIN RETURN utl_i18n.raw_to_nchar (utl_i18n.string_to_raw (v), 'utf8'); END; / select diac_u (land_u) from land where c_land = 7072; ⇒ Zuidrhodesië Thank you! -- 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/ pgpQQrlL5SfnD.pgp Description: OpenPGP digital signature
Re: Oracle encoding query
On Wed, Dec 06, 2017 at 04:12:53PM +0100, H.Merijn Brand wrote: > On Wed, 6 Dec 2017 15:50:11 +0100, "H.Merijn Brand" > > > > 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 :( A view might be useful. Or perhaps define your own function to wrap the expression. Tim.
Re: Oracle encoding query
On Wed, 6 Dec 2017 15:50:11 +0100, "H.Merijn Brand"
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_endnumber (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
Oracle encoding query
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_endnumber (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/
pgpmpQPsRNFz5.pgp
Description: OpenPGP digital signature
