Re: ConText CONTAINS in PL/SQL

Mainly because you have to manage all the temporary tables, which are a bunch of arse, especially if you are wanting to do a union or using OWA or somthing like that, where you are doing lots of repeat queries frequently and have to manage the whole lot through a single results table with sessionids and the like. ugh! And then try catching meaningful errors.

here's how I did it:

procedure searchcat(

	searchtext in varchar2 DEFAULT NULL,
	searchmode in char DEFAULT NULL,
	deptid in char DEFAULT NULL,
	contid in char DEFAULT NULL) is

	TYPE csearchrowtyp is record
	(cs_coursecode course.coursecode%type,
	cs_score integer,
	cs_trainingunit course.trainingunit%type,
	cs_title course.title%type,
	cs_order varchar2(2));

	TYPE csearchcurtyp is REF CURSOR RETURN csearchrowtyp;
	csearch csearchcurtyp;
	cc csearchrowtyp;
	i integer;
	t integer;
	t1 integer;
	t2 integer;
	t3 integer;
	s varchar2(2000);
	d varchar2(50);

	stop_word exception;
	pragma exception_init(stop_word,-20000);
	
begin
	-- only search if there is an argument
	s:=replace(searchtext,'  ',' ');
	s:=ltrim(s,' ');
	s:=rtrim(s,' ');
	if s is not null then
		-- ensure no multiple spaces and lead and trail space
is present
		if searchmode='ANY' then
			s:=replace(s,' ','}, {');
			s:='{' || s || '}';
		end if;
		if searchmode='ALL' then
			s:=replace(s,'*','%');
		end if;
		if s='%' then
			-- special for wildcard only match
			insert into ctxres1
			select coursecode,userenv('sessionid'),10 from
course;
			commit;
		else
			-- perform text query

ctx_query.contains('course_title',s,'ctxres1',1,userenv('sessionid'));

ctx_query.contains('course_keywords',s,'ctxres2',1,userenv('sessionid'));

ctx_query.contains('course_content',s,'ctxres3',1,userenv('sessionid'));

                        delete from ctxres2 where
conid=userenv('sessionid') and

			textkey in (select textkey from ctxres1
			where conid=userenv('sessionid'));
			delete from ctxres3 where
conid=userenv('sessionid') and
			textkey in (select textkey from ctxres1
			where conid=userenv('sessionid'));
			delete from ctxres3 where
conid=userenv('sessionid') and
			textkey in (select textkey from ctxres2
			where conid=userenv('sessionid'));
			commit;
		end if;
		if deptid='ALL' then
			-- all depts
			select count(*) into t1 from ctxres1
			where conid=userenv('sessionid');
			select count(*) into t2 from ctxres2
			where conid=userenv('sessionid');
			select count(*) into t3 from ctxres3
			where conid=userenv('sessionid');
			t:=t1+t2+t3;
			open csearch for
				select
textkey,score,trainingunit,title,'1' from ctxres1 a, course b
				where textkey=coursecode and
conid=userenv('sessionid')
				union
				select
textkey,score,trainingunit,title,'2' from ctxres2 c, course d
				where textkey=coursecode and
conid=userenv('sessionid')
				union
				select
textkey,score,trainingunit,title,'3' from ctxres3 c, course d
				where textkey=coursecode and
conid=userenv('sessionid')
				order by 5, 2 desc, 4;
		elsif deptid != 'ALL' then
			-- a dept
			select count(*) into t1 from ctxres1, course
			where conid=userenv('sessionid') and
			textkey=coursecode and
			trainingunit=deptid;
			select count(*) into t2 from ctxres2, course
			where conid=userenv('sessionid') and
			textkey=coursecode and
			trainingunit=deptid;
			select count(*) into t3 from ctxres3, course
			where conid=userenv('sessionid') and
			textkey=coursecode and
			trainingunit=deptid;
			t:=t1+t2+t3;
			open csearch for
				select
textkey,score,trainingunit,title,'1' from ctxres1, course
				where textkey=coursecode and 
				conid=userenv('sessionid') and
				trainingunit=deptid
				union
				select
textkey,score,trainingunit,title,'2' from ctxres2, course
				where textkey=coursecode and 
				conid=userenv('sessionid') and
				trainingunit=deptid
				union
				select
textkey,score,trainingunit,title,'3' from ctxres3, course
				where textkey=coursecode and 
				conid=userenv('sessionid') and
				trainingunit=deptid
				order by 5, 2 desc, 4;
		end if;
		fetch csearch into cc;
		if (contid is not null) and (contid != 'ALL') then
			-- continuing a previous search
			while cc.cs_coursecode != contid loop
				fetch csearch into cc;
				exit when csearch%NOTFOUND;
			end loop;
		end if;
		if csearch%FOUND then 
			-- output the results
		else
			-- never found any results
			searchform('NO ITEMS FOUND',searchtext);
		end if;
		-- clean up the mess left by context
		close csearch;
		delete from ctxres1 where conid=userenv('sessionid');
		delete from ctxres2 where conid=userenv('sessionid');
		delete from ctxres3 where conid=userenv('sessionid');
		commit;	
	else
		searchform('NO SEARCH WORD ENTERED');
	end if;
