Oracle Tip: Ignoring case and diacritic differences in an Oracle search.

http://forums.oracle.com/forums/thread.jspa?threadID=14970

Instead of using the SQL UPPER and LOWER functions to perform case-insensitive queries, an alternative approach is to utilize the linguistic sort GENERIC_BASELETTER. GENERIC_BASELETTER groups all characters together based on their base letter values, this is achieved by ignoring their case and the diacritic differences.
Here is an example of a GENERIC_BASELETTER query.

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

SELECT * FROM PRODUCT
WHERE PRODUCT_NAME = database;

All other operators, such as LIKE, perform comparisons in binary mode only. i.e. they do not honor the NLS_SORT value. This has now been be enhanced in 10gR2.

See also:
Diacritics in like clause, Globalization support
http://dbaforums.org/oracle/index.php?showtopic=915

Diacritics
http://en.wikipedia.org/wiki/Diacritic

Oracle® Database Globalization Support Guide
http://download.oracle.com/docs/cd/B14117_01/server.101/b10749/ch5lingsort.htm#i1006463

Oracle NLS_LOWER
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions098.htm#i78373

Oracle Replace
http://www.psoug.org/reference/translate_replace.html

SELECT translate('árvíztűrőtükörfúrógép','áéíóöőúüű','aeiooouuu') without FROM dual

CREATE OR REPLACE TRIGGER USER_AI_PK
   BEFORE INSERT
   ON USER
   FOR EACH ROW
DECLARE
   last_Sequence NUMBER;
   last_InsertID NUMBER;
BEGIN
   IF (:NEW.id IS NULL OR :NEW.id = 0) THEN
      SELECT USER_seq.NEXTVAL INTO :NEW.id FROM DUAL;
   ELSE
      SELECT NVL(Last_Number, 0) INTO last_Sequence
        FROM User_Sequences
       WHERE Sequence_Name = 'USER_seq';
      SELECT :NEW.id INTO last_InsertID FROM DUAL;
      WHILE (last_InsertID > last_Sequence) LOOP
         SELECT USER_seq.NEXTVAL INTO last_Sequence FROM DUAL;
      END LOOP;
   END IF;
END;