I have a query that takes up to three input parameters. I run it in sql*plus and just pass one parameter and it gives out the results within couple of seconds.
I created an Apex report using the exact same query. The three input parameters were passed using the text field. When I run it from Apex and pass the same parameter using one of the three text fields that I have created, it runs forever - 20 minutes or more and times out.
SQL_Query that runs quicks is like this:
select <fields>
from <multiple tables >
where <join conditions>
and r.album_name like '&p1_album_name'||'%'
and r.artist_name like '&p1_artist_name'||'%'
and r.track_name like '&p1_track_name'||'%'
I changed the parameters in the Apex query to use the text fields that I have created. So the query is changed to:
select <fields>
from <multiple tables >
where <all join conditions>
and r.album_name like :p1_album_name||'%'
and r.artist_name like :p1_artist_name||'%'
and r.track_name like :p1_track_name||'%'
I tried changing the parameters to v('p1_album_name'), but that did not work.
Is there a reason that query execution would be slower in this case?
Thanks,
Kamy