I have a question involving interaction with oracle. I have a program that submits data to a SSRS report which is pulling data from an oracle database. Basically what I need to do is have a string of text passed in and return all records where a job id field matches a number in that field. Typically my query would look like this

WHERE jobid IN ('id1', 'id2', 'id3')

However, this does not work when I pass the string in as a parameter. There for I am using the insr() function such as:

where instr(:jobid, ptqueue.qgp_id) > 0

This runs extremely slow. Does any one know how I can format the string in order to get the IN clause to work, or of an even better approach of doing this?

5 Years
Discussion Span
Last Post by Momerath

Oracle (like most databases) doesn't support passing in multiple values as a single parameter. So you are stuck doing one of two things:

1) Build the SQL statement dynamically, adding as many parameters as you need.
2) Create a function in Oracle that takes a string and splits it into multiple values and call the function in your SQL statement.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.