Hello,
Was attempting to query data from a table for 145000 values . Created a SELECT statement that used multiple IN clauses, where each IN clause had 990 values in it . Kept getting ORA-00913 despite ensuring each IN clause had < 1000 values. After a lot of trials noticed that the error starts after including the 65536'th value.
Wanted to know if anyone is aware of the reason.
Steps to recreate :
create table test_numbers (a number);
insert into test_numbers
select level from dual connect by level <=145513;
commit;
Now try to select data from the table :
select * from table where (column in (1,2,3....) or column in (991,992,993...)..)
Works fine until we exceed the IN clause with 65535.
Get ORA-00913 when the last IN clause has value 65536 and beyond. Got the same error from two different development environments.
ORA-00913-script.zip (72.31 KB)ORA-00913_error.png (121.2 KB)
Database version : 19.11.0.0.0
SQL Developer version: 21.2.0.187
PS: Just curious why it raised ORA-00913.