SQLize | PHPize | SQLtest

CREATE TABLE HR.ACT_TRACK_PT ( TRACK_ID INTEGER NOT NULL, REQUEST_ID INTEGER, CO_ID INTEGER, DN_NUM VARCHAR2(50 BYTE), SNDES VARCHAR2(10 BYTE), FUDES VARCHAR2(50 BYTE), FU_ID INTEGER, MSG_ID INTEGER, MSGCODE INTEGER, ACT_STATUS VARCHAR2(1 BYTE), ACT_REMARK VARCHAR2(250 BYTE), ENTRY_DATE DATE DEFAULT sysdate--, --SUPPLEMENTAL LOG GROUP GGS_ACT_TRACK_275729 (TRACK_ID) ALWAYS )TABLESPACE DATA PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) LOGGING PARTITION BY RANGE (ENTRY_DATE) ( PARTITION ACT_TRACK_PT_202403 VALUES LESS THAN (DATE '2024-04-01'), PARTITION ACT_TRACK_PT_202404 VALUES LESS THAN (DATE '2024-05-01'), PARTITION ACT_TRACK_PT_202405 VALUES LESS THAN (DATE '2024-06-01'), PARTITION p_max VALUES LESS THAN (MAXVALUE) ) NOCACHE; CREATE UNIQUE INDEX HR.PK_ACT_TRACK_PT ON HR.ACT_TRACK_PT (TRACK_ID) LOGGING TABLESPACE DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ); ALTER TABLE HR.ACT_TRACK_PT ADD ( CONSTRAINT PK_ACT_TRACK_PT PRIMARY KEY (TRACK_ID) USING INDEX HR.PK_ACT_TRACK_PT ENABLE VALIDATE); CREATE INDEX HR.IDX_TRACK_COID_PT ON HR.ACT_TRACK_PT (CO_ID) LOGGING TABLESPACE DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ); CREATE INDEX HR.IDX_TRACK_DNNUM_PT ON HR.ACT_TRACK_PT (DN_NUM) LOGGING TABLESPACE DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ); CREATE INDEX HR.IDX_TRACK_ENTRYDATE_PT ON HR.ACT_TRACK_PT (ENTRY_DATE) LOGGING TABLESPACE DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ); CREATE INDEX HR.IDX_TRACK_MSGID_PT ON HR.ACT_TRACK_PT (MSG_ID) LOGGING TABLESPACE DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ); GRANT SELECT ON HR.ACT_TRACK_PT TO BISSAY; GRANT SELECT ON HR.ACT_TRACK_PT TO BSCS_OPS; GRANT SELECT ON HR.ACT_TRACK_PT TO CAKH6641; GRANT SELECT ON HR.ACT_TRACK_PT TO DSOU; GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON HR.ACT_TRACK_PT TO DURGAK; GRANT DELETE, INSERT, SELECT, UPDATE ON HR.ACT_TRACK_PT TO HRROLE; GRANT SELECT ON HR.ACT_TRACK_PT TO HR_READ_ONLY; GRANT SELECT ON HR.ACT_TRACK_PT TO HR_READ_ONLYMAY2012; GRANT SELECT ON HR.ACT_TRACK_PT TO NAGIND; GRANT SELECT ON HR.ACT_TRACK_PT TO NAMAMM; GRANT SELECT ON HR.ACT_TRACK_PT TO ROHVAR; GRANT SELECT ON HR.ACT_TRACK_PT TO SHYAMN; GRANT SELECT ON HR.ACT_TRACK_PT TO SITREAD; GRANT SELECT ON HR.ACT_TRACK_PT TO VELVED; GRANT SELECT ON HR.ACT_TRACK_PT TO YOGESH; RENAME ACT_TRACK TO ACT_TRACK_OLD; CREATE OR REPLACE VIEW HR.ACT_TRACK_COMBINED AS SELECT * FROM HR.ACT_TRACK_OLD UNION ALL SELECT * FROM HR.ACT_TRACK_PT; CREATE SYNONYM HR.ACT_TRACK FOR HR.ACT_TRACK_COMBINED; CREATE OR REPLACE TRIGGER HR.TRG_ACT_TRACK_COMBINED INSTEAD OF INSERT OR UPDATE ON HR.ACT_TRACK_COMBINED FOR EACH ROW DECLARE v_sql VARCHAR2(4000); v_set VARCHAR2(4000); BEGIN -- Handle INSERT IF INSERTING THEN INSERT INTO HR.ACT_TRACK_PT ( TRACK_ID, REQUEST_ID, CO_ID, DN_NUM, SNDES, FUDES, FU_ID, MSG_ID, MSGCODE, ACT_STATUS, ACT_REMARK, ENTRY_DATE ) VALUES ( :NEW.TRACK_ID, :NEW.REQUEST_ID, :NEW.CO_ID, :NEW.DN_NUM, :NEW.SNDES, :NEW.FUDES, :NEW.FU_ID, :NEW.MSG_ID, :NEW.MSGCODE, :NEW.ACT_STATUS, :NEW.ACT_REMARK, :NEW.ENTRY_DATE ); -- Handle UPDATE with per-field update ELSIF UPDATING THEN v_set := ''; IF :NEW.REQUEST_ID != :OLD.REQUEST_ID THEN v_set := v_set || 'REQUEST_ID = ''' || :NEW.REQUEST_ID || ''', '; END IF; IF :NEW.CO_ID != :OLD.CO_ID THEN v_set := v_set || 'CO_ID = ''' || :NEW.CO_ID || ''', '; END IF; IF (:NEW.DN_NUM IS NULL AND :OLD.DN_NUM IS NOT NULL) OR (:NEW.DN_NUM IS NOT NULL AND :OLD.DN_NUM IS NULL) OR (:NEW.DN_NUM != :OLD.DN_NUM) THEN v_set := v_set || 'DN_NUM = ''' || :NEW.DN_NUM || ''', '; END IF; IF (:NEW.SNDES IS NULL AND :OLD.SNDES IS NOT NULL) OR (:NEW.SNDES IS NOT NULL AND :OLD.SNDES IS NULL) OR (:NEW.SNDES != :OLD.SNDES) THEN v_set := v_set || 'SNDES = ''' || :NEW.SNDES || ''', '; END IF; IF (:NEW.FUDES IS NULL AND :OLD.FUDES IS NOT NULL) OR (:NEW.FUDES IS NOT NULL AND :OLD.FUDES IS NULL) OR (:NEW.FUDES != :OLD.FUDES) THEN v_set := v_set || 'FUDES = ''' || :NEW.FUDES || ''', '; END IF; IF :NEW.FU_ID != :OLD.FU_ID THEN v_set := v_set || 'FU_ID = ''' || :NEW.FU_ID || ''', '; END IF; IF :NEW.MSG_ID != :OLD.MSG_ID THEN v_set := v_set || 'MSG_ID = ''' || :NEW.MSG_ID || ''', '; END IF; IF :NEW.MSGCODE != :OLD.MSGCODE THEN v_set := v_set || 'MSGCODE = ''' || :NEW.MSGCODE || ''', '; END IF; IF (:NEW.ACT_STATUS IS NULL AND :OLD.ACT_STATUS IS NOT NULL) OR (:NEW.ACT_STATUS IS NOT NULL AND :OLD.ACT_STATUS IS NULL) OR (:NEW.ACT_STATUS != :OLD.ACT_STATUS) THEN v_set := v_set || 'ACT_STATUS = ''' || :NEW.ACT_STATUS || ''', '; END IF; IF (:NEW.ACT_REMARK IS NULL AND :OLD.ACT_REMARK IS NOT NULL) OR (:NEW.ACT_REMARK IS NOT NULL AND :OLD.ACT_REMARK IS NULL) OR (:NEW.ACT_REMARK != :OLD.ACT_REMARK) THEN v_set := v_set || 'ACT_REMARK = ''' || :NEW.ACT_REMARK || ''', '; END IF; IF :NEW.ENTRY_DATE != :OLD.ENTRY_DATE THEN v_set := v_set || 'ENTRY_DATE = TO_DATE(''' || TO_CHAR(:NEW.ENTRY_DATE, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS''), '; END IF; -- Remove trailing comma and space IF v_set IS NOT NULL THEN v_set := RTRIM(v_set, ', '); v_sql := 'UPDATE HR.ACT_TRACK_PT SET ' || v_set || ' WHERE TRACK_ID = ' || :OLD.TRACK_ID; EXECUTE IMMEDIATE v_sql; END IF; END IF; END; /