Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Special character while dbms_output (11g)
Oracle Special character while dbms_output [message #652449] |
Thu, 09 June 2016 05:33  |
harshadsp
Messages: 100 Registered: August 2008
|
Senior Member |

|
|
Hello-
I am reading a flat file using utl_file and the file have some special characters. I need to capture the row and column number for such characters from the file. Based on some validations, I am trying to dbms_output the line which has special chars like Ã, é, ç, Symbols (like ©, ®, ™), etc. but the dbms_out put is adding one more special characters.
E.g.I have a line 'Bernice LM Café Inc' in flat file but at the dbms output its displaying as 'Bernice LM Café Inc'. I am not sure why the 'ƒÂ' is added in between. Could you please advise how I should read this and dbms output with same string?
This is a part of code with dbms_output.
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
w_lcnt := w_lcnt + 1;
IF SUBSTR(w_newline,46,2) in ('02','03') THEN
w_ProcessLine := substr(w_newline,1,142);
dbms_output.put_line(w_ProcessLine);
Thanks.
|
|
|
|
|
Re: Oracle Special character while dbms_output [message #652453 is a reply to message #652449] |
Thu, 09 June 2016 06:11   |
Frank Naude
Messages: 4596 Registered: April 1998
|
Senior Member |
|
|
On Linux? Please post:
$ echo $NLS_LANG
and
SQL> SELECT parameter, value FROM v$nls_parameters WHERE parameter IN ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
Something like this should solve the problem:
$ NLS_LANG=AMERICAN_AMERICA.UTF8; export NLS_LANG
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Oracle Special character while dbms_output [message #652489 is a reply to message #652464] |
Fri, 10 June 2016 01:49   |
harshadsp
Messages: 100 Registered: August 2008
|
Senior Member |

|
|
I used CONVERT function to convert the string to UTF8 and it worked in PLSQL developer. Here is the code:-
set serveroutput on
Declare
w_input_dir VARCHAR2(100);
w_input_file VARCHAR2(50);
w_newline VARCHAR2(2000);
w_ProcessLine VARCHAR2(200);
w_lcnt NUMBER := 0;
w_strlgth NUMBER;
w_chrasci NUMBER;
erm VARCHAR2(50):= '';
input_file utl_file.file_type;
BEGIN
dbms_output.enable(null);
w_input_dir := 'WCPOLS_INPUT_DIR';--'CENGROUP_OUTPUT';
w_input_file := 'Invalid_ASCII_characters_out.txt';
input_file := utl_file.fopen(w_input_dir, w_input_file, 'r',32000);
IF utl_file.is_open(input_file) THEN
LOOP
BEGIN
utl_file.get_line(input_file, w_newline);
w_lcnt := w_lcnt + 1;
IF SUBSTR(w_newline,46,2) = '02' THEN
w_ProcessLine := convert(substr(w_newline,1,142),'WE8MSWIN1252','UTF8');
select length(w_ProcessLine) into w_strlgth from dual;
for i in 1..w_strlgth loop
if ascii(substr(w_ProcessLine,i,1)) between 128 and 255 then
dbms_output.put_line('Invalid Character '||chr(ascii(substr(w_ProcessLine,i,1)))||' at line: '||w_lcnt ||' and '||
'column pos:'||i);
end if;
end loop;
ELSIF SUBSTR(w_newline,46,2) = '03' THEN
w_ProcessLine := convert(substr(w_newline,1,110),'WE8MSWIN1252','UTF8');
select length(w_ProcessLine) into w_strlgth from dual;
for i in 1..w_strlgth loop
if ascii(substr(w_ProcessLine,i,1)) between 128 and 255 then
dbms_output.put_line('Invalid Character '||chr(ascii(substr(w_ProcessLine,i,1)))||' at line: '||w_lcnt ||' and '||
'column pos:'||i);
end if;
end loop;
END IF;
EXCEPTION WHEN no_data_found THEN
EXIT;
END;
END LOOP; --main loop
END IF; --InputFile is_open
utl_file.fclose(input_file);
END ;
/
The output is correct as-
Invalid Character à at line: 6 and column pos:67
Invalid Character © at line: 6 and column pos:68
Invalid Character ½ at line: 1485 and column pos:56
Now when I run the above code on Linux box by setting NLS_LANG variable to UT8 as
[hpatil@AUS-VX-APWX-TS1 sql]$ setenv NLS_LANG AMERICAN_AMERICA.UTF8
[hpatil@AUS-VX-APWX-TS1 sql]$ sqlplus
the output is
SQL> @Code1.sql
Invalid Character à at line: 6 and column pos:67
Invalid Character © at line: 6 and column pos:68
Invalid Character ½ at line: 1485 and column pos:56
now why the  is coming automatically with symbols © and ½. Any Idea what I am still missing? Please advise.
|
|
|
|
Goto Forum:
Current Time: Thu May 15 05:45:16 CDT 2025
|