Test case-insensitivity with a simple mixed-case query : CONTAINS « Char Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Char Functions
3. CONTAINS

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.

   
    
    
    
  

Related examples in the same category