This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Feb 18, 2013 9:15 PM by SenthilkumarS Go to original post RSS
  • 15. Re: CSV file <Â>
    ranit B Expert
    Currently Being Moderated
    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 <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 <Â>
    SenthilkumarS Newbie
    Currently Being Moderated
    Thanks
1 2 Previous Next

Legend

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