-1

I am writing a query where 'batch_name' is the parameter, some times I get only one batch name and sometime I get 2 or more batch names. How can I handle this in Oracle BI Publisher query,

Here is my query,

Select * from pay_batch_headers pbh Where UPPER(pbh.batch_name) = UPPER(:p_batch_name)

Now this query will handle for only one batch name, I want it to handle multiple batch names.

something like Where UPPER(pbh.batch_name) IN ('Batch1','Batch2','Batch3') But problem to use IN clause is I cant predict number of batches I have to query. Can any one help me in this please.

Aleksej
  • 22,443
  • 5
  • 33
  • 38
Raghu
  • 1,324
  • 7
  • 24
  • 47

1 Answers1

1

You have two choices. One is to munge the variables together into a string and use some method, such as regexp_like():

where regexp_like(upper(pbh.batch_name), ??)

The parameter string should look like: '^abc|def|ghi|jkl$'. You can make it as long as you like.

Another method is to use execute immediate. Dump the values into a SQL query as a string, using IN. The advantage of this method is that it can more easily use indexes

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I was looking at 1st solution , here where can I Pass parameter `:p_batch_name`. The query will look like `Select * from pay_batch_headers pbh Where regexp_like(upper(pbh.batch_name), ??);` – Raghu Nov 24 '16 at 16:49
  • Thank you it worked, i replaced ?? to my parameter :p_batch_name – Raghu Nov 24 '16 at 17:08