Raghu Raghu - 15 days ago 8
SQL Question

How to parameterise variable length of Strings?

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.

Answer

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