1 2 Previous Next 29 Replies Latest reply: Jul 12, 2012 3:51 AM by chris227 Go to original post RSS
      • 15. Re: junk chars in data .
        user7955917
        can you please help me out how to set character set . i am using sunos and sql*plus client;

        export LC_ALL=en_US.UTF-8
        export LANG=en_US
        export NLS_LANG=AMERICAN_AMERICA.UTF8


        now i am getting this error

        SQL> insert into junk_char (txt1,txt2) values ('ts ‘low’ & & é',2);
        commit;ERROR:
        ORA-01756: quoted string not properly terminated
        • 16. Re: junk chars in data .
          Sergiusz Wolicki-Oracle
          First, what is the database character set?
          SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET'
          Second, it is not enough to set LC_ALL=.UTF-8 and NLS_LANG=.AL32UTF8. Your terminal must also be configured for UTF-8. What terminal do you use?


          -- Sergiusz
          • 17. Re: junk chars in data .
            user7955917
            SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET'
            US7ASCII
            connecting from fsecure - vt100
            i am using SunOS and connecting from sqlplus

            Edited by: user7955917 on Jul 10, 2012 8:32 AM

            Edited by: user7955917 on Jul 10, 2012 8:34 AM
            • 18. Re: junk chars in data .
              Sergiusz Wolicki-Oracle
              US7ASCII will not support all the characters you want to store, but this should not cause the error you get.

              Also, I have not asked about the O/S nor the application but about the terminal (or terminal emulator).


              -- Sergiusz
              • 19. Re: junk chars in data .
                user7955917
                SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET'
                US7ASCII
                connecting from fsecure - vt100
                i am using SunOS and connecting from sqlplus
                • 20. Re: junk chars in data .
                  user7955917
                  i have connected from windows command prompt and i have set the enivornment variable to NLS_LANG=.WE8MSWIN1252
                  and tried executing the same script but still i am facing the same problem .

                  SQL> insert into junk_char (txt1,txt2) values ('t’s ‘low’ & & é',2);
                  ERROR:
                  ORA-01756: quoted string not properly terminated


                  SQL>insert into junk_char (txt1,txt2) values ('ts ‘low’ & & é',2);
                  1 row created.

                  SQL> select dump(txt1) from junk_char;

                  Typ=1 Len=36: 0,116,0,115,0,32,0,63,0,63,0,126,0,108,0,111,0,119,0,63,0,63,0,84,

                  0,32,0,38,0,32,0,38,0,32,0,63

                  select txt1 from junk_char;

                  ts ??~low??T & & ?

                  SQL> desc junk_char
                  Name Null? Type
                  ----------------------------------------- -------- ----------------------

                  TXT1 NVARCHAR2(2000)
                  TXT2 VARCHAR2(2000)

                  paul , can you please let me know where i am wrong .

                  Edited by: user7955917 on Jul 10, 2012 9:15 AM
                  • 21. Re: junk chars in data .
                    Sergiusz Wolicki-Oracle
                    (I have not noticed this thread is a bit longer than the last question ;-))

                    Let' try with Command Prompt. Try this:
                    C:\>set ORA_NCHAR_LITERAL_REPLACE=TRUE
                    C:\>chcp 850
                    C:\>set NLS_LANG=.WE8PC850
                    C:\>sqlplus ...
                    SQL>insert into junk_char (txt1,txt2) values (n't’s ‘low’ & & é',2);
                    Note the N before the literal. You may need to configure Command Prompt window to use a TrueType font instead of the raster font (System menu->Properties).


                    -- Sergiusz
                    • 22. Re: junk chars in data .
                      Sergiusz Wolicki-Oracle
                      By the way, both client and server need to be 10.2 or newer.


                      -- Sergiusz
                      • 23. Re: junk chars in data .
                        user7955917
                        executing from command propt . still not able to see the inserted data while retrieving
                        Microsoft Windows XP [Version 5.1.2600]
                        H:\>set ORA_NCHAR_LITERAL_REPLACE=TRUE
                        H:\>chcp 850
                        Active code page: 850
                        H:\>set NLS_LANG=.WE8PC850
                        H:\>sqlplus ......
                        Connected to:
                        Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit....

                        SQL> desc junk_char
                        Name Null? Type
                        ----------------------------------------- -------- ----------------------------
                        TXT1 NVARCHAR2(2000)
                        TXT2 VARCHAR2(2000)

                        SQL> delete from junk_char;
                        0 rows deleted.
                        SQL> commit;
                        Commit complete.

                        SQL> set define off;
                        SQL> insert into junk_char (txt1,txt2) values ('t’s ‘low’ & & é',2);
                        ERROR:ORA-24450: Cannot pre-process OCI statement

                        SQL> insert into junk_char (txt1,txt2) values ('t’s ‘low’ & & é',2);
                        ERROR:ORA-24450: Cannot pre-process OCI statement

                        SQL> insert into junk_char (txt1,txt2) values ('1',2);
                        1 row created.

                        SQL> insert into junk_char (txt1,txt2) values ('low’ & & é',2);
                        1 row created.

                        SQL> select * from junk_char;
                        TXT1
                        --------------------------------------------------------------------------------
                        TXT2
                        --------------------------------------------------------------------------------
                        1
                        2

                        lowa?T & & e
                        2

                        SQL> $set NLS_LANG=.WE8MSWIN1252
                        SQL> $chcp 1252
                        Active code page: 1252

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

                        SQL> select * from junk_char;
                        TXT1
                        --------------------------------------------------------------------------------
                        TXT2
                        --------------------------------------------------------------------------------
                        1
                        2

                        lowa?T & & e
                        2

                        t?s OCylowOCO & & U
                        2


                        SQL> select dump(txt1) from junk_char;

                        DUMP(TXT1)
                        --------------------------------------------------------------------------------
                        Typ=1 Len=2: 0,49
                        Typ=1 Len=24: 0,108,0,111,0,119,0,97,0,63,0,84,0,32,0,38,0,32,0,38,0,32,0,101
                        Typ=1 Len=38: 0,116,0,63,0,115,0,32,0,79,0,67,0,121,0,108,0,111,0,119,0,79,0,67,
                        0,79,0,32,0,38,0,32,0,38,0,32,0,85


                        SQL> SELECT * from NLS_DATABASE_PARAMETERS where PARAMETER like 'NLS_%CHAR%'
                        2 ;

                        PARAMETER VALUE
                        ------------------------------ ----------------------------------------
                        NLS_NCHAR_CHARACTERSET AL16UTF16
                        NLS_NUMERIC_CHARACTERS .,
                        NLS_CHARACTERSET US7ASCII
                        NLS_NCHAR_CONV_EXCP FALSE

                        Edited by: user7955917 on Jul 11, 2012 1:35 AM
                        • 24. Re: junk chars in data .
                          orafad
                          It seems you did not follow Sergiusz instructions exactly; note where it says:
                          "SQL>insert into junk_char (txt1,txt2) values (n't’s ‘low’ & & é',2);


                          Note the N before the literal. "
                          • 25. Re: junk chars in data .
                            user7955917
                            thank you , Sergiusz and oraford . though you given clear instructions i have missed out it ...(;-

                            hi used the following changes to environment variables to make it load .

                            H:\>set ORA_NCHAR_LITERAL_REPLACE=TRUE
                            H:\>set NLS_LANG=.WE8MSWIN1252
                            H:\>chcp 1252
                            Active code page: 1252

                            the data inserted successfully
                            • 26. Re: junk chars in data .
                              user7955917
                              Thank you orafad , i have readit but i used my old insert stmt (:- . Thank to all of you .

                              H:\>set ORA_NCHAR_LITERAL_REPLACE=TRUE
                              H:\>set NLS_LANG=.WE8MSWIN1252
                              H:\>chcp 1252
                              Active code page: 1252

                              i have made the following changes to make the code to work out
                              • 27. Re: junk chars in data .
                                chris227
                                I thought it was easy for you just reading the solution in the docs i pointed you to http://docs.oracle.com/cd/E14072_01/server.112/e10729/ch7progrunicode.htm#i1006019
                                the chapter "NCHAR String Literal Replacement"
                                where exactly the usage of the environment parameter and of 'N' is described.
                                I did explain the problem and gave the solution but you liked more to follow the ace, disappointing.
                                • 28. Re: junk chars in data .
                                  user7955917
                                  sorry chris . i have overlooked the link you have provided . But it is definetly it's a help full link to set the enviromnet from java . Actually i have very limited time to fix the fix .
                                  But i should say thanks to all those who replied to this thread spending their valuable time . i wish i should also excel my knowledge like you ppl .

                                  Edited by: user7955917 on Jul 11, 2012 1:11 PM
                                  • 29. Re: junk chars in data .
                                    chris227
                                    user7955917 wrote:
                                    sorry chris . i have overlooked the link you have provided . But it is definetly it's a help full link to set the enviromnet from java . Actually i have very limited time
                                    It is not about java only, and you have not to fix anything you did it allright.
                                    Just to recapitulate:

                                    "Being part of a SQL or PL/SQL statement, the text of any literal, with or without the prefix N, is encoded in the same character set as the rest of the statement. On the client side, the statement is in the client character set, which is determined by the client character set defined in NLS_LANG, or specified in the OCIEnvNlsCreate() call, or predefined as UTF-16 in JDBC. On the server side, the statement is in the database character set."

                                    The last sentence was the most important in your context. The statement and with this the literal was converted to ascii7, which couldnt work obviously.

                                    "Because many applications, for example, SQL*Plus, use OCI to connect to a database, and they do not control NCHAR literal replacement explicitly, you can set the client environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE to control the functionality for them."

                                    SQL and probably the sofware you use are using OCI under the hood. So this setting ensures to bypass the conversion of n-char-parts of the statement. The n-char parts are dedicated with the n-prefix.

                                    The display issue mentioned by paul and blue plays a role afterwards.

                                    Regards
                                    1 2 Previous Next