This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Jan 23, 2013 6:59 AM by 986193 RSS

ORA 01482: unsupported character set when joining on a view

986193 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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. Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Can you post the error?
  • 5. Re: ORA 01482: unsupported character set when joining on a view
    986193 Newbie
    Currently Being Moderated
    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. Explorer
    Currently Being Moderated
    Check whether view CF_CITY is successfully created or not..
  • 7. Re: ORA 01482: unsupported character set when joining on a view
    Nitesh. Explorer
    Currently Being Moderated
    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. Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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