Oracle Query – Listing the Most Popular Item Being Ordered

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit.

This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value?

0xcb 0xcf is indeed nonsense as utf-8. It's a continuation byte without any appropriate context.

Trying other encodings, it's:

• ËÏ in latin-1. Unlikely. Same in iso-8859 3,4,9,10,14,15, and in the default Windows encoding for Western Europe / US, cp1252.
• ËĎ in iso-8859-2
• ЫЯ in iso-8859-5
• ثد in iso-8859-6
• ΛΟ in iso-8859-7
• หฯ in iso-8859-11
• ĖĻ in iso-8859-13

Any of those look like likely candidates, given what you know about the original data?

You need to either export in a known encoding, or determine the encoding of the input reliably.

It's also possible that the data is in fact starting with a utf-8 continuation byte and was supposed to be utf-8, but something has mangled it by cutting utf-8 strings up byte-wise.

If you can post a complete line of exported CSV it might help.

You need to create a list of values which is unfortunately quite cumbersome in Oracle (due to the missing values() clause).

But the following will work:

with id_list as (
  select 12345 as id from dual union all 
  select 98765 from dual union all 
  select 65436 from dual union all 
  select 81764 from dual
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = il.id
where i.item_code is null;

But given the nature of the query this is a bit complicated to parameterize though. The best solution is probably to create a (global) temporary table, then put the list of IDs you receive into that table and use that instead of the common table expression (the with (...) part)

Edit

There is a (cumbersome) way to "explode" a comma separated list of values, which might be "easier" in this case:

with base_value(id_string) as (
  select '12345,98765,65436,81764' from dual
), id_list(id) as (
  select regexp_substr (id_string, '[^,]+', 1, level)
  from base_value
  connect by regexp_substr (id_string, '[^,]+', 1, level) is not null
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = to_number(il.id)
where i.item_code is null;

But that might quickly hit Oracle's limit of 4000 bytes for a string literal....