Oracle PL/SQL/Char Functions/CONTAINS

CONTAINS(description, "10g near oracle") > 0

  
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> SELECT isbn
  2  FROM book
  3  WHERE CONTAINS(description, "10g near oracle") > 0;
SELECT isbn
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

SQL>
SQL>
SQL> drop table book;
Table dropped.

CONTAINS(description, "website") > 3

  
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> SELECT isbn
  2  FROM book
  3  WHERE CONTAINS(description, "website") > 3;
SELECT isbn
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

SQL>
SQL> drop table book;
Table dropped.
SQL>

Proximity searches with contains

    
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>

Test case-insensitivity with a simple mixed-case query

    
SQL>
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> SET SERVEROUTPUT ON
SQL>
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 or 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>
SQL>
SQL> drop table book;
Table dropped.