Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    20

    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

    thanks,
    Ian

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    I don't know if this will work in 7.3 or not but ....

    PHP Code:
    DECLARE
        
    v_sql_query VARCHAR2(4000);
        
    vVar VARCHAr2(100);
        
    TYPE cur_typ IS REF CURSOR;
        
    c1 cur_typ;
    BEGIN
        v_sql_query 
    := 'Select field from table';
        
    OPEN c1 FOR v_sql_query;
            
    LOOP
            FETCH c1 INTO vVar
    ;
                EXIT 
    WHEN c1%NOTFOUND;
                EXIT 
    WHEN c1%NOTFOUND IS NULL;
                
            
    END LOOP;
        
    CLOSE c1;
    END 
    There are 10 types of people in the world, those that know Binary and those that don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •