Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Write a unicode nclob into a text file
btidba
post Sep 27 2017, 10:23 AM
Post #1


Advanced Member
***

Group: Members
Posts: 174
Joined: 30-September 13
Member No.: 49,003



Hi,

I have to write a PL/Sql code which writes a file with the chinese caracters .I use for this the call of DBMS_XSLPROCESSOR.CLOB2FILE ,the syntax is :

DBMS_XSLPROCESSOR.CLOB2FILE(v_file, DIR_EXP, fileName,871)

- v_file is a NCLOB variable and before writing in the file I use DBMS_LOB.append(v_file,to_nchar(A.field)) to append a line to the NCLOB variable , where A.field is a column NVARCHAR type ( I've put a chinese value in it )

-871 is the nls character set of UTF8

but when I check in the text file I find żż instead of the chinese caracters, could you help to resolve this or if you can suggest another procedure other than DBMS_XSLPROCESSOR.CLOB2FILE which allow writing a large file with the chinese caracters?

Thanks
Go to the top of the page
 
+Quote Post
btidba
post Sep 28 2017, 04:25 PM
Post #2


Advanced Member
***

Group: Members
Posts: 174
Joined: 30-September 13
Member No.: 49,003



Hi Don.

Could you check.please my request?

I'd thank you in advance 😉
Go to the top of the page
 
+Quote Post
burleson
post Sep 28 2017, 05:27 PM
Post #3


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi,

This is a DBA forum, and I have no idea!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
btidba
post Sep 28 2017, 06:25 PM
Post #4


Advanced Member
***

Group: Members
Posts: 174
Joined: 30-September 13
Member No.: 49,003



This is not the 1st time I access to this forum I have almost all the time posted Pl/sql questions and got responses! This is why I asked the question...
Go to the top of the page
 
+Quote Post
burleson
post Sep 29 2017, 08:30 AM
Post #5


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi,

It's okay to post any Oracle question here, just know that we may not be able to help!

Take care. . .


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Oct 2 2017, 01:09 AM
Post #6


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



Hello btidba,

Microsoft Windows text file (.txt) doesnt support special strings. You can try either .doc or .docx instead of .txt type to find your foreign characters.

Kindly try the above suggestion and let us know if you are stuck anywhere smile.gif

Cheers!


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
btidba
post Oct 8 2017, 10:50 AM
Post #7


Advanced Member
***

Group: Members
Posts: 174
Joined: 30-September 13
Member No.: 49,003



Hi,

Thanks for the feedback. You're right using Word I can see the chinese character. But still have an issue with the unicode file because I need to display it in this txt file which is used by another process .For info,my DB is under 11G and nls_characterset=WE8MSWIN1252.Now I don't use DBMS_XSLPROCESSOR.CLOB2FILE to right the unicode form my 1 byte DB I had the information that it cannot do it. I'am testing with another procedures now, let me give you all the item necessary to reproduce fromp your side:

I share with you the scripts which allow to reproduce the issue dwnload them from http://www76.zippyshare.com/pd/qb74pt7U/4644/Unicode.7z .Please follow the next steps:

1- Run Install_script.sql, this will install:

a- the tunicode table which contains 1 row and many various fields

b- the procedure WriteUnicodeFile which read from tunicode file and store the record in nclob and then write the unicode file (uou should change the file directory I used EXPORT) ,I comment in it a call of a procedure write_nclob which do the same job as Clob2file.

2- Once installed run the 1st_data.sql to insert the 1st data in tunicode table , note: after inserting the record you should modify via your db client tool the field f68 which contains '??' and replace the value by whatever chinese caracters (let's say 網站).

3- in sql session run : exec WriteUnicodeFile;
4- Open the generated file.txt and check the chinese is correctly inserted /displayed (I got the record displayed in attached right_unicode_reco)
5- Run fill_tunicode.sql this script fill all the varchar2/nvarchar2 fields of the tunicode table up to their max size
6- Run again exec WriteUnicodeFile;
7- open the file.txt again and check the wrong interpretation of the chinese characters, see the result I got in wrong_unicode_reco image.

I got the same result either using Clob2file or write_nclob procedures's calls. It seems that when the record exceeds a certain size limit it write wrong characters in the unicode file (?)

Please check and advise.

Thanks
Attached File(s)
Attached File  wrong_unicode_reco.png ( 15.51K ) Number of downloads: 0
Attached File  right_unicode_reco.png ( 11.97K ) Number of downloads: 0
 
Go to the top of the page
 
+Quote Post
burleson
post Oct 9 2017, 02:17 PM
Post #8


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi,

This forum is for answering specific Oracle questions!

This looks like a service request.

I would log a service request on MOSC.

Http://support.oracle.com

If you don't have Oracle Support services, just call 800-766-1884 and we can assign a programmer to help you.

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 03:46 PM