This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Jul 12, 2012 1:51 AM by chris227 RSS

junk chars in data .

909162 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 .
    909162 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 .
    909162 Newbie
    Currently Being Moderated
    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 .
    909162 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points