migrate from PostgreSQL to Oracle

Hi all,

I'm new to Oracle, I'm trying to migrate from PostgreSQL to Oracle.

I have this table in PostgreSQL that I would like to convert to Oracle.
There are a couple of errors here:

1) I can't create the CHECK constraints on date_start and date_end, because Oracle doesn't allow this syntax.
Is there an work around for this?

2) I would like to have the last_modify column updated, every time someone update the row. In Pg I had the pgplsql update_last_modify() and the reg_on_update_set_last_modify trigger, but I can't transalte them to PL/PSQL.

3) I would like to create an index on rows having type column in 'A' or 'P' but 'C'.

Could you please give me some help?

CREATE TABLE reg (
  code            VARCHAR2(10) PRIMARY KEY,
  name            VARCHAR2(100),
  type            CHAR CHECK (type IN ('A','C','P'),
  date_start      DATE CHECK (date_start <= CURRENT_DATE),
  date_end        DATE,
  bdn_id          NUMBER,
  bdn_cod_reg     VARCHAR2(10),
  last_modify     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT reg_date_end_check CHECK (date_end BETWEEN date_start AND CURRENT_DATE)
);


CREATE FUNCTION update_last_modify() RETURNS trigger
   AS
$$
   BEGIN
       NEW.last_modify := CURRENT_TIMESTAMP;
       RETURN NEW;
   END;
$$
   LANGUAGE plpgsql;


CREATE TRIGGER reg_on_update_set_last_modify
    BEFORE UPDATE ON reg
    FOR EACH ROW EXECUTE PROCEDURE update_last_modify();

CREATE UNIQUE INDEX uniq_name on reg (name, type) where type = 'A' and typo ='P';


j

--
http://www.freelists.org/webpage/oracle-l


Other related posts: