OraFAQ Forum: SQL & PL/SQL » Arabic letters not displayed in xmltype table

Home » SQL & PL/SQL » SQL & PL/SQL » Arabic letters not displayed in xmltype table (11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Arabic letters not displayed in xmltype table [message #615216]

Sun, 01 June 2014 01:31

ledo60
Messages: 63
Registered: May 2014

Member

Hi,
I have a problem in Arabic display from both server and client
I have checked db nls and it's AL32UTF8 and setting Client value AMERICAN_AMERICA.AR8MSWIN,etc was n't helpfull .
Was it XML type issue .

Sample of output:ex.( O???,I?Ui? OiY,?I?I ?E?i),

O/S AIX DB 11g.2.3

Please advice .

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615219 is a reply to message #615216]

Sun, 01 June 2014 02:02

ledo60
Messages: 63
Registered: May 2014

Member

Case linked with this topic

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615223 is a reply to message #615219]

Sun, 01 June 2014 02:27

ledo60
Messages: 63
Registered: May 2014

Member

Am usin this query for data extraction ,
SCOTT@orcl12c> SELECT x.id, x.c1, y.c2
2 FROM table_name t,
3 XMLTABLE
4 ('//row'
5 PASSING t.column_name
6 COLUMNS
7 id NUMBER PATH '@id',
8 c1 VARCHAR2(15) PATH 'c1',
9 c2 XMLTYPE PATH 'c2') x,
10 XMLTABLE
11 ('//c2'
12 PASSING x.c2
13 COLUMNS
14 c2 VARCHAR2(15) PATH '.') y
15 /

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615224 is a reply to message #615219]

Sun, 01 June 2014 02:28

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ledo60 wrote on Sun, 01 June 2014 09:02

Case linked with this topic

Nothing is linked.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615226 is a reply to message #615216]

Sun, 01 June 2014 02:39

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

ledo60 wrote on Sun, 01 June 2014 12:01

DB 11g.2.3

Are you sure it's 11g? I think you are working on 12c, your DB is shown as :

ledo60 wrote on Sun, 01 June 2014 12:57

SCOTT@orcl12c>

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615227 is a reply to message #615219]

Sun, 01 June 2014 02:53

ledo60
Messages: 63
Registered: May 2014

Member

http://www.orafaq.com/forum/mv/msg/192815/614649/#msg_614649

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615228 is a reply to message #615227]

Sun, 01 June 2014 02:55

ledo60
Messages: 63
Registered: May 2014

Member

Dear Lalit Kumar B :
Thanks for your node , yes am sure 11.2.0.3.0 , this is the syntax which am using .

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615229 is a reply to message #615227]

Sun, 01 June 2014 03:36

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

ledo60 wrote on Sun, 01 June 2014 13:23

http://www.orafaq.com/forum/mv/msg/192815/614649/#msg_614649

Ok, now I get it. This topic is the subpart part of your previous topic.

I remember, once similar issue was addressed http://www.orafaq.com/forum/m/587605/?srch=arabic#msg_587605

Please post the results of the following :

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

select dump(column_name) from table_name where ...

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615231 is a reply to message #615229]

Sun, 01 June 2014 03:43

ledo60
Messages: 63
Registered: May 2014

Member

VALUE
--------------------------------------------------------------------------------
AL32UTF8

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615232 is a reply to message #615231]

Sun, 01 June 2014 03:46

ledo60
Messages: 63
Registered: May 2014

Member

Sample output

DUMP(table_name)
--------------------------------------------------------------------------------
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,179,143,0,0,0,3,89,0,0,0,1,16,151,0,112,0,0,0,1,9,167,203,

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615233 is a reply to message #615232]

Sun, 01 June 2014 04:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Post results of :

SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615235 is a reply to message #615233]

Sun, 01 June 2014 04:57

ledo60
Messages: 63
Registered: May 2014

Member

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
UTF8

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615236 is a reply to message #615235]

Sun, 01 June 2014 05:23

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Which client do you use? Please post the client session showing the unrecognized arabic characters.

To me it seems to be NLS_LANG issue with client. NLS_LANG needs to be set to match the character set.

Follow these steps per your OS.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615238 is a reply to message #615236]

Sun, 01 June 2014 05:52

ledo60
Messages: 63
Registered: May 2014

Member

I have the same results from client or from server side.
Sample of output:ex.( O???,I?Ui? OiY,?I?I ?E?i),

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615239 is a reply to message #615238]

Sun, 01 June 2014 05:59

ledo60
Messages: 63
Registered: May 2014

Member

when apply export NLS_LANG=AMERICAN_AMERICA.UTF8
output like
أ�أأأ� أأأ
أأ�أ�أ، أأأ�

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615243 is a reply to message #615216]

Sun, 01 June 2014 06:24

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

What encoding is specified in XML file?

SY.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615245 is a reply to message #615239]

Sun, 01 June 2014 06:41

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Did you correctly set the NLS_LANG with proper nls_characterset?

Also, do you have any MULTIBYTE characters?

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615247 is a reply to message #615245]

Sun, 01 June 2014 07:34

ledo60
Messages: 63
Registered: May 2014

Member

I applied this command

#export NLS_LANG=AMERICAN_AMERICA.UTF8
How can i check MULTIBYTE characters?
How can i check XML encoding table?

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615248 is a reply to message #615247]

Sun, 01 June 2014 07:37

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Again, what encoding is specified in XML?

SY.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615250 is a reply to message #615248]

Sun, 01 June 2014 07:49

ledo60
Messages: 63
Registered: May 2014

Member

How we can check the encoding is specified in XML?

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615260 is a reply to message #615250]

Sun, 01 June 2014 09:09

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

It saya it right there. E.g.

<?xml version="1.0" encoding="UTF-8"?>

SY.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615303 is a reply to message #615260]

Mon, 02 June 2014 02:45

ledo60
Messages: 63
Registered: May 2014

Member

Dear Solomon
Sorry what should i check this is a table .

Dear Lalit Kumar :
What is the next step

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615304 is a reply to message #615303]

Mon, 02 June 2014 02:50

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The first line of the actual XML data you are storing in the DB contains the encoding specification.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615305 is a reply to message #615304]

Mon, 02 June 2014 03:01

ledo60
Messages: 63
Registered: May 2014

Member

I checked every thing and table creation script as well , no enty for encoding, please advice if you have a query to check this.

[Updated on: Mon, 02 June 2014 03:07]

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615307 is a reply to message #615305]

Mon, 02 June 2014 03:22

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I say the first line of the data and you check table creation scripts?
It's in the xml data you stored in the DB. Get one of the XML's, from before it got stored in the DB, and read it.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615308 is a reply to message #615307]

Mon, 02 June 2014 03:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Here's an example XML I just pulled from the web, read the first line:

<?xml version="1.0" encoding="UTF-8"?>

<!-- Edited by XMLSpy -->
-<note><to>Tove</to><from>Jani</from><heading>Reminder</heading><body>Don't forget me this weekend!</body></note>

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615309 is a reply to message #615308]

Mon, 02 June 2014 04:00

ledo60
Messages: 63
Registered: May 2014

Member

1-As per my previous comment , i checked every thing not only table creation script.
2-am doing the following :select * from the xmltype table and no encoding appered in the first line .
Am ready to deliver what you want but unfortunitly not exist

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615310 is a reply to message #615309]

Mon, 02 June 2014 04:09

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The XML didn't appear in the DB by magic, it will have been inserted from another source. So go back to the original source of the XML and check it there. If that doesn't contain it then its not specified, in which case the default UTF-8 is used, which I imagine is the source of your issue.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615311 is a reply to message #615310]

Mon, 02 June 2014 04:51

ledo60
Messages: 63
Registered: May 2014

Member

Actualy the source is black box for me and can't reach this point so let us assume it's UUTF8

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615314 is a reply to message #615311]

Mon, 02 June 2014 05:01

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Why can't you capture the xml before it gets stored in DB? Look at the program that inserts the xml into the table. Find a way to see the xml encoding before insert happens.

[Updated on: Mon, 02 June 2014 05:04]

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615316 is a reply to message #615314]

Mon, 02 June 2014 05:18

ledo60
Messages: 63
Registered: May 2014

Member

It's Application screens, no clear code !

[Updated on: Mon, 02 June 2014 05:18]

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615380 is a reply to message #615316]

Tue, 03 June 2014 00:44

ledo60
Messages: 63
Registered: May 2014

Member

Any advice or new direction should we follow

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615397 is a reply to message #615380]

Tue, 03 June 2014 06:47

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

OK. You have column of XML type, right? If so, issue:

select xmltype_column_name from your_table;

SY.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615400 is a reply to message #615397]

Tue, 03 June 2014 07:20

ledo60
Messages: 63
Registered: May 2014

Member

The command is not working(syntax) .. when adding both coulmn and table name

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615401 is a reply to message #615400]

Tue, 03 June 2014 07:36

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

What command? You didn't post anything. For example:

SQL> desc tbl
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 X                                                  SYS.XMLTYPE

SQL> set long 1000
SQL> select x from tbl;

X
-------------------------------------------------------------------
<?xml version="1.0" encoding="US-ASCII"?>
<response>
  <statement stment="423112341234">
    <error code="113">
      <order id="20290003528699"/>
      <order id="20290003528703"/>
      <order id="20290003528703"/>
    </error>
  </statement>
</response>


SQL>

SY.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615403 is a reply to message #615401]

Tue, 03 June 2014 08:01

ledo60
Messages: 63
Registered: May 2014

Member

i got your point , the select statment get all table data with huge # of records

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615409 is a reply to message #615403]

Tue, 03 June 2014 09:22

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

So limit it with WHERE ROWNUM = 1.

SY.

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615450 is a reply to message #615409]

Wed, 04 June 2014 01:57

ledo60
Messages: 63
Registered: May 2014

Member

I got one record , no encoding entry .
can the below info help you from the output
xml:space='preserve'

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615482 is a reply to message #615450]

Wed, 04 June 2014 10:17

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I suspect that it's taking a default encoding. As CM already pointed out, it might be the root cause.

Also, you are yet to answer my question regarding any MULTIBYTE characters being used.

Unless you provide any useful information, Its really impossible to comment/conclude about the issue and its root cause. Since, only you would have much better knowledge about the issue than anyone of us here, so, what did you try with your analysis so far?

[Updated on: Wed, 04 June 2014 12:03]

Report message to a moderator

Re: Arabic letters not displayed in xmltype table [message #615760 is a reply to message #615482]

Mon, 09 June 2014 01:59

ledo60
Messages: 63
Registered: May 2014

Member

For sure am trying to deliver what is needed as much as i can .
as for MULTIBYTE
i issued this command
ex.
[code]
SQL> select length (name)
from test
/
[code]

and got sample output :

1518
933
1029
981
898
989
1282
1722
1261
1678
869

**if this is not the required data please advice

[Updated on: Mon, 09 June 2014 02:00]

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	date format as dd/mm/yyyy in the excel
Next Topic:	XMLAGG Function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:14:00 CDT 2024