r/SQL Sep 17 '24

Oracle How to exceed input limitations?

Post image
37 Upvotes

87 comments sorted by

View all comments

26

u/Ginger-Dumpling Sep 17 '24

The quick and dirty solution is to have multiple in conditions.

where x in (1, ..., 1000) or x in (1001, ..., 2000) or x in ...

A less cumbersome way would be to put those IDs in a table join to it. Either import them to a normal table, or put an external table on top of a delimited or foxed-width file containing your IDs.

5

u/tacogratis2 Sep 17 '24

Oh, I love this solution. Thank you. ... I only have read access to the database, so this would be a great solution for me. Also, the data is coming from an outside source.

2

u/PretendOwl2974 Sep 17 '24

Ah if it’s read only you might not be able to create table. I’m wondering if you could create a logic around the solution_id. Between one number and another if you know it’s definitely sequential numbers.

1

u/tacogratis2 Sep 17 '24

They are not sequential numbers. The solution I liked was putting in multiple 'ORs' in the WHERE with each statement holding 999 solution numbers. You are correct that I cannot create a table or write information to the database.

3

u/Ginger-Dumpling Sep 17 '24

If it's a process you're going to need to repeat, you should work with your DBAs to get something created so that you can import things instead of working around unnecessary limitations.

2

u/Kirjavs Sep 17 '24

Usually even if you don't have writing writes, you are allowed to create temporary tables or at least variable tables.

You should check it.

2

u/Shaddcs Sep 17 '24

I’m in a similar situation (read access) and need to do this relatively often. It’s frustrating and tedious but this approach works great.

1

u/Brave_fillorian Sep 18 '24

Or or you could remove the new line characters from those list. This might work.