Member Avatar for jt_murphree

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?

  • 2 Contributors
  • 1 Reply
  • 96 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Momerath
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.