0

Search list(list of values) for a search parameter in Oracle BI Publisher (10g) works fine when the query doesn't contain a bind variable (parameter identifier) but when a bind variable is introduced in the constraint of search list query, it keeps on searching endlessly.

Using a bind variable in the list of values for menu parameters works fine.

Is it an issue regarding BI Publisher or what should I do to refresh a search list by listening to another parameter?

Edit: LOV codes for the search parameter

select col1|| ' - ' || col2 as item
  from table --search works on applying no filter

select col1|| ' - ' || col2 as item
  from table
 where col3 = substr(:I_col3_param, 0, instr(:I_col3_param,'-')-2)) -- search doesn't work on applying this filter

The first query will obviously return larger number of data but its search time is lesser than that of second query. So, the problem should not be due to huge amount of data.

chemicalkt
  • 816
  • 5
  • 16
  • 33
  • Can you share the search parameter (menu) `LOV` code and the `Data structure` query (the one which searches endlessly)? Also the amount of data that the query operates on would help. – Anjan Biswas Aug 21 '12 at 14:11
  • Would you mind giving a look at the edit? @Annjawn – chemicalkt Aug 22 '12 at 02:28
  • your second query may be slower than the first and its not un-usual since `table` might not have proper index on `col3`. I don't think its an issue with BIPublisher, so you may try creating index on `col3`. Also try to reduce `substr` and `instr` usage on `:I_col3_param` by making it as a static or dynamic LOV list and let the user select only the values you can directly use in the where clause without using the string functions. – Anjan Biswas Aug 22 '12 at 03:00
  • When I try running the queries from SQL Developer, it takes less than 1 sec. So, I guess, the problem is not related to substr, instr, index or stuffs. @Annjawn – chemicalkt Aug 22 '12 at 03:31
  • SQL Developer runs query on a result set basis i.e. 50 rows at a time or 60 rows at a time for large data set. Can you try, running the query in SQLPlus and see how much time it takes? – Anjan Biswas Aug 22 '12 at 03:33
  • I tried running the query in SQLPlus. There as well the query runs in about 1 sec. – chemicalkt Aug 22 '12 at 03:39
  • Well, then I think you should start looking at the BI logs in the appserver and see whats going on. – Anjan Biswas Aug 22 '12 at 03:47

0 Answers0