1 2 Previous Next 29 Replies Latest reply: Jul 12, 2012 3:51 AM by chris227 RSS

    junk chars in data .

    user7955917
      How can i insert these character of this type in a table

      data
      t’s ‘low’ & & é

      we are using
      NLS_NCHAR_CHARACTERSET AL16UTF16
      NLS_CHARACTERSET US7ASCII
      database version 10g

      i tried storing it in a nvarchar column but not able to retain these chars . please let me know is it possible to retain these chars are not .
      we need to retain this data
        • 1. Re: junk chars in data .
          BluShadow
          How do you know it's not retaining the characters?

          It's possible that when you are querying the data the interface you are using does not support multi-byte character sets?

          What happens when you query the columns with the dump function?

          e.g.
          SQL> col dmp format a40
          SQL> select ename, dump(ename) dmp from emp;
          
          ENAME      DMP
          ---------- ----------------------------------------
          SMITH      Typ=1 Len=5: 83,77,73,84,72
          ALLEN      Typ=1 Len=5: 65,76,76,69,78
          WARD       Typ=1 Len=4: 87,65,82,68
          JONES      Typ=1 Len=5: 74,79,78,69,83
          MARTIN     Typ=1 Len=6: 77,65,82,84,73,78
          BLAKE      Typ=1 Len=5: 66,76,65,75,69
          CLARK      Typ=1 Len=5: 67,76,65,82,75
          SCOTT      Typ=1 Len=5: 83,67,79,84,84
          KING       Typ=1 Len=4: 75,73,78,71
          TURNER     Typ=1 Len=6: 84,85,82,78,69,82
          ADAMS      Typ=1 Len=5: 65,68,65,77,83
          JAMES      Typ=1 Len=5: 74,65,77,69,83
          FORD       Typ=1 Len=4: 70,79,82,68
          MILLER     Typ=1 Len=6: 77,73,76,76,69,82
          
          14 rows selected.
          Does that show it having stored multi byte characters on your database?
          • 2. Re: junk chars in data .
            BluShadow
            user7955917 wrote:
            NLS_CHARACTERSET US7ASCII
            That could be a problem as your standard character set is only 7 bit ASCII.
            • 3. Re: junk chars in data .
              user7955917
              SQL> create table junk_char
              (
              txt1 nvarchar2(2000),
              txt2 varchar2(2000),
              );

              2 3 4 5 )
              *
              ERROR at line 5:
              ORA-00904: : invalid identifier


              SQL> SQL> create table junk_char
              (
              txt1 nvarchar2(2000),
              txt2 varchar2(2000)
              );
              2 3 4 5
              Table created.


              SQL> insert into junk_char (txt1,txt2) values ('t’s ‘low’ & & é',2);

              1 row created.

              SQL> SELECT txt1 FROM junk_char WHERE txt2=2;

              TXT1
              --------------------------------------------------------------------------------
              t?s ???low??? & & ?

              SQL> SELECT DUMP(txt1) FROM junk_char WHERE txt2=2;

              DUMP(TXT1)
              --------------------------------------------------------------------------------
              Typ=1 Len=38: 0,116,255,253,0,115,0,32,255,253,255,253,255,253,0,108,0,111,0,119
              ,255,253,255,253,255,253,0,32,0,38,0,32,0,38,0,32,255,253

              Edited by: user7955917 on Jul 10, 2012 4:26 AM
              • 4. Re: junk chars in data .
                BluShadow
                user7955917 wrote:
                create table junk_char
                (
                txt1 nvarchar2(2000),
                txt2 varchar2(2000),
                );

                insert into junk_char (txt1,txt2) values ('t’s ‘low’ & & é',2);

                SELECT txt1 FROM junk_char WHERE txt2=2;

                txt1
                --------------------------------------------------------------------------------
                t?s ???low??? & & ?

                SQL> SELECT DUMP(txt1) FROM junk_char WHERE txt2=2;

                DUMP(txt1)
                --------------------------------------------------------------------------------
                Typ=1 Len=38: 0,116,255,253,0,115,0,32,255,253,255,253,255,253,0,108,0,111,0,119
                ,255,253,255,253,255,253,0,32,0,38,0,32,0,38,0,32,255,253

                Edited by: user7955917 on Jul 10, 2012 3:53 AM
                Well, that's certainly storing multi-byte characters (38 bytes are being used to store 19 characters), so the fact you're not seeing it when you select the data is due to the interface, which I'm guessing is SQL*Plus in the above case. The data's there, you just need a suitably compatible interface to view it through.
                • 5. Re: junk chars in data .
                  chris227
                  I doubt on it.
                  The example given seems just to be a fake. The create doesnt work due to a comma at the end and the select goes (was going, it was corrected meanwhile) onto another table.
                  Furthermore there is always 255,253 for the critical characters. That may be a hint, that something was already wrong with the insert. The only thing being correct seems to be that the characters are stored in two bytes.
                  Correct dump should be somethin like:
                  Typ=1 Len=38: 0,116,32,25,0,115,0,32,0,226,32,172,2,220,0,108,0,111,0,119,0,226,32,172,33,34,0,32,0,38,0,32,0,38,0,32,0,233
                  Edited by: chris227 on 10.07.2012 04:15

                  Edited by: chris227 on 10.07.2012 04:16
                  • 6. Re: junk chars in data .
                    user7955917
                    when i examine the dump values 253,255,253,255,253 repeatdly . that means all these junk characters are stored of same character instead of different junk characters
                    • 7. Re: junk chars in data .
                      user7955917
                      i have executed the script just now and pasted the out put . can you please let me know what character set u have at ur db level ;
                      select * from NLS_DATABASE_PARAMETERS;
                      • 8. Re: junk chars in data .
                        chris227
                        select * from NLS_DATABASE_PARAMETERS
                        where
                        parameter like '%CHAR%'
                        
                        PARAMETER VALUE 
                        NLS_NUMERIC_CHARACTERS ., 
                        NLS_CHARACTERSET AL32UTF8 
                        NLS_NCHAR_CHARACTERSET AL16UTF16 
                        NLS_NCHAR_CONV_EXCP FALSE 
                        So my cleint AND my database support those characters which is not the case in your environment.

                        Read in the doc
                        http://docs.oracle.com/cd/E14072_01/server.112/e10729/ch7progrunicode.htm#i1006019
                        the chapter "NCHAR String Literal Replacement"
                        to overcome this issue.

                        regards
                        • 9. Re: junk chars in data .
                          BluShadow
                          chris227 wrote:
                          I doubt on it.
                          The example given seems just to be a fake. The create doesnt work due to a comma at the end and the select goes (was going, it was corrected meanwhile) onto another table.
                          Furthermore there is always 255,253 for the critical characters. That may be a hint, that something was already wrong with the insert. The only thing being correct seems to be that the characters are stored in two bytes.
                          Correct dump should be somethin like:
                          Typ=1 Len=38: 0,116,32,25,0,115,0,32,0,226,32,172,2,220,0,108,0,111,0,119,0,226,32,172,33,34,0,32,0,38,0,32,0,38,0,32,0,233
                          I don't have a multi byte character database to test on, so was just examining by eye unfortunately.
                          • 10. Re: junk chars in data .
                            BluShadow
                            user7955917 wrote:
                            when i examine the dump values 253,255,253,255,253 repeatdly . that means all these junk characters are stored of same character instead of different junk characters
                            You should perhaps be referring to them as international characters rather than "junk". Not sure the people who use those characters in their language would appreciate their character set being called "junk".
                            • 11. Re: junk chars in data .
                              Paul M.
                              what character set u have at ur db level ;
                              I don't think that's the problem, I think the problem is in your interface (as BluShadow said). See this example (Windows) :
                              SQL> select * from nls_database_parameters
                                2  where parameter like '%CHARACTERSET';
                              
                              PARAMETER                      VALUE
                              ------------------------------ ----------------------------------------
                              NLS_CHARACTERSET               WE8MSWIN1252
                              NLS_NCHAR_CHARACTERSET         AL16UTF16
                              
                              SQL> desc tt1
                               Name                                      Null?    Type
                               ----------------------------------------- -------- ----------------------------
                               A                                                  NVARCHAR2(50)
                              
                              SQL> select * from tt1;
                              
                              A
                              --------------------------------------------------
                              tÆs ÔÇÿlowÔÇÖ & & Ú
                              
                              SQL> $chcp 1252
                              Tabella codici attiva: 1252
                              
                              SQL> /
                              
                              A
                              --------------------------------------------------
                              t’s ‘low’ & & é
                              
                              SQL>
                              • 12. Re: junk chars in data .
                                chris227
                                Hm, show us your dump please ...
                                It's about inserting not selecting i guess.
                                Sure this depends on the client too, but this seems not to be the only issue, also nls_characterset in DB (in your case WE8MSWIN1252 which knows to encode those characters, but in OPs case it is ascii) because the encoding of the statement itself (or did i misunderstand something totally?).
                                However there is a specific forum for globalization support ;-)
                                • 13. Re: junk chars in data .
                                  BluShadow
                                  Yeah, I think it's a whole case of wrong character set for inserting the data, and then wrong character set in the interface for displaying data... i.e. a whole mish-mash of character sets that are not compatible to meet the needs of the data.
                                  • 14. Re: junk chars in data .
                                    Paul M.
                                    Hm, show us your dump please ...
                                    SQL> select dump(a) from tt1;
                                    
                                    DUMP(A)
                                    --------------------------------------------------------------------------------
                                    Typ=1 Len=38: 0,116,32,25,0,115,0,32,0,226,32,172,2,220,0,108,0,111,0,119,0,226,
                                    32,172,33,34,0,32,0,38,0,32,0,38,0,32,0,233
                                    
                                    
                                    SQL>
                                    1 2 Previous Next