Oracle PL/SQL/Char Functions/CONTAINS

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

   <source lang="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> 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.

 </source>
   
  

CONTAINS(description, "website") > 3

   <source lang="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> 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>

 </source>
   
  

Proximity searches with contains

   <source lang="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> 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>

 </source>
   
  

Test case-insensitivity with a simple mixed-case query

   <source lang="sql">
   

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.

 </source>