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

[Quoted] 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 - 16:25:56 CET