0

Excuse if the question is trivial, but I've been having trouble figuring this out.

So i have a setup where some values for a query will be received from Saxon. In example code, that would be like

select *
from a
where a.value > 0 AND (a.name IN ':variable')

In this case ':variable' will be switched out to a list like ('abc','de f','Egh1', ...) But this has started causing a problem when the list is more than 1000 elements long.

I was trying to figure out how to convert variable into a sub-query through CAST, but have been unsuccessful so far. How would it be recommend to handle a case like this if i need the changes to be done entirely in SQL?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
meta
  • 153
  • 12
  • If the list can be greater than 1000 elements, you should consider to use collections. Or at least correctly bind this variable as clob. In the best case (Oracle 12C and above with appropriate settings) varchar2 will allow you 32k symbols, which can be not enough for long lists – astentx May 12 '21 at 07:29

1 Answers1

1

One option is to create a table, store those values into it and rewrite your query to

and a.name in (select b.name from a_new_table b)

If more than a single user uses that new table, consider using a global (or private, depending on your Oracle version) temporary table so that everyone sees only their own data, or include another identifier to serve the same purpose.

Another option is to split that loooong list of elements into rows; you'd do that in a subquery which would act just like the previous example, e..g

and a.name in (select regexp_substr(:variable, '[^,]+', 1, level) as var_name
               from dual
               connect by regexp_count(:variable, ',') + 1
              )
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • yes, i agree that tables would be a better solution, but the second seems to do what i need in a quick fix for now. but the values from :variable seem to be mistaken for parameters in the function. any tips on how to deal with that? – meta May 12 '21 at 19:18
  • 1
    Sure. Don't use colon with its name (as for a bind variable) - create a function that accepts an IN **parameter**. – Littlefoot May 12 '21 at 19:23