Home » SQL & PL/SQL » SQL & PL/SQL » multibyte characters to a csv file (10g,xp)
multibyte characters to a csv file [message #436247] Tue, 22 December 2009 06:28 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I have to write the multi byte characters fetched from a sql query to a csv file .

Any ideas!!!
Re: multibyte characters to a csv file [message #436250 is a reply to message #436247] Tue, 22 December 2009 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about the classical ways to generate a csv file?

Regards
Michel
Re: multibyte characters to a csv file [message #436315 is a reply to message #436250] Tue, 22 December 2009 21:48 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
select 'This' || ',' || 'Is' || ',' || 'Test' from dual;


Jsut Spool it or Put it in variable and DBMS OUT

hope this help

Rahul

[Updated on: Tue, 22 December 2009 21:48]

Report message to a moderator

Re: multibyte characters to a csv file [message #436336 is a reply to message #436250] Tue, 22 December 2009 22:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
What about the classical ways to generate a csv file?

Regards
Michel


Michel,are you talking about UTL_FILE??
However I have achieved the requirement by using Oracle SQL Developer tool.

Rahul,
An example for you of multibyte characters
select 'सेन्गुप्त' from dual



Re: multibyte characters to a csv file [message #436338 is a reply to message #436336] Tue, 22 December 2009 22:36 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Ayush,

Does not matter, To write csv you will have to either spool the file with comma separated or DBMS output the Comma separated Only difference is what char-Set you are using accordingly you will get the output.( This Easiest way )

Regards,
Rahul

Re: multibyte characters to a csv file [message #436339 is a reply to message #436336] Tue, 22 December 2009 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SQL> set colsep ","
SQL> select * from employer;

	ID,NAME      ,	  SALARY,     MONTH
----------,----------,----------,----------
	 1,Eric      ,	    1500,	  6
	 1,Eric      ,	    2000,	  7
	 1,Eric      ,	    1800,	  8
	 2,Steve     ,	    2200,	  7
	 2,Steve     ,	    1200,	  8
Re: multibyte characters to a csv file [message #436341 is a reply to message #436339] Tue, 22 December 2009 22:44 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
BlackSwan,Rahul

This works for singlebyte characters but as far as I know sqlplus does not works with multibyte characters..
Re: multibyte characters to a csv file [message #436343 is a reply to message #436339] Tue, 22 December 2009 22:49 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
BlackSwan this is Simple and nice way but don't you think so that it will increase the size of file ( if data is too big ) since this puts , comma after complete length of column not after immediate the records !!!

Regards,
Rahul
Re: multibyte characters to a csv file [message #436344 is a reply to message #436341] Tue, 22 December 2009 22:54 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Ayush,

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 



what is the CHARACTERSET you are using ( Use above query) ?
Re: multibyte characters to a csv file [message #436346 is a reply to message #436344] Tue, 22 December 2009 22:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ; 
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET'
                       *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>  SELECT USERENV ('language') FROM DUAL;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.UTF8
Re: multibyte characters to a csv file [message #436347 is a reply to message #436343] Tue, 22 December 2009 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>BlackSwan this is Simple and nice way but don't you think so that it will increase the size of file ( if data is too big ) since this puts , comma after complete length of column not after immediate the records !!!

1) When I can buy a 1TB disk for $80 (US Dollar) I am not concerned about disk space all that much.

2) It was a Quick & Dirty example to show what could be done.

3) If you are obsessed with disk space you are free to enumerate each column_name & apply RTRIM() or LTRIM() function to each.

[Updated on: Tue, 22 December 2009 22:57]

Report message to a moderator

Re: multibyte characters to a csv file [message #436349 is a reply to message #436347] Tue, 22 December 2009 23:01 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Quote:

1) When I can buy a 1TB disk for $80 (US Dollar) I am not concerned about disk space all that much.


you have $80 , other may not !! another thing user is trying to Create CSV file may be for further processing and not for FUN so in my opinion we should keep the output as small as possibel so further process time is reduced or become easy even for transfer the data or for Storage.

hope you are Agree with me !!

-Rahul

[Updated on: Tue, 22 December 2009 23:04]

Report message to a moderator

Re: multibyte characters to a csv file [message #436354 is a reply to message #436343] Tue, 22 December 2009 23:44 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Guys ,

I am not able to see multi byte characters in SqlPlus
SQL>  ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

Session altered.

SQL>  ALTER SESSION SET NLS_TERRITORY='CHINA';

Session altered.

SQL> ed r

SQL> @ r

PARTY_NAME                                         ENDUSER_CITY                                                 CITY
-------------------------------------------------- -------------------------------------------------
¿                                                  ¿¿                                                           ¿¿


If you are able to see please copy paste your session Smile
Re: multibyte characters to a csv file [message #436355 is a reply to message #436354] Tue, 22 December 2009 23:59 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

Session altered.

SQL> ALTER SESSION SET NLS_TERRITORY='CHINA';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
23-12┐ -09

SQL> select to_char(sysdate,'Day Month yyyy','NLS_DATE_LANGUAGE = ARABIC') from dual;

TO_CHAR(SYSDATE,'DAY
--------------------
┐┐┐┐┐┐┐┐ ┐┐┐┐┐┐ 2009

SQL> select to_char(sysdate,'Day Month yyyy') from dual;

TO_CHAR(SYSDATE,
----------------
┐┐┐    12┐  2009

SQL> select to_char(sysdate,'Day Month yyyy','NLS_DATE_LANGUAGE = RUSSIAN') from dual;

TO_CHAR(SYSDATE,'DAYMONTH
-------------------------
┐┐┐┐┐       ┐┐┐┐┐┐┐  2009

SQL> select to_char(sysdate,'DD Month YYYY','NLS_CALENDAR=''Arabic Hijrah''') from dual;

TO_CHAR(SYSDATE,'DDMO
---------------------
06 ┐┐┐┐          1431




SRIRAM Wink
Re: multibyte characters to a csv file [message #436356 is a reply to message #436355] Wed, 23 December 2009 00:02 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Sriram,
What does your post implies?

Re: multibyte characters to a csv file [message #436357 is a reply to message #436355] Wed, 23 December 2009 00:02 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
I just wanna post this ayush ....This may help you if you think this is irrelevant ignore it

sriram Smile

[Updated on: Wed, 23 December 2009 00:04]

Report message to a moderator

Re: multibyte characters to a csv file [message #436358 is a reply to message #436357] Wed, 23 December 2009 00:11 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
For me too , That is Strange

SQL>ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

Session altered.

SQL> ALTER SESSION SET NLS_TERRITORY='CHINA';

Session altered.

SQL>select sysdate from dual;

SYSDATE
----------
23-12¿ -09

SQL> select to_char(sysdate,'Day Month yyyy','NLS_DATE_LANGUAGE = ARABIC') from dual;

TO_CHAR(SYSDATE,'DAY
--------------------
¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿ 2009

SQL>select to_char(sysdate,'Day Month yyyy','NLS_DATE_LANGUAGE = RUSSIAN') from dual;

TO_CHAR(SYSDATE,'DAYMONTH
-------------------------
¿¿¿¿¿       ¿¿¿¿¿¿¿  2009

SQL>


Me confused.
Re: multibyte characters to a csv file [message #436359 is a reply to message #436357] Wed, 23 December 2009 00:11 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I found evidence to support my fact

Configuring Your Client To Be a Unicode Client on Windows:

On Windows you cannot use sqlplusw.exe or sqlplus.exe as a Unicode / UTF8 client. Using sqlplusw.exe with NLS_LANG set to AL32UTF8 is totally incorrect. There are 2 Oracle provided Unicode clients you can use on Windows: iSQL*Plus and Oracle SQL Developer. If you want to use / write a Unicode application on Windows then the application should be specifically written to use the Windows Unicode API, setting NLS_LANG to AMERICAN_AMERICA.AL32UTF8 is not enough. You should consult the application vendor or the vendor of the development environment to see how to properly code in Unicode.

The popular tool TOAD is NOT able to run against (AL32)UTF8 databases. Toad does not support databases with a Unicode character set such as UTF8 or AL16UTF16. The International version of Toad is the only version of Toad that does support (AL32)UTF8.

All Oracle 9i and up clients are compatible with a AL32UTF8 database, even if they use a non-AL32UTF8 NLS_LANG setting:
http://repettas.wordpress.com/2008/05/15/al32utf8utf8-unicode-database-character-set-implications/
Re: multibyte characters to a csv file [message #436434 is a reply to message #436359] Wed, 23 December 2009 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>All Oracle 9i and up clients are compatible with a AL32UTF8 database
Is sqlplus client software?
Is answer Operating System dependent?
Re: multibyte characters to a csv file [message #436449 is a reply to message #436359] Wed, 23 December 2009 11:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Try reading the following note from metalink : 158577.1

Regards

Raj
Re: multibyte characters to a csv file [message #436519 is a reply to message #436449] Wed, 23 December 2009 23:01 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks Rajaram
This note says Quote:
Removed

[Updated on: Thu, 24 December 2009 01:21] by Moderator

Report message to a moderator

Re: multibyte characters to a csv file [message #436535 is a reply to message #436519] Thu, 24 December 2009 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT quote anything from Metalink it is illegal.
Read the terms of your contract with Oracle.

Quote:
Unless specifically provided in your licensing or distribution agreement with Oracle, the Materials may not be used to provide services for or to third parties and may not be shared with or accessed by third parties.


Regards
Michel

Re: multibyte characters to a csv file [message #436539 is a reply to message #436535] Thu, 24 December 2009 01:46 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks Michel Smile
Previous Topic: outer join and exsists
Next Topic: backup and restore the view
Goto Forum:
  


Current Time: Thu Sep 29 19:15:04 CDT 2016

Total time taken to generate the page: 0.05809 seconds