Thread: Dynamic Pl/SQL Report
05-25-04, 06:00 #1Registered User
- Join Date
- Jun 2003
Unanswered: Dynamic Pl/SQL Report
Anyone any ideas how I could overcome this problem, I have a pl/sql report with one cursor in it which pulls out all the info. The problem is I have a line it it like this:
where jjobh.jcategory like decode(v_askcat, 'ALL', '%', v_askcat)
so that if the user types in ALL it will select all records in the field jcategory. There is however an index on this field and so if the user types in ALL the index is used but it actually slows things down rather than speed it up because of the amount of records.
What I would like to do is set up the report so that if the user types in a value for jcategory other than ALL, the index is used. If the user types ALL then it shouldn't use the index. I'm not clear on the best way to do this, would I have to create two seperate cursors and put them within procedures? Or is there some other way I could do this? I'm using oracle version 7.3
05-25-04, 09:19 #2Registered User
- Join Date
- Mar 2004
- Berkshire, UK
I don't know if this will work in 7.3 or not but ....
TYPE cur_typ IS REF CURSOR;
v_sql_query := 'Select field from table';
OPEN c1 FOR v_sql_query;
FETCH c1 INTO vVar;
EXIT WHEN c1%NOTFOUND;
EXIT WHEN c1%NOTFOUND IS NULL;
There are 10 types of people in the world, those that know Binary and those that don't.