In this example, you gather news articles about different subjects and then classify them. After you create the rules, you can index them and then use the MATCHES statement to classify documents.

1. Create the schema to store the data.

   The news_table stores the documents to be classified. The news_categories table stores the categories and rules that define the categories. The news_id_cat table stores the document IDs and their associated categories after classification.

   create table news_table (
          tk number primary key not null,
          title varchar2(1000),
          text clob);
   
   create table news_categories (
           queryid  number primary key not null,
           category varchar2(100),
           query    varchar2(2000));
   
   create table news_id_cat (
           tk number, 
           category_id number);

2. Load the documents with SQLLDR.

   Use the SQLLDR program to load the HTML news articles into the news_table. The file names and titles are read from loader.dat.

   LOAD DATA
        INFILE 'loader.dat'
        INTO TABLE news_table
        REPLACE
        FIELDS TERMINATED BY ';'
        (tk         INTEGER EXTERNAL,
         title      CHAR,
         text_file  FILLER CHAR,
         text       LOBFILE(text_file) TERMINATED BY EOF)

3. Create the categories and write the rules for each category.

   The defined categories are Asia, Europe, Africa, Middle East, Latin America, United States, Conflicts, Finance, Technology, Consumer Electronics, World Politics, U.S. Politics, Astronomy, Paleontology, Health, Natural Disasters, Law, and Music News.

   A rule is a query that selects documents for the category. For example, the 'Asia' category has a rule of 'China or Pakistan or India or Japan'. Insert the rules in the news_categories table.

   insert into news_categories values
     (1,'United States','Washington or George Bush or Colin Powell');
   
   insert into news_categories values
     (2,'Europe','England or Britain or Germany');
   
   insert into news_categories values
     (3,'Middle East','Israel or Iran or Palestine');
   
   insert into news_categories values(4,'Asia','China or Pakistan or India or Japan');
   
   insert into news_categories values(5,'Africa','Egypt or Kenya or Nigeria');
   
   insert into news_categories values
     (6,'Conflicts','war or soldiers or military or troops');
   
   insert into news_categories values(7,'Finance','profit or loss or wall street');
   insert into news_categories values
     (8,'Technology','software or computer or Oracle 
      or Intel or IBM or Microsoft');
   
   insert into news_categories values
     (9,'Consumer electronics','HDTV or electronics');
   
   insert into news_categories values
     (10,'Latin America','Venezuela or Colombia 
      or Argentina or Brazil or Chile');
   
   insert into news_categories values
     (11,'World Politics','Hugo Chavez or George Bush 
      or Tony Blair or Saddam Hussein or United Nations');
   
   insert into news_categories values
     (12,'US Politics','George Bush or Democrats or Republicans 
      or civil rights or Senate');
   
   insert into news_categories values
     (13,'Astronomy','Jupiter or Earth or star or planet or Orion 
      or Venus or Mercury or Mars or Milky Way 
      or Telescope or astronomer 
      or NASA or astronaut');
   
   insert into news_categories values
     (14,'Paleontology','fossils or scientist 
      or paleontologist or dinosaur or Nature');
   
   insert into news_categories values
     (15,'Health','stem cells or embryo or health or medical
      or medicine or World Health Organization 
      or virus or centers for disease control or vaccination');
   
   insert into news_categories values
     (16,'Natural Disasters','earthquake or hurricane or tornado');
   
   insert into news_categories values
     (17,'Law','Supreme Court or legislation');
   
   insert into news_categories values
     (18,'Music News','piracy or anti-piracy 
      or Recording Industry Association of America 
      or copyright or copy-protection or CDs 
      or music or artist or song');
   
   commit;

4. Create the CTXRULE index on the news_categories query column.

   create index news_cat_idx on news_categories(query)
   indextype is ctxsys.ctxrule;

5. To classify the documents, use the CLASSIFIER.THIS PL/SQL procedure (a simple procedure designed for this example).

   The procedure scrolls through the news_table, matches each document to a category, and writes the categorized results into the news_id_cat table.

   create or replace package classifier as procedure this;end;/
   
   show errors
   
   create or replace package body classifier as
   
    procedure this
    is
     v_document    clob;
     v_item        number;
     v_doc         number;
    begin
   
     for doc in (select tk, text from news_table)
        loop
           v_document := doc.text;
           v_item := 0;
           v_doc  := doc.tk;
           for c in (select queryid, category from news_categories
                where matches(query, v_document) > 0 )
             loop
               v_item := v_item + 1;
               insert into news_id_cat values (doc.tk,c.queryid);
             end loop;
      end loop;
   
    end this;
   
   end;
   /
   show errors
   exec classifier.this