1 2 Previous Next 26 Replies Latest reply: Feb 18, 2013 11:15 PM by Senthilkumar S Go to original post RSS
      • 15. Re: CSV file <Â>
        ranit B
        925896 wrote:
        When I try to add as first row
        l_contents:=CHR(0xFEFF) || CHR(10);
        here l_contents is clob

        am getting below error


        ORA-06550: line 138, column 18:
        PLS-00103: Encountered the symbol "XFEFF" when expecting one of the following:

        ) , * & = - + < / > at in is mod remainder not rem =>
        <an exponent (**)> <> or != or ~= >= <= <> and or like like2
        like4 likec as between from using || multiset member
        submultiset
        The symbol "," was substituted for "XFEFF" to continue.
        ORA-06550: line 143, column 32:
        PLS-00103: Encountered the symbol "WITH" when expecting one of the following:

        ( select
        1] I guess you are trying to do something else also... what is this Multiset error? What exactly are you trying to do before/after this?

        2] From where is this WITH error coming?

        It's getting v difficult to suggest w/o actually seeing your code.
        • 16. Re: CSV file <Â>
          Senthilkumar S
          Hi Ranit,

          Am adding each line into this l_contents after this.


          Sample as below
          l_contents:=CHR(0xFEFF) || CHR(10);
          
          l_contents:=l_contents||Headers || CHR(10);
          
          For I in <Cursor?
          Loop
          l_contents:=l_contents|| < each row from cursor> ||CHR(10);
          End loop;
          
           dbms_xslprocessor.clob2filE(l_contents,<directory>,<filename.csv>);
          • 17. Re: CSV file <Â>
            ascheffer
            Use for a UTF-16 BOM
            l_contents:=utl_raw.cast_to_varchar2(hextoraw('FEFF')); 
            l_contents:=l_contents||Headers || CHR(10);
            ....
            Or for a UTF-8 BOM
            l_contents:=utl_raw.cast_to_varchar2(hextoraw('EFBBBF')); 
            l_contents:=l_contents||Headers || CHR(10);
            ....
            • 18. Re: CSV file <Â>
              chris227
              Dont confuse yourself.
              Go a step back and reread odie first answer.
              Most likely it is a matter on how you open the file to read.
              It is written in some characterset. Without knowing that characterset excel might guess that it is some corresponding to your client machine settings. This may be wrong, thats why you see some odd characters displayed.
              Try to open the csv not per double mouse click but per excel text import feature where you can choose the origin characterset of the file to open.
              So in short, find out the encoding of the file written and open it properly.
              • 19. Re: CSV file <Â>
                stefan nebesnak
                925896 wrote:
                When I try to add as first row
                l_contents:=CHR(0xFEFF) || CHR(10);
                am getting below error

                ORA-06550: line 138, column 18:
                PLS-00103: Encountered the symbol "XFEFF" when expecting one of the following:
                This is PERL syntax:
                CHR(0x hex hex).
                You can't use SQL CHR function in this way..
                SQL> select CHR(0xFEFF) from dual;
                 
                select CHR(0xFEFF) from dual
                 
                "ORA-00907":     missing right parenthesis
                But you can use this:

                (To convert from hexadecimal to decimal)
                SQL> select TO_NUMBER('FEFF', 'XXXX') from dual;
                 
                TO_NUMBER('FEFF','XXXX')
                ------------------------
                                   65279
                
                SQL> select CHR(TO_NUMBER('FEFF', 'XXXX')) from dual;
                 
                CHR(TO_NUMBER('FEFF','XXXX'))
                -----------------------------
                ˙
                (And back)
                SQL> select UPPER(TO_CHAR(65279, 'XXXX')) from dual;
                 
                UPPER(TO_CHAR(65279,'XXXX'))
                ----------------------------
                 FEFF
                • 20. Re: CSV file <Â>
                  Senthilkumar S
                  Hi Chris,

                  Am having 3 scenarios here
                  Currently my system excel encoding has changed to UTF-8.

                  SC1:
                  When i use EE8ISO8859P2 for writing file from database, Am getting degree symbol and for some places instead of degree '?' is coming
                  SC2:
                  When i use AL16UTF16 for writing file from database, Am getting degree symbol and for some places instead of degree ,degree superscript on the top of '-' is coming
                  SC3:
                  When i use AL32UTF8for writing file from database, Am getting degree symbol and for some places instead of degree '' is coming °


                  Please let me know how to get '°' without issue.

                  PS: Am using dbms_xslprocessor.clob2filE to create CSV file from database, Here am sending charcterset Id to write CSV file
                  • 21. Re: CSV file <Â>
                    ascheffer
                    How do you set the characterset for writing file from database?
                    I mean, if your database is AL32UTF8 that means that your clob is in AL32UTF8.
                    And if you use dbms_xslprocessor.clob2file for writing the clob to file the file is also in AL32UTF8.
                    So how do you write your file using EE8ISO8859P2?
                    Did your tried adding the ATF-8 BOM
                    l_contents:=utl_raw.cast_to_varchar2(hextoraw('EFBBBF'));
                    • 22. Re: CSV file <Â>
                      chris227
                      925896 wrote:
                      Hi Chris,

                      Am having 3 scenarios here
                      Currently my system excel encoding has changed to UTF-8.
                      Personally i would try to write out the file as UTF-8 so here AL32UTF8 would be my choice, therefor no needto set any character id in CLOB2FILE.

                      >
                      SC1:
                      When i use EE8ISO8859P2 for writing file from database, Am getting degree symbol and for some places instead of degree '?' is coming
                      SC2:
                      When i use AL16UTF16 for writing file from database, Am getting degree symbol and for some places instead of degree ,degree superscript on the top of '-' is coming
                      SC3:
                      When i use AL32UTF8for writing file from database, Am getting degree symbol and for some places instead of degree '' is coming °
                      First question comes into my mind is, why are there different representations of characters that should to be the same?
                      I would isolate the issue by examining only to rows containing the source of the correct and the incorrect representation only.
                      For this i would first look at the result of
                      select
                       your_degree_column_correct
                      ,dump(your_degree_column_correct,1010) dc
                      ,your_degree_column_incorrect
                      ,dump(your_degree_column_incorrect,1010) di
                      from your_table
                      • 23. Re: CSV file <Â>
                        Senthilkumar S
                        Hi Chris,
                        STOR_TEMP                       DC
                        32º to 104ºF  (0º to 40ºC)     Typ=1 Len=30 CharacterSet=AL32UTF8: 51,50,194,186,32,116,111,32,49,48,52,194,186,70,32,32,40,48,194,186,32,116,111,32,52,48,194,186,67,41
                        OP_TEMP                           DI
                        32º to 104ºF  (0º to 40ºC)     Typ=1 Len=30 CharacterSet=AL32UTF8: 51,50,194,186,32,116,111,32,49,48,52,194,186,70,32,32,40,48,194,186,32,116,111,32,52,48,194,186,67,41
                        • 24. Re: CSV file <Â>
                          chris227
                          194,186 is not degree, but masculine ordinal indicator, 194, 176 would be degree.
                          The dumps are pretty same. Does the csv-output for them really differs when just these two values are written out? Cant really believe it.
                          Im my opinion this should result wether in displaying &ordm; or in &#194;&ordm;.
                          In the latter case the sole problem is, that your client-programm (e.g. excel) doesnt recognize that the file is in a mulit byte characterset.
                          Did you try already the option given by schaeffer and others? (although i dont thinnk that this is the reason)
                          • 25. Re: CSV file <Â>
                            odie_63
                            Let's get this straight.

                            For starters, as Chris pointed out, you probably have a mix of &ordm; (&amp;ordm;) and &deg; (&amp;deg;) characters in the first place.
                            Look at them closely, they're not the same.
                            Some fonts render them differently, for instance Calibri (Excel default) render the (&amp;ordm;) character with a little underbar whereas Arial does not, hence it can be easily confused with the degree symbol.

                            Now, about the encoding issue, I've run the following scenario on my db, please tell us if it works for you or not :
                            SQL> create table test_degree (txt varchar2(30));
                             
                            Table created
                             
                            SQL> insert into test_degree values ('32° to 104°F');
                             
                            1 row inserted
                             
                            SQL> select dump(txt,1016) from test_degree;
                             
                            DUMP(TXT,1016)
                            --------------------------------------------------------------------------------
                            Typ=1 Len=14 CharacterSet=AL32UTF8: 33,32,c2,b0,20,74,6f,20,31,30,34,c2,b0,46
                             
                            DB charset is AL32UTF8, like you, and I've inserted a string containing degree symbols which are encoded on two bytes (C2B0).

                            Let's try dumping the string in a file :
                            SQL> declare
                              2    v_txt clob;
                              3  begin
                              4    select to_clob(txt) into v_txt from test_degree;
                              5    dbms_xslprocessor.clob2file( v_txt, 'TEST_DIR', 'test.csv');
                              6  end;
                              7  /
                             
                            PL/SQL procedure successfully completed
                             
                            (By default CLOB2FILE uses the DB charset)

                            Now, if I open the file with a text editor that doesn't support multibyte encoding, I see this, as expected :
                            32° to 104°F
                            - If I try to open it in Excel directly (double-click), I see the same, because Excel expects the file to be encoded with the OS codepage (mine is CP1252).
                            - If I open Excel, launch the text importer utility and choose the real encoding of the file in the list - on my Excel 2007 it's [65001 : Unicode (UTF-8)] - then the data appears correctly.


                            In order to have Excel directly recognize that the file uses UTF-8 encoding, let's add a BOM at the beginning of the file :
                            SQL> declare
                              2    v_txt clob;
                              3  begin
                              4    select to_clob(txt) into v_txt from test_degree;
                              5    dbms_xslprocessor.clob2file( utl_raw.cast_to_varchar2(hextoraw('EFBBBF')) || v_txt, 'TEST_DIR', 'test.csv');
                              6  end;
                              7  /
                             
                            PL/SQL procedure successfully completed
                             
                            Now, when I double-click on the file, Excel opens and I see the correct representation of the characters.
                            • 26. Re: CSV file <Â>
                              Senthilkumar S
                              Thanks
                              1 2 Previous Next