CODEWITHSUNDEEP
oracle-database
Mikayil Abdullayev
Mikayil Abdullayev

Reputation: 12367

Getting "ORA-01482: unsupported character set" when using WHERE IN pattern

I have a table with the following structure in Oracle database:

CREATE TABLE PASSENGERS
(ID VARCHAR2(6),
 PASSPORTNO VARCHAR2(14));

I want to get the IDs of the passengers who have been registered more than once. For that I run the following query.

SELECT ID FROM PASSENGERS WHERE PASSPORTNO IN 
  (SELECT PASSPORTNO FROM PASSENGERS
     GROUP BY PASSPORTNO 
     HAVING COUNT(*)>1);

But I get "unsuported character set" error. What's the point I'm missing?

Upvotes: 2

Views: 3523

Answers (2)

ranit b
ranit b

Reputation: 51

Mostly seems like a data issue. Try checking the exact data row which is causing the issue.

Use : DML Error Logging - http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php

Btw, you are doing GROUP BY passportno .Is that correct? (This implies multiple passports can have same passport number). I guess it should be GROUP BY id

Upvotes: 2

ararog
ararog

Reputation: 1092

Since all queries related with PASSPORTNO are running fine you have at least two more things to do:

  1. Run SELECT ID FROM PASSENGERS and check for errors, if the error cames up, then it may be releated with content stored in your table
  2. Try another SQL tool to execute your queries, your client OS may be using a system enconding which the database can't understand both when processing your query of to display the returning rows.

Since both ID and PASSPORTNO are varchar fields, there's a big change to one of then have data in a enconding which oracle can't decode properly.

Upvotes: 2

Related Questions