Re: multiple word search (forms block based on a procedure)

Hey, Thanks a million!!!!

So, what I did was, I took the cursors and put them into text variables and added to them as needed using native dynamic SQL.

But, I'm using your function and it works quite nicely!

You rock! Thanks

sldcrew2k_at_yahoo.com (steve deno) wrote in message news:<8e6b14d8.0203140725.79d65d70_at_posting.google.com>...
> Thanks so much! I am working on that right now.
>
> Another question though, What if the queries in question are actually
> cursors, which are subsequently opened based on some criteria? as
> follows:
>
> IF "criteria from form" <> 0 THEN
> OPEN x_cur;
> LOOP
> FETCH x_cur INTO xcursor_rec;
> EXIT WHEN x_cur%NOTFOUND;
> form_tbl(idx) := survey_rec;
> ELSE OPEN y_cur;
> LOOP
> FETCH y_cur INTO ycursor_rec;
> EXIT WHEN y_cur%NOTFOUND;
> form_tbl(idx) := survey_rec;
> ...
>
> Can I use the dynamic SQL as part of the cursor definition?
>
> Your help is REALLY appreciated!
>
>
>
>
> Ken Denny <ken_at_kendenny.com> wrote in message news:<Xns91D0BD989F0E2kendenny_at_65.82.44.7>...
> > sldcrew2k_at_yahoo.com (steve deno) wrote in
> > news:8e6b14d8.0203131440.7812824f_at_posting.google.com:
> >
> > > I have a forms block based on a pl/sql procedure that returns a pl/sql
> > > table. It takes a search term as an argument(a text item that is
> > > passed as an in parameter) Everything was splendid until the users
> > > wanted to be able to put multiple words in the text item and have it
> > > work the same way. I attacked the problem this way: The parameteer
> > > is called p_query_text and the field I was searching in was called
> > > description. So I search for spaces and try to split words out by
> > > adding the following to my query. I am getting unpredictable results.
> > > Do you know a better(preferably simpler) way?
> > >
> > > AND INSTR(p_query_text, ' ') > 0 and
> > > (lower(u.description)like
> > > '%'||lower(substr(p_query_text,1,INSTR(p_query_text,' ',1)))||'%' AND
> > > lower(u.description) like
> > > '%'||lower(substr(p_query_text,INSTR(p_query_text,'
> > > ',1)+1,INSTR(p_query_text,' ',2)))||'%')
> >
> > One problem I see here is that if there is only one space in p_query_text
> > then INSTR(p_query_text,' ',2) is going to return 0 making the substr null
> > and you'll be looking for
> > lower(u.description) like '%' || NULL || '%'
> > which is every row in the table. I assume you want to be able to enter one
> > or more words in the text item, so you'll have to use dynamic SQL and
> > build your "WHERE" clause based on the number of words in the text item.
> > I'll give you some code I wrote to help you out. I wrote a function called
> > "scan_to" in a former life. It works pretty much the same as the C
> > function "strtok" which is useful for parsing character strings. Here's
> > the code for scan_to:
> >
> > FUNCTION scan_to(scanstr IN OUT VARCHAR2,
> > scanfor IN VARCHAR2 := ' ',
> > mults IN BOOLEAN := TRUE,
> > strip IN BOOLEAN := TRUE)
> > RETURN VARCHAR2
> > IS
> > /*
> > || This function scans a string for another string. It returns the
> > || portion of the string which occurs prior to the scanfor string. If the
> > || strip boolean is true it strips the returned string from the original
> > || string as well as the scanfor string. If the mults boolean is true,
> > || multiple occurances of the scanfor string are stripped. The mults
> > || boolean has no meaning when the strip boolean is false.
> > */
> > startpos NUMBER;
> > scount NUMBER;
> > returnstr VARCHAR2(32767);
> > BEGIN
> > scount := LENGTH(scanfor);
> > startpos := INSTR(scanstr,scanfor);
> > IF startpos = 0
> > THEN
> > returnstr := scanstr;
> > ELSE
> > returnstr := SUBSTR(scanstr,1,startpos-1);
> > END IF;
> > IF strip
> > THEN
> > scanstr := SUBSTR(scanstr,LENGTH(returnstr)+scount+1);
> > IF mults
> > THEN
> > WHILE SUBSTR(scanstr,1,scount)=scanfor
> > LOOP
> > scanstr := SUBSTR(scanstr,scount+1);
> > END LOOP;
> > END IF;
> > END IF;
> > RETURN returnstr;
> > END scan_to;
> >
> > Now to use this to build your "WHERE" clause:
> > where_clause := 'WHERE [everything up to the 'AND INST...' part]';
> > BEGIN
> > .
> > .
> > .
> > WHILE p_query_text IS NOT NULL
> > LOOP
> > where_clause := where_clause ||
> > ' AND LOWER(u.description LIKE ''%''|| ' ||
> > scan_to(p_query_text,' ',TRUE,TRUE) || '||''%'')'
> > END LOOP;
> > .
> > .
> > .
> > Now || where_clause on to the end of your select command and use DBMS_SQL
> > to execute it.
Received on Thu Mar 14 2002 - 21:39:54 CET