OraFAQ Forum: SQL & PL/SQL » sending arabic sms to gsm from oracle database

Home » SQL & PL/SQL » SQL & PL/SQL » sending arabic sms to gsm from oracle database (11g )

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

sending arabic sms to gsm from oracle database [message #587483]

Sun, 16 June 2013 05:29

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

hi

i'm trying to send arabic sms to mobile but the the message come in this way "???"

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587486 is a reply to message #587483]

Sun, 16 June 2013 05:50

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't know the answer but this has already been asked and answered here, please do search.

Regards
Michel

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587489 is a reply to message #587486]

Sun, 16 June 2013 06:02

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

trust me i did search before i open this topic but i came out with nothing
thanks any way

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587490 is a reply to message #587483]

Sun, 16 June 2013 06:04

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

Senior Member

First of all, you did not mention the OS. Going forward, please read and follow the forum guidelines:

http://www.orafaq.com/forum/t/88153/0/

If the issue is with incorrect display of data due to language settings, then you need to check the NLS_LANG.

For Arabic (U.A.E), the NLS_LANG value is : ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256

Format to interpret : [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].

To set the NLS_LANG environment variable for Oracle databases, refer:-
http://docs.oracle.com/cd/E12102_01/books/AnyInstAdm784/AnyInstAdmPreInstall18.html

In the link, it is explained for both Windows and UNIX.

[Updated on: Sun, 16 June 2013 06:16]

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587536 is a reply to message #587490]

Mon, 17 June 2013 00:05

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

thanks alot
Mr Lalit

the OS is Suse linux enterprise Server 10(x86_64)Version 10 Patchlevel 2, the Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ERP 12.1 . in database message is readable but in Mobile its not readable and it appear like this "?????"

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587539 is a reply to message #587536]

Mon, 17 June 2013 00:19

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

Senior Member

It means those characters are not supported in the mobile. Can't help on that!!!

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587549 is a reply to message #587539]

Mon, 17 June 2013 00:50

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Or maybe the message is not correctly sent.
OP did not say which NLS_LANG his application which send SMS is using.

Regards
Michel

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587552 is a reply to message #587549]

Mon, 17 June 2013 01:00

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

select * from v$nls_parameters;

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YY
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET AR8ISO8859P6
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

this is from my database

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587554 is a reply to message #587552]

Mon, 17 June 2013 01:13

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

Senior Member

All parameters seem fine. Language is American, mobile has to be set to language AMERICAN ENGLISH.

Did you check if the DATA formed in DB before being sent as SMS is correct? Sometimes, you might face issue with MULTIBYTE characters.

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587555 is a reply to message #587554]

Mon, 17 June 2013 01:16

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

Did you check if the DATA formed in DB before being sent as SMS is correct? Sometimes, you might face issue with MULTIBYTE characters.
will be please explain ?

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587561 is a reply to message #587555]

Mon, 17 June 2013 01:38

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ First what is your database character set:

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

2/ Then post the result of the following query:

select dump(<sms text col>) from <sms table> where ...

for one text that is not correctly displayed.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587570 is a reply to message #587555]

Mon, 17 June 2013 02:05

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

Senior Member

For sure it has to be an issue with characterset.

The characterset defined in nls_database_parameters is defined at database level, i.e. when the database was created.
And characterset defined in v$nls_parameter is locale-specific.

Just query it as Michel suggested, and check if there is any variance among them.

See if this thread might help you in better understanding:-
http://www.orafaq.com/forum/t/50691/0/

[Updated on: Mon, 17 June 2013 02:08]

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587571 is a reply to message #587561]

Mon, 17 June 2013 02:08

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

1.
SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

VALUE
----------------------------------------
AR8ISO8859P6

2.
SQL> select dump(full_name) from per_all_people_f
2 where upper(known_as) like '%ISHAQ%' AND ROWNUM<2;

DUMP(FULL_NAME)
----------------------------------------------------------------------------------------------------
Typ=1 Len=22: 199,211,205,199,226,32,195,205,229,207,32,229,205,229,207,32,206,199,202,202,199,228

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587574 is a reply to message #587571]

Mon, 17 June 2013 02:19

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Put the following in a file (say, test.sql):

set echo off feed off head off lines 100 trimspool on 
set markup html on
spool result.html
select distinct client_charset from v$session_connect_info where lower(client_charset) != 'unknown';
select full_name from per_all_people_f  where upper(known_as) like '%ISHAQ%' AND ROWNUM<2;
spool off

In SQL*Plus, execute the file (@test.sql) and attach the result file.

Regards
Michel

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587580 is a reply to message #587574]

Mon, 17 June 2013 02:36

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

[url]d:\result.html[/url]
I'm attaching the result.html, but we cannot upload the file with extensions html.
So I change the file extension to .txt . Just change it back to html and view it.
Thanks

Attachment: result.txt
(Size: 0.27KB, Downloaded 1162 times)

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587582 is a reply to message #587580]

Mon, 17 June 2013 02:45

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

Senior Member

The client-side characterset is : AR8ISO8859P6
And the data is : ÇÓÍÇÞ ÃÍãÏ ãÍãÏ ÎÇÊÊÇá

Is that Arabic?

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587583 is a reply to message #587582]

Mon, 17 June 2013 03:00

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

Hi,
Thats is not arabic.
Maybe you dont have arabic installed on your pc. So its displaying junk.
Regards

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587584 is a reply to message #587583]

Mon, 17 June 2013 03:07

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, this is not the problem.
If your client is correctly set to send Arabic characters then the HTML output should show Arabic characters when opened with a browser.
Do you see Arabic characters when you open result.html in your Internet browser?

Regards
Michel

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587586 is a reply to message #587584]

Mon, 17 June 2013 03:36

bahaelsharif
Messages: 10
Registered: June 2013
Location: QATAR

Junior Member

YES its showing arabic characters in browser

please downlod the attached file and remove the .txt extention

Attachment: result.html.txt
(Size: 0.27KB, Downloaded 1176 times)

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587587 is a reply to message #587583]

Mon, 17 June 2013 04:12

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

Senior Member

bahaelsharif wrote on Mon, 17 June 2013 13:30

Hi,
Thats is not arabic.
Maybe you dont have arabic installed on your pc. So its displaying junk.
Regards

That's quite obvious and not an issue when viewed at our side.

Because for me the NLS_LANG = american_america.WE8ISO8859P1, similarly for Michel it might be French. If your's is set to Arabic, you can definitely view it correctly.

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587600 is a reply to message #587587]

Mon, 17 June 2013 06:46

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Not so obvious as any Internet browser is able to display any character even not configure in the OS.
This is why I asked for an HTML spool.
See for instance: اسمي ميشيل

bahaelsharif, can you see Arabic in your spool file (there is no reason you see something different from us that is what Lalit Kumar B has posted)?

Regards
Michel

[Updated on: Mon, 17 June 2013 06:49]

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587602 is a reply to message #587600]

Mon, 17 June 2013 06:52

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

Senior Member

Okay, so you are saying even though these characters are not known by the OS(depending on the locale-specific characterset), however, the browser could be rich enough to show these characters. Fair enough.

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587605 is a reply to message #587600]

Mon, 17 June 2013 06:57

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

Senior Member

Michel Cadot wrote on Mon, 17 June 2013 17:16

bahaelsharif, can you see Arabic in your spool file?

Yes he has already stated that he is able to see the characters as Arabic.

bahaelsharif wrote on Mon, 17 June 2013 14:06

YES its showing arabic characters in browser

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587608 is a reply to message #587600]

Mon, 17 June 2013 07:13

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And the SMS bahaelsharif sent us is:
اسحاق أحمد محمد خاتتال

Regards
Michel

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587612 is a reply to message #587605]

Mon, 17 June 2013 07:20

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Yes he has already stated that he is able to see the characters as Arabic.

So the SQL*Plus session (if it is really a SQL*Plus session) was not started with a correct NLS_LANG.

To summarize:
1/ The data are correct in the database
2/ The client program is not properly set to send the data

Regards
Michel

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587633 is a reply to message #587612]

Mon, 17 June 2013 10:27

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

Senior Member

@Michel,

So the clear picture per my understanding is:-

1. Client setting will override anything set at the database level.
2. If OP is using a SQL*Plus session, then SQL*Plus is the client.
3. So, NLS_LANG needs to be set to match the character set that his client system supports.
4. As OP has mentioned it is Linux OS, so it would be defined as an environment variable.(Can you throw some light on this Michel, since I have knowledge about windows registry for NLS_LANG, but no idea about how it works with LINUX).

Thanks,
Lalit

Report message to a moderator

Re: sending arabic sms to gsm from oracle database [message #587635 is a reply to message #587633]

Mon, 17 June 2013 10:42

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1. Yes and no. It can't override database character set (for the data stored inside the database).
2. Yes
3. Yes
4. Yes:
export NLS_LANG=<language>_<country>.UTF8
By default Linux character set is UTF8.

Regards
Michel

Report message to a moderator