Oracle: How to find values present in an external list but not present in a column?

I have a table my_table with column my_column and a large list 开发者_运维百科my_list of LONGs. I'd like to find all values that present in the my_list but not present in the my_table.my_column. How can I do that with SQL without repeating huge list of LONGs twice? Please shed some light as I'm Oracle beginner. Thank you.

EDIT:

my_list is just a short form of comma-separated values (1, 2, 3, 4), it's not a variable, just a way to call.

If your list is constant, it's probably a good idea to store it in a reference table and to use a join as suggested to filter the records.

Otherwise you might reach 2 limits: the max length for a SQL statement and the maximum number of items that Oracle allows in a list. The latter can be solved by splitting your big list into smaller lists (my_column no in (...) and my_column not in (...)).

maybe

select * from my_table where my_column not in (my_list)

where my_list - comma-separated values list. and how large is your list?

SELECT * FROM my_list
EXCEPT
SELECT * FROM my_table