exception
   	when stop_word then
		searchform('INVALID SEARCH WORD ENTERED',searchtext);
	when others then
		htp.htmlOpen;
		htp.headOpen;
		htp.Title('Catalogue Search Error');
		htp.headClose;
		htp.bodyOpen;
		htp.p(sqlerrm);
		htp.p('<br>A system error has occured. Please correct
the problem, press back, and try again.');
		htp.bodyClose;
		htp.htmlClose;

On Tue, 19 Jan 1999 06:37:33 GMT, bonanos_at_yahoo.com wrote:

>Why don't you just use a two step query?
>I can't see why the original poster would prefer to use a one step.
>
>
>In article <36A33E95.59A900C4_at_berlin.de>,
> "Rüdiger J. Schulz" <r.schulz_at_berlin.de> wrote:
>> u are right! the only way - in the moment - is to try the following way with
>> dynamic-sql:
>>
>> CREATE OR REPLACE PROCEDURE yyy.ctxcheck
>> (mSuch VARCHAR2)
>> IS
>> mxxxid NUMBER;
>> xrows NUMBER;
>> xcur NUMBER;
>> xquery VARCHAR2(2000);
>> BEGIN
>> xcur := DBMS_SQL.OPEN_CURSOR;
>> xquery := 'Select xxxid from yyy.data Where contains(volltext,
>> '||chr(39)||mSuch||chr(39)||') > 0';
>> DBMS_OUTPUT.PUT_LINE(xquery);
>> DBMS_SQL.PARSE(xcur, xquery, DBMS_SQL.V7);
>> DBMS_SQL.DEFINE_COLUMN(xcur,1,mxxxid);
>> xrows := DBMS_SQL.EXECUTE(xcur);
>> IF xrows > 0 THEN
>> xrows := DBMS_SQL.FETCH_ROWS(xcur);
>> DBMS_SQL.COLUMN_VALUE(xcur,1,mxxxid);
>> ELSE
>> mxxxid := 0;
>> END IF;
>> DBMS_OUTPUT.PUT_LINE( mxxxid );
>> END;
>> /
>> show errors;
>>
>> Richard Murphy schrieb:
>>
>> > That does not work from within a PL/SQL block. For some reason PL/SQL does
>> > not recognize the CONTAINS function. It works fine using SQL or embedded
>> > SQL (ProC), but not from within a block - i get a CONTAINS not declared
>> > error.
>> >
>> > Thanks though, R.
>> >
>> > "Rüdiger J. Schulz" wrote:
>> >
>> > > try the following sql-statement for a one-step-query:
>> > >
>> > > select test1, test2 from tablename where CONTAINS(test1,
>> > > 'searchvalue') > 0;
>> > >
>> > > (here is test1 the column on which the context-policy runs)
>> > >
>> > > Richard Murphy schrieb:
>> > >
>> > > > Anyone know how to access the ConText CONTAINS function from within a
>> > > > PL/SQL block? Most likely the only way is a two step query, but would
>> > > > prefer a one step query.
>> > > >
>> > > > Thanks, R.
>> > >
>> > > --
>> > > b e r l i n . d e - d e r o n l i n e d i e n s t d e r s t
>> > > a d t
>> > > ___________________________________________________________
>> > > Primus-Online Berlin-Brandenburg GmbH & Co. KG
>> > > debis Haus am Potsdamer Platz
>> > > 10875 Berlin
>> > >
>> > > Rüdiger J. Schulz
>> > > Technik
>> > >
>> > > tel: +49 (30) 2554-1497
>> > > fax: +49 (30) 2554-3725
>> > > email: r.schulz_at_berlin.de
>> > > homepage: http://www.berlin.de
>>
>> --
>> b e r l i n . d e - d e r o n l i n e d i e n s t d e r s t a d t
>> ___________________________________________________________
>> Primus-Online Berlin-Brandenburg GmbH & Co. KG
>> debis Haus am Potsdamer Platz
>> 10875 Berlin
>>
>> Rüdiger J. Schulz
>> Technik
>>
>> tel: +49 (30) 2554-1497
>> fax: +49 (30) 2554-3725
>> email: r.schulz_at_berlin.de
>> homepage: http://www.berlin.de
>>
>>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Jan 21 1999 - 11:22:56 CST