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 Go to next message
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 #652451 is a reply to message #652449] Thu, 09 June 2016 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The problem is in your client configuration.
You tell Oracle you are using UTF8 character set when you are not.

Re: Oracle Special character while dbms_output [message #652452 is a reply to message #652451] Thu, 09 June 2016 06:10 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Thanks for fast response Michel but I didn't understand what and how Oracle is using UTF8? Could you please give some more pointers on this? I am running this code against PL/SQL developer and also when I run code on Unix its showing '?' for those special characters.

Please advise.
Re: Oracle Special character while dbms_output [message #652453 is a reply to message #652449] Thu, 09 June 2016 06:11 Go to previous messageGo to next message
Frank Naude
Messages: 4569
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 #652454 is a reply to message #652453] Thu, 09 June 2016 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

and
select distinct client_charset from v$session_connect_info where sid=sys_context('userenv','sid');
Re: Oracle Special character while dbms_output [message #652455 is a reply to message #652453] Thu, 09 June 2016 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Something like this should solve the problem:
$ NLS_LANG=AMERICAN_AMERICA.UTF8; export NLS_LANG


I think this is what he has done but his terminal is not in UTF8 Mode.

Re: Oracle Special character while dbms_output [message #652456 is a reply to message #652455] Thu, 09 June 2016 06:20 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Here it is-
SELECT parameter, value FROM v$nls_parameters WHERE parameter IN ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
PARAMETER VALUE
1 NLS_LANGUAGE AMERICAN
2 NLS_TERRITORY AMERICA
3 NLS_CHARACTERSET WE8MSWIN1252

select distinct client_charset from v$session_connect_info where sid=sys_context('userenv','sid');
CLIENT_CHARSET
1 Unknown

Also I coudnot find $NLS_LANG on my Linus environment variables.

Please advise
Re: Oracle Special character while dbms_output [message #652457 is a reply to message #652456] Thu, 09 June 2016 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Also I coudnot find $NLS_LANG on my Linus environment variables.


This is one of your problem, you MUST set it and set it to what your terminal is set.

Quote:
NLS_CHARACTERSET WE8MSWIN1252


Your database does not support UTF8 you have to read your file in binary mode and not character one.
That is use GET_RAW and not GET_LINE, or use GET_LINE_NCHAR if your national character set is UTF8.
DBMS_OUTPUT works only with your database character set, that is WE8MSWIN1252 in your case.


Re: Oracle Special character while dbms_output [message #652458 is a reply to message #652456] Thu, 09 June 2016 06:30 Go to previous messageGo to next message
Frank Naude
Messages: 4569
Registered: April 1998
Senior Member
Then set it:
$ NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252; export NLS_LANG
Re: Oracle Special character while dbms_output [message #652459 is a reply to message #652458] Thu, 09 June 2016 06:36 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

I did like this on Linux-
[hpatil@AUS-VX-APWX-TS1 output]$ setenv NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
[hpatil@AUS-VX-APWX-TS1 output]$ echo $NLS_LANG
AMERICAN_AMERICA.WE8MSWIN1252

Now when I execute the program, its still adding 2 more special characters at column 68 and 69. Actualy there are just 2 like à at 67 and © at 68 ..not sure why its adding something at 68 and 69.
6- Ã-67 -68 Â-69 ©-70
Re: Oracle Special character while dbms_output [message #652460 is a reply to message #652459] Thu, 09 June 2016 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do what I said.

Re: Oracle Special character while dbms_output [message #652461 is a reply to message #652460] Thu, 09 June 2016 06:45 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

yes, I am trying to user get_raw instead of get_line but its giving error ORA-06502: character string buffer too small.
Re: Oracle Special character while dbms_output [message #652462 is a reply to message #652461] Thu, 09 June 2016 07:07 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

but is it possible to read file each line by line when we read the file in binary mode?
Re: Oracle Special character while dbms_output [message #652463 is a reply to message #652462] Thu, 09 June 2016 07:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
harshadsp wrote on Thu, 09 June 2016 05:07
but is it possible to read file each line by line when we read the file in binary mode?

What defines end-of-line in binary mode?
Re: Oracle Special character while dbms_output [message #652464 is a reply to message #652463] Thu, 09 June 2016 07:23 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

yes which is not possible. I think I need to explore more about this get_raw.
Re: Oracle Special character while dbms_output [message #652489 is a reply to message #652464] Fri, 10 June 2016 01:49 Go to previous messageGo to next message
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.
Re: Oracle Special character while dbms_output [message #652490 is a reply to message #652489] Fri, 10 June 2016 03:29 Go to previous message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

okay, I set the NLS_LANG on Linux as AMERICAN_AMERICA.WE8MSWIN1252 (same as plsql developer) and it worked. Thank you all for giving your valuables inputs.
Previous Topic: Rebuild table contain partition
Next Topic: RAISE_APPLICATION_ERROR to display multiple line
Goto Forum:
  


Current Time: Fri Jul 30 00:16:23 CDT 2021