Re: ConText CONTAINS in PL/SQL

From: Rüdiger J. Schulz <r.schulz_at_berlin.de>
Date: Mon, 18 Jan 1999 14:00:53 +0000
Message-ID: <36A33E95.59A900C4@berlin.de>

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 Received on Mon Jan 18 1999 - 08:00:53 CST