Proximity searches with contains : CONTAINS « Character String Functions « Oracle PL/SQL Tutorial






SQL>
SQL> CREATE TABLE book (
  2     id   NUMBER (10) PRIMARY KEY,
  3     isbn             VARCHAR2(10),
  4     description      VARCHAR2(500));

Table created.

SQL>
SQL> INSERT INTO book(id,isbn,description)VALUES (1,'3', 'The essential PL/SQL.');

1 row created.

SQL>
SQL> DECLARE
  2     v_isbn book.ISBN%TYPE;
  3     v_score NUMBER(10);
  4  BEGIN
  5
  6     SELECT score(1), isbn
  7     INTO v_score, v_isbn
  8     FROM book
  9     WHERE CONTAINS (description, '10g near Oracle', 1) > 0;
 10
 11     DBMS_OUTPUT.PUT_LINE('Score: '||v_score||' and ISBN: '||v_isbn);
 12
 13  EXCEPTION
 14     WHEN OTHERS
 15     THEN
 16        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 17  END;
 18  /
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

PL/SQL procedure successfully completed.

SQL>
SQL> drop table book;

Table dropped.

SQL>








11.21.CONTAINS
11.21.1.CONTAINS(description, '10g near oracle') > 0
11.21.2.CONTAINS(description, 'website') > 3
11.21.3.Proximity searches with contains