1 2 Previous Next 23 Replies Latest reply: Jan 23, 2013 8:59 AM by 986193 RSS

    ORA 01482: unsupported character set when joining on a view

    986193
      Hi guys,

      I watched many threads but I was not able to find an answer to my issue, so I am creating a new one.

      I need the convert function to filter a table on a column which contains accented characters. I am using Oracle 10G and my database charset is US7ASCII

      This query works fine.
      SELECT * FROM User u WHERE UPPER(CONVERT(u.name, 'US7ASCII', 'WE8ISO8859P1')) LIKE UPPER('name%');

      But I also need to join this table with a view to get some informations on the user. When I use the convert function on this view, everything is ok :
      The object 'code' is my view.
      SELECT c.code from city c where convert(c.code, 'US7ASCII', 'WE8ISO8859P1') LIKE 'code';

      However, when I try to join these 2 objects, I get an ORA 01482 error : unsupported character set.
      SELECT * FROM user u LEFT JOIN city c ON u.code_city = c.code WHERE UPPER(CONVERT(u.name, 'US7ASCII', 'WE8ISO8859P1'))) LIKE UPPER('name%');

      I tried to figure out the solution but I really don't understand why this query doesn't work whereas the convert function works fine on the 2 objects.

      Thank you for your help
        • 1. Re: ORA 01482: unsupported character set when joining on a view
          BluShadow
          Can you provide us with some example tables and data and your full database version, so members can attempt to reproduce the issue.

          Read: {message:id=9360002}
          user10820282 wrote:
          I need the convert function to filter a table on a column which contains accented characters. I am using Oracle 10G and my database charset is US7ASCII
          Not sure how you are managing to store accented characters in a database that uses US7ASCII (7 bit ascii) character set. There's no extended ascii characters in that character set, so you cannot store them.
          • 2. Re: ORA 01482: unsupported character set when joining on a view
            971895
            In your code extra on bracket is there.Pls check below one is correct...
            WHERE UPPER(CONVERT(u.name, 'US7ASCII', 'WE8ISO8859P1'))
            • 3. Re: ORA 01482: unsupported character set when joining on a view
              Nitesh.
              SELECT * FROM user u LEFT JOIN city c ON u.code_city = c.code WHERE UPPER(CONVERT(u.name, 'US7ASCII', 'WE8ISO8859P1'))) LIKE UPPER('name%');

              In above case an extra closing paranthesis is been added before LIKE operator so try again by removing it and let us know what's error been shown over there ..
              • 4. Re: ORA 01482: unsupported character set when joining on a view
                971895
                Can you post the error?
                • 5. Re: ORA 01482: unsupported character set when joining on a view
                  986193
                  Sorry about the extra closing parenthesis, I made a short version of the real query in order to focus on the real issue and I missed it. Anyway, the real query is well formed.

                  I reduced the database to the minimum with one table and one view and I am still able to reproduce the error. I cannot provide you the full version of the database, but here is the real query with 2 tables and 1 view.

                  SELECT * FROM CUSTOMER_FILE cf LEFT JOIN OWNER o ON cf.ID_OWNER= o.ID_OWNER LEFT JOIN CF_CITY c ON cf.CODE_CITY= c.CODE_CITY WHERE UPPER(CONVERT(o.name, 'US7ASCII', 'WE8ISO8859P1')) LIKE UPPER('name%');

                  The 3 tables and the view :

                  CREATE TABLE "OWNER"
                  (     "ID_OWNER" NUMBER(10,0) NOT NULL ENABLE,
                       "NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE,
                       PRIMARY KEY ("ID_OWNER")
                  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "USERS" ENABLE
                  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "USERS" ;

                  CREATE TABLE "ALL_CITY"
                  (     "CODE_CITY" VARCHAR2(10 BYTE) NOT NULL ENABLE,
                       "CITY_NAME" VARCHAR2(70 BYTE) NOT NULL ENABLE,
                       "CITY_TYPE" NUMBER(10,0) NOT NULL ENABLE,
                       CONSTRAINT "PK_ALL_CITY" PRIMARY KEY ("CODE_CITY")
                  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "USERS" ENABLE
                  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "USERS" ;

                  CREATE TABLE CUSTOMER_FILE
                  (     "ID_CUSTOMER_FILE" NUMBER(10,0) NOT NULL ENABLE,
                       "CODE_CITY" VARCHAR2(7 BYTE) DEFAULT NULL,
                       "ID_OWNER" NUMBER(10,0) NOT NULL ENABLE,
                       PRIMARY KEY ("ID_CUSTOMER_FILE")
                  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "USERS" ENABLE,
                       CONSTRAINT "FK_OWNER" FOREIGN KEY ("ID_OWNER")
                       REFERENCES "OWNER" ("ID_OWNER") ENABLE,
                       CONSTRAINT "FK_CF_CITY" FOREIGN KEY ("CODE_CITY")
                       REFERENCES "ALL_CITY" ("CODE_CITY") ENABLE
                  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                  TABLESPACE "USERS" ;

                  CREATE VIEW "CF_CITY" ("CODE_CITY", "CITY_NAME") AS
                  SELECT c.CODE_CITY AS CODE_CITY, c.CITY_NAME AS CITY_NAME
                  FROM ALL_CITY c
                  WHERE c.CITY_TYPE= 2 OR c.CITY_TYPE=23 OR c.CITY_TYPE= 5;

                  My query raises the error 01482. 00000 - "unsupported character set".
                  And when I remove the left join on the view (CF_CITY), everything works great.

                  Thanks for your time. Your help is really appreciated.
                  • 6. Re: ORA 01482: unsupported character set when joining on a view
                    Nitesh.
                    Check whether view CF_CITY is successfully created or not..
                    • 7. Re: ORA 01482: unsupported character set when joining on a view
                      Nitesh.
                      And also check NLS_SETTINGS for character set howevr It cant be an issue but since you are performing conversion also so just check it out too ...
                      • 8. Re: ORA 01482: unsupported character set when joining on a view
                        Nitesh.
                        Hey i tried testing here my best but only cause I can say is character set used to perform the operation that is the CONVERT function is not a supported character set .
                        • 9. Re: ORA 01482: unsupported character set when joining on a view
                          986193
                          The view is succefully created,

                          here are the nls settings:

                          NLS_LANGUAGE     FRENCH
                          NLS_TERRITORY     FRANCE
                          NLS_CURRENCY     �
                          NLS_ISO_CURRENCY     FRANCE
                          NLS_NUMERIC_CHARACTERS     ,
                          NLS_CALENDAR     GREGORIAN
                          NLS_DATE_FORMAT     DD/MM/RR
                          NLS_DATE_LANGUAGE     FRENCH
                          NLS_SORT     FRENCH
                          NLS_TIME_FORMAT     HH24:MI:SSXFF
                          NLS_TIMESTAMP_FORMAT     DD/MM/RR HH24:MI:SSXFF
                          NLS_TIME_TZ_FORMAT     HH24:MI:SSXFF TZR
                          NLS_TIMESTAMP_TZ_FORMAT     DD/MM/RR HH24:MI:SSXFF TZR
                          NLS_DUAL_CURRENCY     ?
                          NLS_COMP     BINARY
                          NLS_LENGTH_SEMANTICS     BYTE
                          NLS_NCHAR_CONV_EXCP     FALSE

                          The convert function with the provided character sets seems to work properly, but it failed when joining on the view.
                          • 10. Re: ORA 01482: unsupported character set when joining on a view
                            stefan nebesnak
                            Could be bug (4686909, 4746930, ..), see this thread:
                            Charset not supported when using convert and a join

                            Check the patches that have been applied:
                            C:\>\oracle\opatch\opatch lsinventory
                            Post the NLS_CHARACTERSET parameter:
                            select t.name, t.value$ from sys.props$ t where t.name = 'NLS_CHARACTERSET';
                            • 11. Re: ORA 01482: unsupported character set when joining on a view
                              986193
                              Hi stefan, thanks for your answer.

                              Here is the result of SELECT * FROM nls_database_parameters; :
                              NLS_LANGUAGE     AMERICAN
                              NLS_TERRITORY     AMERICA
                              NLS_CURRENCY     $
                              NLS_ISO_CURRENCY     AMERICA
                              NLS_NUMERIC_CHARACTERS     .,
                              NLS_CHARACTERSET     US7ASCII
                              NLS_CALENDAR     GREGORIAN
                              NLS_DATE_FORMAT     DD-MON-RR
                              NLS_DATE_LANGUAGE     AMERICAN
                              NLS_SORT     BINARY
                              NLS_TIME_FORMAT     HH.MI.SSXFF AM
                              NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
                              NLS_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
                              NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
                              NLS_DUAL_CURRENCY     $
                              NLS_COMP     BINARY
                              NLS_LENGTH_SEMANTICS     BYTE
                              NLS_NCHAR_CONV_EXCP     FALSE
                              NLS_NCHAR_CHARACTERSET     AL16UTF16
                              NLS_RDBMS_VERSION     10.2.0.1.0

                              My oracle version is 10.2.0.1.0

                              Unfortunately I cannot upgrade or patch the database to a newer version so I tested the suggested workaround by setting cursor_sharing at 'EXACT' but I am getting the same error again.
                              • 12. Re: ORA 01482: unsupported character set when joining on a view
                                stefan nebesnak
                                user10820282 wrote:
                                I need the convert function to filter a table on a column which contains accented characters.
                                I am using Oracle 10G and my database charset is US7ASCII

                                This query works fine.
                                SELECT * FROM User u WHERE UPPER(CONVERT(u.name, 'US7ASCII', 'WE8ISO8859P1')) LIKE UPPER('name%');
                                As BluShadow said, US7ASCII is 7 bit Oracle ASCII (127 codes), so what are you trying to achieve?

                                The syntax of the convert function is:
                                convert( string1 , char_set_to , [ char_set_from ] )
                                Why are you using WE8ISO8859P1 (Latin-1) as source character set?
                                Why are you using US7ASCII (Oracle ASCII) as destination character set?

                                Check the valid character sets:
                                select * from v$nls_valid_values t where t.PARAMETER = 'CHARACTERSET';
                                It seems like you are trying to convert user.name (US7ASCII) from WE8ISO8859P1 to US7ASCII.

                                See example here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions027.htm

                                Edited by: stefan nebesnak on Jan 22, 2013 9:00 AM
                                • 13. Re: ORA 01482: unsupported character set when joining on a view
                                  chris227
                                  Hi,

                                  is it not the other way round? You got some 'WE8ISO8859P1' characters for which you search in your ASCII7-Table
                                  Therefore something like this seems more correct to me
                                  with data as (
                                  select 'Andre' name from dual
                                  )
                                  
                                  select
                                  *
                                  from data
                                  where
                                  upper(name) = upper(convert('André', 'US7ASCII', 'WE8ISO8859P1'))
                                  
                                  NAME
                                  Andre
                                  You should provide some simple test data and describe your aim in simple words, so that we can show you some workarounds around this probably convert-bug.
                                  • 14. Re: ORA 01482: unsupported character set when joining on a view
                                    stefan nebesnak
                                    user10820282 wrote:

                                    SELECT * FROM User u WHERE UPPER(CONVERT(u.name, 'US7ASCII', 'WE8ISO8859P1')) LIKE UPPER('name%');
                                    The default value for source character set argument [ char_set_from ] is the database character set, so you can omit this parameter if you are using database column.
                                    convert( string1 , char_set_to , [ char_set_from ] )
                                    CONVERT(u.name, 'the name of the character set to which string1 is converted')
                                    Make sure you know how the CONVERT function work.
                                    http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm
                                    1 2 Previous Next