How to use Oracle9 from utopia and pdc-lab
******************************************

We have installed Oracle9 on another machine in the PDC Lab. It can be
accessed in a similar way as the Oracle8 installation from either utopia
or pdc-lab. We have created accounts for all students, with user ID equal
to your utopia login, and the initial password set to "p" plus student ID 
number (e.g., "p012345"). Connect by typing  

sqlplus username@sun23      (instead of username@pdc)

NOTE: if you prefer, you can use Oracle8 instead of Oracle9 for the second
project. The only difference in the project is that Oracle9 supports
"contains", but I think "like" under Oracle 8 will be fine. So I would say,
if you are unsure, stay with Oracle8; if you want to learn about text
features in Oracle, try the new version.

Here is an example of the use of "contains". Note that you need to build a
special text index before you can use "contains". Please look online for
more information on this operation.


CREATE TABLE songs (
      ID         NUMBER(10),
      Title      VARCHAR2(50),
      Genre      VARCHAR2(50)
);

INSERT INTO songs (ID, Title, Genre)
    VALUES (1, 'The Preble Mice Go Squeak', 'CHILD');
INSERT INTO songs (ID, Title, Genre)
    VALUES (2, 'Benri The Cat', 'CHILD');
INSERT INTO songs (ID, Title, Genre)
    VALUES (3, 'My Mouse Won't Work Blues', 'COMPUTER ENGINEER');
INSERT INTO songs (ID, Title, Genre)
    VALUES (4, 'My Pen Leaked - Ballad Of The Pocket Protector', 'COMPUTER
ENGINEER');
INSERT INTO songs (ID, Title, Genre)
VALUES (5, 'The Mechanical Pencil - Get The Lead Out', 'HEAVY METAL');
COMMIT;


# now you need to parse the data and build an index
BEGIN CTX_DDL.CREATE_PREFERENCE(
'english_lexer','basic_lexer');

CTX_DDL.SET_ATTRIBUTE(
'english_lexer','index_themes',
'no');
END;
/
CREATE INDEX song_index ON songs(title)
   INDEXTYPE IS CTXSYS.CONTEXT
   PARAMETERS('LEXER english_lexer
     STOPLIST ctxsys.default_stoplist');


# here is a contains query that seems to work 
SELECT score(1), title, genre
FROM songs
WHERE CONTAINS(title, 'mice', 1) > 0;