Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: PL SQL Question - Need help please

    I would like to write a loop in PLSQL to run a 225 different queries on a table.

    That is i have 225 stop worded queries to run, and would like to write a procedure that could automate this, when it has run the query i would also like for it to output the results into a table (e.g. Results1). The queries are numbered 1 to 225.
    I have tried to write some code that gives an idea of what i would like to do, but as i have only been using PLSQL for 1 week i am still a newbie. Any help would be much appreciated!!

    Code:
    Procedure mulitpleQuery
    
    FOR i =1 to 225
    searchstring string
    
    queryno = i
    
    Select stoppedwords from cranqrel
    where queryno = i
    fetch to searchstring
    replace(searchstring,' ',')
    
    
    INSERT INTO results1(DOCNO, REL_SCORE) SELECT DOCNO,SCORE(1)
    FROM CRAN1400
    WHERE CONTAINS (abstract, 'searchstring',1) > 0
    ORDER BY SCORE(1) DESC;
    END;
    Thanks
    Mark

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2004
    Posts
    3

    Still stuck

    Hi,

    Thanks for your reply, but i have tried looking at the manual and hence i am posting here for some pointers! I have only been codeing PL SQL for a week, so i could do with some assistance. That is a few pointers as to what i should be doing, i will then try and work it out my self from the manual.

    Thanks Again
    Mark

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I'm not sure wat a stop-worded query is. Can you give an example of what one or two of these would look like, then we can think about how to generalise it.

    My first thought would be to try to avoid running 225 generated queries, but instead somehow write one GROUP BY report or similar. Even a loop might be preferable.

    The CONTAINS keyword is used by Oracle Text. I must admit I've never used this, but there's a first time for everything...

  5. #5
    Join Date
    Nov 2004
    Posts
    3

    thanks for reply

    I will try my best to explain briefly what I am trying to do:

    I would like to run a 225 different queries on a table and output the results to a different table. The queries are taken from a table called 'cranqrel' which holds the 225 queries the field is called 'stoppedwords' in the table they are numbered 1 to 225 this field is called 'queryno' .

    Rather than manually running the query 225 times on a table called 'cran1400' i would like to write a loop to read the queries from the 'cranqrel' table into a variable possibly then to insert the results into my results table called 'results1'.

    This is how i manually instert a query into the 'results1' table

    INSERT INTO results1(DOCNO, REL_SCORE) SELECT DOCNO,SCORE(1)
    FROM CRAN1400
    WHERE CONTAINS (abstract, '&searchstring',1) > 0
    ORDER BY SCORE(1) DESC;

    As you can see when i run it '&searchstring' ask for the search string which i manually get from 'cranqrel' and paste in. Then it runs the query which inserts into the 'results1' table 'DOCNO' and 'REL_SCORE'.

    This works fine but i have to maunally copy and paste the query, i would like to write a loop to get the query and run the insert for me.

    ** Another less important question, I have been playing about with this and I have also come across another problem, I cant workout how to get a row from a table to be put into a variable. e.g. I would like to read query 1 from the cranqrel table then put it into variable called 'query'. Any ideas?

  6. #6
    Join Date
    Nov 2004
    Posts
    8
    I think you need to have a look at the manual that says annything about cursors.

    The loop you would like to do can be done in a "cursor for loop".

    Inside this loop u can use the results of youre string query and use these results in an insert statement in youre results1 table.

    This should do the trick.
    The CONTAINS and SCORE are features from the intermediate tools ( wich should be installed sepperately if used )

    If you don't want a score but just all of the rows containing the keyword then you can use a simple instring in the where clause.

    hope it will help you on you're way.

  7. #7
    Join Date
    Nov 2004
    Posts
    8
    i do have an other option mabey this can help you
    this is without the use of oracle text / intermedia

    works on a database from 8 and up.

    here is a simple example.
    here are the table defenitions and the data.
    Code:
    -- create "keyword to search with" table
    create table keywords(word varchar2(30));
    
    insert into keywords
    values
       ('test');
    insert into keywords
    values
       ('quick');
    insert into keywords
    values
       ('fox');
    insert into keywords
    values
       ('dog');
    
    -- Create "string to search in" table
    create table string_tab(id integer
                           ,strings varchar2(255) not null
                           );
    insert into string_tab
    values
       (1
       ,'the little brown fox jumps quickly over the lazy dog');
    insert into string_tab
    values
       (2
       ,'in the summer the dog lies in the grass');
    insert into string_tab
    values
       (3
       ,'test, just a simple quick test!');
    insert into string_tab
    values
       (4
       ,'the shade is fine for me, i was just testing my sun block.');
       
    create table search_result( hits number(4)
                              , string_tab_id integer
                              , keyword varchar2(255)
                              );
    here is the search statement wich will fill the search result table.
    Code:
    truncate table search_result;
    
    declare
       -- create a cursor to loop through the keyword table.
       cursor c_keyword is
       select word 
       from keywords ;
       
       cursor c_strings is
       select id, strings 
       from string_tab;
       
       v_hit_count search_result.hits%type;
       
    begin 
       -- create a loop for every row in the keyword cursor.
       for r_keyword in c_keyword
       loop
          -- create a loop for every row in the strings cursor.
          for r_strings in c_strings
          loop
             -- count the hits
             v_hit_count := ( length( r_strings.strings ) - length( REPLACE( r_strings.strings  , r_keyword.word , '' ) ) )/length(r_keyword.word);
         
             -- if hit is found
             if v_hit_count >0
             then
                -- do an insert
                insert into search_result(hits,string_tab_id,keyword)
                   values( v_hit_count,r_strings.id,r_keyword.word );
             end if;      
             -- reset the counter
             v_hit_count := 0;
    
          end loop;
       end loop;
    end;
    /
    commit;
    Good luck.
    hope you can use this in youre situation.

Posting Permissions

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