Re: [Q]: CONTEXT one-step query in PL/SQL procedure

On Thu, 19 Feb 1998 11:41:04 -0000, Fat Boy Slim <fatty_at_boy.slim.com> wrote:
>Hi
>
>Im trying to do the following CONTEXT query within PL/SQL. THis query works
>one hundred per cent using SQL*Plus, but will not compile when placed in
>PL/SQL. Anyone know why?
>
> select * from course where
> contains(content,s,1)>0 or
> contains(title,s,2)>0 and
> trainingunit = deptid
> order by score(2) desc, score(1) desc, title;
>
>When this is placed in a PL/SQL procedure, I get an undeclared identifier
>"CONTAINS" message on compile. In SQL Plus or SQL Worksheet, this works
>fine.
>
>

  contains is a "magic" word, it indicates to the parser to re-write the SQL to use the Context Keys. in PL/SQL it doesn't get detected, therefore you need to use the ctx_contains procedure. The worst one is that THIS doesn't work for the same reason:

   INSERT INTO table

        SELECT col1, col2 FROM table2 WHERE contains(title,s,2)>0;

BTW, having multiple single step context queries is generally alot slower (orders of magnitude) than two two step queries. If your tables only have 100s or thousands of rows it probably doesn't matter.

-- 
-- Keith Moore
   President
   KMA Computer Solutions, Inc.

-- 
/*----C/C++--Java--VB--Pro*C--SQL--OCI--Java--Delphi--ODBC--COBOL-----* 
 *        When the project must be saved at all costs:                *
 * KMA Computer Solutions, Inc.   Project Troubleshooting/Recovery    *
 *---------Linux---AIX---HPUX---SYSV---Novell---NT---OS/2---'95-------*/