Re: context contains in procedure

From: <bonanos_at_yahoo.com>
Date: Mon, 09 Nov 1998 10:53:55 GMT
Message-ID: <726hk3$t55$1@nnrp1.dejanews.com>

> I have a context server and text search application which works wonderfully,
> but now I want to create some procedures to text search.
>
> For a simple example, if I were to use a query such as:
>
> select * from mytable
> where contains(text_col, 'me') > 0;
>
> everything works fine. However, stick that same query inside a procedure
> and it results in error:
>
> PLS-00201: identifier 'CONTAINS' must be declared.

The problem is not with permissions. When you use context in a procedure you do it differently to command line sql.

You have to do a two step query using: the following proc: ctx_query.contains(policy,search string, 'ctx_docs');

here is an example procedure.(if you are unfamilar with the HTP packages, all it does is print output to the web).

--- CREATE OR REPLACE PROCEDURE HDI_SERVICE_SEARCH_DO (search_string in varchar2,

suburb_cluster in varchar2,

no_results in varchar2,

search_where in varchar2 default 'all',

ccat in varchar2 default null) is

This cursor interogates the docs table after it has been
populated by context, context is an oracle textsearch sub-system

cursor c1 is
SELECT SCORE,serv_name,serv_descr,serv_cost,cat_code,inst_name,inst_code FROM hdi_services_text_data,ctx_docs,des.hdi_services,hdi_institutes5 WHERE hdi_services_text_data.serv_id = textkey

and hdi_services_text_data.serv_id=hdi_services.serv_id
and hdi_services.hdi_id=hdi_institutes5.inst_code
and hdi_services.CAT_CODE = ccat

ORDER BY -SCORE ; results varchar2(3) default 'no';

BEGIN
htp.br;

Search in all categories if search_where = 'all' then --execute the context query ctx_query.contains('hdi_services', search_string , 'ctx_docs');

		htp.p('<table border=0 cellpading=0 cellspacing=0 width=100%>');
                        --loop through the results
			for c2rec in c2 loop
				results := 'yes';
				htp.p('<tr>');
					htp.p('<td align=right><font
face=arial><tt>');
						htp.p('<i>Company name:</i>');
					htp.p('</tt></face></td>');
					htp.p('<td ><font face=arial

size=+1><tt>');

htp.anchor('/des/owa/hdi_inst_details?instcode='||c2rec.inst_code,c2rec.inst_nam e);

					htp.p('</tt></face></td>');
				htp.p('</tr>');

				htp.p('<tr>');
					htp.p('<td align=right><font
face=arial><tt>');
						htp.p('<i>Service name:</i>');
					htp.p('</tt></face></td>');
					htp.p('<td><font face=arial><tt>');
						htp.p(c2rec.serv_name);
					htp.p('</tt></face></td>');
				htp.p('</tr>');

				htp.p('<tr>');
					htp.p('<td align=right><font
face=arial><tt>');
						htp.p('<i>Service
description:</i>');
					htp.p('</tt></face></td>');
					htp.p('<td><font face=arial><tt>');
						htp.p(c2rec.serv_descr);
					htp.p('</tt></face></td>');
				htp.p('</tr>');

				htp.p('<tr>');
					htp.p('<td align=right><font
face=arial><tt>');
						htp.p('<i>Service
category:</i>');
					htp.p('</tt></face></td>');
					htp.p('<td><font face=arial><tt>');

htp.p(hdi_get_category(c2rec.cat_code));
					htp.p('</tt></face></td>');
				htp.p('</tr>');
				end loop;
		htp.p('</table>');
	end if;

blah blah blah

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Nov 09 1998 - 04:53:55 CST