INSTALL_TABLES_FOR_LOCALES.sql

-- Basic locales
CREATE TABLE UTL$LOCALES
(
  ID_LOCALE                        CHAR(5 BYTE)          NOT NULL,
  LANG                             CHAR(2 BYTE)          NULL,
  TERRITORY                        CHAR(2 BYTE)          NULL,
  DESCRIPTION                      VARCHAR2(200 BYTE)    NULL
)
/
CREATE UNIQUE INDEX UTL$LOCALES_PK
  ON UTL$LOCALES
  (
   ID_LOCALE
  )
/
ALTER TABLE UTL$LOCALES ADD(
  CONSTRAINT UTL$LOCALES_PK PRIMARY KEY(ID_LOCALE)
    NOT DEFERRABLE INITIALLY IMMEDIATE USING INDEX UTL$LOCALES_PK
    ENABLE VALIDATE)
/
CREATE OR REPLACE TRIGGER SET_UTL$LOCALES
  BEFORE INSERT
  ON UTL$LOCALES FOR EACH ROW
BEGIN
    :NEW.ID_LOCALE := :NEW.LANG || (CASE WHEN :NEW.TERRITORY IS NOT NULL THEN '_' || :NEW.TERRITORY ELSE '' END);
END;
/

COMMENT ON TABLE UTL$LOCALES IS 'Locales data'
/

-- Messages
CREATE TABLE UTL$LOCALE_MESSAGES
(
  ID_MESSAGE                       VARCHAR2(100 BYTE)    NOT NULL,
  ID_LOCALE                        CHAR(5 BYTE)          NOT NULL,
  HAS_CLOB                         CHAR(1 BYTE)          DEFAULT 'N' NULL,
  MESSAGE                          VARCHAR2(4000 BYTE)   NULL,
  CLOB_MESSAGE                     CLOB                  NULL
)
/
CREATE UNIQUE INDEX UTL$LOCALE_MESSAGES_PK
  ON UTL$LOCALE_MESSAGES
  (
   ID_MESSAGE,
   ID_LOCALE
  )
/
ALTER TABLE UTL$LOCALE_MESSAGES ADD(
  CONSTRAINT UTL$LOCALE_MESSAGES_PK PRIMARY KEY(ID_MESSAGE, ID_LOCALE)
    NOT DEFERRABLE
    INITIALLY IMMEDIATE
    USING INDEX UTL$LOCALE_MESSAGES_PK
    ENABLE VALIDATE )
/

COMMENT ON TABLE UTL$LOCALE_MESSAGES IS 'Messages for locales'
/