Home » SQL & PL/SQL » SQL & PL/SQL » Writing Turkey characters to text file (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production, TNS for Linux: Version 11.2.0.3.0 - Production )
| Writing Turkey characters to text file [message #597827] |
Tue, 08 October 2013 10:31  |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
T
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I am trying to write the Turkey character stored in the table in VARCHAR2 to Unix file. But when the text is written to the unix, the characters are coming as junk.
The output of the file it is writing is as below after the execution
Fis Içe Aktarma Olusturuldu Header
Fis Içe Aktarma Olusturuldu
Fiş İçe Aktarma Oluşturuldu
Instead I expect the chararecter to be as "Fiş İçe Aktarma Oluşturuldu Header" which when converted to English will show as "Created Import Plug Header".
Your help much appreciated
DROP TABLE TEST_JUNK_CHAR
/
CREATE TABLE TEST_JUNK_CHAR (primary_description VARCHAR2(400))
/
INSERT INTO TEST_JUNK_CHAR VALUES('Fiş İçe Aktarma Oluşturuldu Header')
/
COMMIT
/
CREATE OR REPLACE
PROCEDURE GEMTC_PRIM_SEC_LEDGER_TEST_1(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_outputfile_path IN VARCHAR2)
AS
lv_filename VARCHAR2(32767);
lv_prim_sec_feed VARCHAR2(32767);
lv_prim_sec_head VARCHAR2(32767);
lv_inv_out_file VARCHAR2(32767);
lv_exp_out_file VARCHAR2(32767);
lv_error_msg VARCHAR2(32767);
ln_req_id NUMBER;
ln_req_id_exl NUMBER;
ln_finished BOOLEAN;
ln_message VARCHAR2(240);
ln_sub_status BOOLEAN := FALSE;
a NUMBER;
lv_output UTL_FILE.FILE_TYPE;
lv_label VARCHAR2(100) := 'GEMTC Primary Secondary Ledger feed' ;
BEGIN
BEGIN
SELECT 'GEMTC_GL_PRI_SEC_'
||TO_CHAR(SYSDATE, 'ddmmyyyyHH24MISS')
||'.txt'
INTO lv_filename
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
lv_filename := 'GEMTC_GL_PRI_SEC_'||TO_CHAR(SYSDATE, 'ddmmyyyyHH24MISS')||'.txt';
END;
lv_inv_out_file := p_outputfile_path || '/' || lv_filename;
lv_exp_out_file := 'GEMTC_PRI_SEC_EXCEPTION_'||TO_CHAR(SYSDATE, 'ddmmyyyyHH24MISS')||'.txt';
lv_output := UTL_FILE.FOPEN (p_outputfile_path, lv_filename, 'W', 32767); --FOPEN_NCHAR
-- lv_output := UTL_FILE.FOPEN_NCHAR (p_outputfile_path, lv_filename, 'W', 32767);
/*
l_file := utl_file.fopen('OUT', 'a.txt', 'w');
utl_file.put_line(l_file,
convert('Rosëttenville', 'WE8ISO8859P1', 'UTF8'));
*/
lv_prim_sec_head := 'Fis Içe Aktarma Olusturuldu Header';
-- UTL_FILE.PUT_LINE (lv_output, lv_prim_sec_head);
UTL_FILE.PUT_LINE (lv_output, lv_prim_sec_head);
UTL_FILE.PUT_LINE ( lv_output, CONVERT('Fis Içe Aktarma Olusturuldu', 'UTF8', 'UTF8') );
FOR i IN
(SELECT primary_description||CHR(9) PD FROM TEST_JUNK_CHAR
)
LOOP
lv_prim_sec_feed := i.PD;
UTL_FILE.PUT_LINE (lv_output, lv_prim_sec_feed);
--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,lv_prim_sec_feed);
END LOOP;
UTL_FILE.FCLOSE (lv_output);
EXCEPTION
WHEN UTL_FILE.invalid_path THEN
dbms_output.put_line( 'problem IN FCLOSE - invalid_path ,Please Check..');
UTL_FILE.fclose (lv_output);
RAISE_APPLICATION_ERROR (-20100, 'Invalid FILE Path');
WHEN UTL_FILE.invalid_mode THEN
dbms_output.put_line( 'problem in fclose - invalid_mode');
UTL_FILE.fclose (lv_output);
RAISE_APPLICATION_ERROR (-20101, 'Invalid FILE MODE');
WHEN UTL_FILE.invalid_filehandle THEN
dbms_output.put_line( 'problem in fclose - invalid_filehandle');
UTL_FILE.fclose (lv_output);
RAISE_APPLICATION_ERROR (-20102, 'Invalid FILE Handle');
WHEN UTL_FILE.invalid_operation THEN
dbms_output.put_line( 'problem in fclose - invalid_operation');
UTL_FILE.fclose (lv_output);
RAISE_APPLICATION_ERROR (-20103, 'Invalid FILE Operation');
WHEN UTL_FILE.write_error THEN
dbms_output.put_line( 'problem in fclose - write_error');
UTL_FILE.fclose (lv_output);
RAISE_APPLICATION_ERROR (-20104, ' FILE WRITE Error');
WHEN UTL_FILE.internal_error THEN
dbms_output.put_line( 'problem in fclose - internal_error');
UTL_FILE.fclose (lv_output);
RAISE_APPLICATION_ERROR (-20105, 'Internal Oracle Error');
WHEN UTL_FILE.CHARSETMISMATCH THEN
dbms_output.put_line( 'problem in Char set Mismatch - charsetmismatch');
UTL_FILE.fclose (lv_output);
RAISE_APPLICATION_ERROR (-20105, 'CHARSETMISMATCH Oracle Error');
WHEN OTHERS THEN
lv_error_msg := SUBSTR(sqlerrm,1,200);
dbms_output.put_line( 'Unknown error SQLCODE =' || TO_CHAR (SQLCODE) || ' SQLERROR MESSAGE =' || SUBSTR (SQLERRM, 1, 200) );
RAISE_APPLICATION_ERROR (-20106, SUBSTR(SQLERRM,100));
END GEMTC_PRIM_SEC_LEDGER_TEST_1;
/
DECLARE
err VARCHAR2(32767);
Code NUMBER;
BEGIN
GEMTC_PRIM_SEC_LEDGER_TEST_1(err, Code, '/arpogax1/erpcsf/temp');
DBMS_OUTPUT.PUT_LINE('Error : ' || err);
END;
/
|
|
|
|
|
|
| Re: Writing Turkey characters to text file [message #597874 is a reply to message #597831] |
Wed, 09 October 2013 01:26  |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
I have tried using NVARCHAR2 as below but getting the same issue. The output is coming as junk. Any help really aprreciated
Fiş İçe Aktarma Oluşturuldu
FiÃ...Ÿ İçe Aktarma OluÃ...Ÿturuldu
Fiş İçe Aktarma Oluşturuldu
SQL> desc TEST_JUNK_NVARCHAR;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRIMARY_DESCRIPTION NVARCHAR2(1000)
create or replace
PROCEDURE GEMTC_PRIM_SEC_LEDGER_TEST_2(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_outputfile_path IN VARCHAR2)
AS
lv_filename VARCHAR2(32767);
lv_prim_sec_feed NVARCHAR2(32767);
lv_prim_sec_head NVARCHAR2(32767);
lv_inv_out_file VARCHAR2(32767);
lv_exp_out_file VARCHAR2(32767);
lv_error_msg VARCHAR2(32767);
ln_req_id NUMBER;
ln_req_id_exl NUMBER;
ln_finished BOOLEAN;
ln_message VARCHAR2(240);
ln_sub_status BOOLEAN := FALSE;
a NUMBER;
lv_output UTL_FILE.FILE_TYPE;
lv_label VARCHAR2(100) := 'GEMTC Primary Secondary Ledger feed' ;
BEGIN
BEGIN
SELECT 'GEMTC_GL_PRI_SEC_'
||TO_CHAR(SYSDATE, 'ddmmyyyyHH24MISS')
||'.txt'
INTO lv_filename
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
lv_filename := 'GEMTC_GL_PRI_SEC_'||TO_CHAR(SYSDATE, 'ddmmyyyyHH24MISS')||'.txt';
END;
lv_inv_out_file := p_outputfile_path || '/' || lv_filename;
lv_output := UTL_FILE.FOPEN_NCHAR (p_outputfile_path, lv_filename, 'W', 32767); --FOPEN_NCHAR
lv_prim_sec_head := 'Fiş İçe Aktarma Oluşturuldu';
--UTL_FILE.PUT_LINE (lv_output, lv_prim_sec_head);
DBMS_OUTPUT.PUT_LINE( 'lv_prim_sec_head :' || lv_prim_sec_head);
UTL_FILE.PUT_LINE_NCHAR (lv_output, lv_prim_sec_head);
UTL_FILE.PUT_LINE_NCHAR ( lv_output, CONVERT('Fiş İçe Aktarma Oluşturuldu', 'UTF8', 'WE8ISO8859P1') );
FOR i IN
(SELECT primary_description||CHR(9) PD FROM TEST_JUNK_NVARCHAR
)
LOOP
lv_prim_sec_feed := i.PD;
UTL_FILE.PUT_LINE_NCHAR (lv_output, lv_prim_sec_feed);
--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,lv_prim_sec_feed);
END LOOP;
UTL_FILE.FCLOSE (lv_output);
END GEMTC_PRIM_SEC_LEDGER_TEST_2; |
|
|
|
|
|
| Re: Writing Turkey characters to text file [message #597894 is a reply to message #597884] |
Wed, 09 October 2013 03:06 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Thank you.
When I tried writing "Fiş İçe Aktarma Oluşturuldu" manually in Unix using Winscp or vi editor, the characters are converting to Junk. Does this mean that the OS does not support this. I am pretty sure that the database is in the same OS where the file is getting written with UTL_FILE.
Will this need adding the font to the OS. Can we see the OS detail and any font supports from any database query. Any direction please.
Regards
Ajendra
|
|
|
|
| Re: Writing Turkey characters to text file [message #597895 is a reply to message #597894] |
Wed, 09 October 2013 03:09 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Below is the NLS_PARAMETER detail
SQL> select * from nls_instance_parameters;
PARAMETER VALUE
------------------------------------------------------------------------------------------ ---------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY america
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT DD-MON-RR
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS .,
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
PARAMETER VALUE
------------------------------------------------------------------------------------------ ---------
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL>
|
|
|
|
|
|
| Re: Writing Turkey characters to text file [message #597906 is a reply to message #597895] |
Wed, 09 October 2013 04:11 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Linux Support UTF8 and terminal does not ==> Does that mean that the UTL_FILE is writing correctly to the DB and when i see it or transfer it to my windows environment it is misleading. What should be done to bell out of this situation.
Here is the output fro database parameters
SQL> SQL> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
------------------------------ ---------------------------------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_NCHAR_CONV_EXCP FALSE
SQL> SQL> |
|
|
|
|
|
| Re: Writing Turkey characters to text file [message #597911 is a reply to message #597906] |
Wed, 09 October 2013 04:49 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Aju wrote on Wed, 09 October 2013 04:11Linux Support UTF8 and terminal does not ==> Does that mean that the UTL_FILE is writing correctly to the OS file and when i see it or transfer it to my windows environment it is misleading. What should be done to bell out of this situation.
Here is the output fro database parameters
SQL> SQL> select * from nls_database_parameters where parameter like '%CHAR%';
PARAMETER VALUE
------------------------------ ---------------------------------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_NCHAR_CONV_EXCP FALSE
SQL> SQL>
|
|
|
|
|
|
| Re: Writing Turkey characters to text file [message #597922 is a reply to message #597907] |
Wed, 09 October 2013 05:25 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Michel Cadot wrote on Wed, 09 October 2013 04:17
Quote:Does that mean that the UTL_FILE is writing correctly to the DB and when i see it or transfer it to my windows environment it is misleading.
Yes this is the main point.
But also some characters are not valid for your CONVERT call.
I am wondering how do I able to confirm if the characters are correct in linux. I am not really bothererd about the Convert call, that was just to test in case the writing to the file has an issue.
Is there any font installation or any setting that need to be done on my local Windows. But again it contradicts as i can see the said characters in the Windows which is correctly translated in google trnslator.
However when I see the font for "Fiş İçe Aktarma Oluşturuldu" in windows it shows "Calibri (Body)". When i see the same characters in another Linux system it show DejaVu Sans Mono
[Edit MC: move OP's answer outside quote]
[Updated on: Wed, 09 October 2013 05:58] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Writing Turkey characters to text file [message #598137 is a reply to message #597939] |
Thu, 10 October 2013 11:08 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
You are right Michael. I changed the terminal setting to UTF8 and getting the correct output. From Winscp I copied as binary and the data is opened correctly in notepad. Thank you very much for this.
In fact I was saving the output file ax .xls extension and when i was opening in excel it was taking this as junk
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:20:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
