CODEWITHSUNDEEP
sqloracle-database
Viktor Carlson
Viktor Carlson

Reputation: 1008

Oracle SQL -> Select row where ID is in Long txt File -> string literal too long

txt file that contains ~30k of comma seperated IDs

for small .txt files containing less than 100 IDs I used this simple Query:

select * from employee
where employee.id in ('123', '456', '478', '658')

When I now paste the content of the 30k IDs in my .sql file I get the following error:

ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.
Error at Line: 7 Column: 20

Can someone help me how I can use another data Type. It would be good if it would still be possible to just copy and paste the long list of comma separated values into the SQL script.

Upvotes: 0

Views: 506

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

You could use a collection and a table collection expression:

select * from employee
where employee.id in (select * from table(sys.odcivarchar2list('123', '456', '478', '658')))

Or preferably, as they are numbers:

select * from employee
where employee.id in (select * from table(sys.odcinumberlist(123, 456, 478, 658)))

Or you could turn that into a CTE and join to it:

select e.*
from (
  select *
  from table(sys.odcinumberlist(123, 456, 478, 658))
) t
join employee e on e.id = t.column_value

You could also create a numeric table type and use that with member of instead of in:

create type mynumbertable as table of number;

select * from employee
where employee.id member of (mynumbertable('123', '456', '478', '658'));

... but that might be overkill for an ad hoc task.

db<>fiddle

Or, of course, put the values into a real table (which could be a temporary table) and join to that, but that doesn't really work for your copy-and-paste scenario.

Upvotes: 1

Related Questions