1 2 Previous Next 23 Replies Latest reply on Jan 23, 2013 2:59 PM by 986193 Go to original post
      • 15. Re: ORA 01482: unsupported character set when joining on a view
        986193
        Thanks for your answers.
        I know you two are right, I wouldn't have chosen this character set neither but this is a pretty old database and changing it is not an option. I have to handle accented characters storage within this database. Other applications were developed with accented characters with the same database and they work fine.

        Anyway, I couldn't explain by what magic this is working, all I see is when I insert accented characters in the database and then I retrieve it, they have not been corrupted, they print properly. You can try it out with these inserts :

        INSERT INTO ALL_CITY VALUES ('D12','PARIS',23);

        INSERT INTO OWNER VALUES (1,'bélàïd');

        INSERT INTO CUSTOMER_FILE VALUES (1,'D12',1);

        What I want is to make case insensitive and accent insensitive researches.

        The SELECT * FROM owner WHERE name LIKE 'bél%'; query works properly, but SELECT * FROM owner WHERE name LIKE 'BEL%'; doesn't work.

        The SELECT UPPER(CONVERT(o.name, 'US7ASCII', 'WE8ISO8859P1')) FROM owner o; query returns the following result : BELAID which is the proper string I want to filter in my where condition.

        But SELECT * FROM CUSTOMER_FILE cf LEFT JOIN OWNER o ON cf.ID_OWNER= o.ID_OWNER WHERE UPPER(CONVERT(o.name, 'US7ASCII', 'WE8ISO8859P1')) LIKE UPPER('bel%'); is not working.

        Thanks for your time.
        • 16. Re: ORA 01482: unsupported character set when joining on a view
          chris227
          user10820282 wrote:
          The SELECT * FROM owner WHERE name LIKE 'bél%'; query works properly, but SELECT * FROM owner WHERE name LIKE 'BEL%'; doesn't work.
          That's not surprising, because of the case-sensitiv search.
          The SELECT UPPER(CONVERT(o.name, 'US7ASCII', 'WE8ISO8859P1')) FROM owner o; query returns the following result : BELAID which is the proper string I want to filter in my where condition.

          But SELECT * FROM CUSTOMER_FILE cf LEFT JOIN OWNER o ON cf.ID_OWNER= o.ID_OWNER WHERE UPPER(CONVERT(o.name, 'US7ASCII', 'WE8ISO8859P1')) LIKE UPPER('bel%'); is not working.
          That's might be because of the bug with convert, in the first one the convert function is only applied in the select.

          Please post the results of
          SELECT name, dump(name,1010) d FROM owner WHERE name LIKE 'bél%';
          
          desc owner
          • 17. Re: ORA 01482: unsupported character set when joining on a view
            986193
            Here is the result of :
            SELECT name, dump(name,1010) DUM FROM owner WHERE name LIKE 'bél%';
            DESC OWNER;
            b�l��d     Typ=1 Len=6 CharacterSet=US7ASCII: 98,233,108,224,239,100

            If this is really a convert bug, I am going to use the translate function in order to mimic the expected behaviour.
            • 18. Re: ORA 01482: unsupported character set when joining on a view
              chris227
              Output for DESC OWNER also, then we can start to figure out an accurate solution.
              • 19. Re: ORA 01482: unsupported character set when joining on a view
                986193
                DESC OWNER
                Nom NULL Type
                -------- -------- -------------
                ID_OWNER NOT NULL NUMBER(10)
                NAME NOT NULL VARCHAR2(255)
                • 20. Re: ORA 01482: unsupported character set when joining on a view
                  chris227
                  One simple thing you could try is
                  alter session set NLS_SORT = FRENCH_M_AI
                  With this you are able to use LIKE is the usal manner.

                  Another possibility
                  select
                  *
                  from owner
                  where
                  NLSSORT(name,'NLS_SORT=FRENCH_M_AI')
                  like
                  NLSSORT('BEL','NLS_SORT=FRENCH_M_AI')||'%'
                  • 21. Re: ORA 01482: unsupported character set when joining on a view
                    986193
                    Thanks Chris,

                    This solution make the research case insensitive :

                    select
                    *
                    from owner
                    where
                    NLSSORT(name,'NLS_SORT=FRENCH_M_AI')
                    like
                    NLSSORT('BÉL','NLS_SORT=FRENCH_M_AI')||'%'

                    is working but

                    select
                    *
                    from owner
                    where
                    NLSSORT(name,'NLS_SORT=FRENCH_M_AI')
                    like
                    NLSSORT('BEL','NLS_SORT=FRENCH_M_AI')||'%'

                    doesn't find any matching result.
                    • 22. Re: ORA 01482: unsupported character set when joining on a view
                      chris227
                      Regarding your dump and your posted nls settings you probaly have some data corruption issue for which convert seems one way to workaround.
                      I assume that the nls-settings posted first, with NLS_LANGUAGE=FRENCH, are your client settings, and the second settings are your DB settings.
                      So probably your client uses the wrong characterset for the ASCII7-database, perhaps something like ISO88591.
                      As oracle doesnt prevent this saving, there are two-byte characters in your DB.
                      I dont know how to handle this situation.
                      May be you should stay on the usage of the convert function.
                      You may try to use it in the select of a subquery and use this as an inline view in your query.
                      with o as{
                      select
                      SELECT
                       UPPER(CONVERT(name, 'US7ASCII', 'WE8ISO8859P1'))
                      -- ,rownum --uncommetd, if error till occurs, but care of performance
                       name
                      FROM owner 
                      }
                      
                      select
                      *
                      from o --join goes here
                      where
                      upper(name) like upper('Bel%')
                      Edited by: chris227 on 23.01.2013 06:05

                      Edited by: chris227 on 23.01.2013 06:08
                      • 23. Re: ORA 01482: unsupported character set when joining on a view
                        986193
                        with o as{
                        select
                        SELECT
                        UPPER(CONVERT(name, 'US7ASCII', 'WE8ISO8859P1'))
                        -- ,rownum --uncommetd, if error till occurs, but care of performance
                        name
                        FROM owner
                        }
                        select
                        *
                        from o --join goes here
                        where
                        upper(name) like upper('Bel%');
                        This query raises the same error 01482. 00000 - "unsupported character set"
                        I think it's a bug in the convert function.
                        I realize that our configuration is prety messed up, but it's the only workaround we found to store accented characters in this database. I hope it won't lead to too many unexpected behaviours.
                        For now I am going with an other workaround and hope that the performances will still be acceptable.
                        SELECT * FROM CUSTOMER_FILE cf LEFT JOIN OWNER o ON cf.ID_OWNER= o.ID_OWNER WHERE UPPER(TRANSLATE(NVL(o.name,'%'),'àâäãáåÀÁÂÃÄÅçÇéèêëÉÈÊËîïìíÌÍÎÏñÑôöðòóÒÓÔÕÖùúûüÙÚÛÜýÿÝ_-,'';.:?!/"',
                        'AAAAAAAAAAAACCEEEEEEEEIIIIIIIINNOOOOOOOOOOUUUUUUUUYYY ')) LIKE UPPER(TRANSLATE(NVL('béL%','%'),'àâäãáåÀÁÂÃÄÅçÇéèêëÉÈÊËîïìíÌÍÎÏñÑôöðòóÒÓÔÕÖùúûüÙÚÛÜýÿÝ_-,'';.:?!/"',
                        'AAAAAAAAAAAACCEEEEEEEEIIIIIIIINNOOOOOOOOOOUUUUUUUUYYY '));
                        Anyway, thanks a lot for your help, it helped me to figure out what was wrong.
                        1 2 Previous Next