Rem Rem Copyright (c) 2000, 2008, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem WB_RT_AUDIT.SQL Rem FUNCTION Rem Create OWB runtime auditing objects Rem NOTES Rem - This file can be tested using SQL*Plus. Rem - OWB developers must make sure this file is also runnable Rem using Runtime assistant. Rem Rem MODIFIED Rem xulu 03/28/08 - Rem tzrose 01/16/08 - Rem nbaldwin 09/13/06 - XbranchMerge nbaldwin_tokyo_cc_security from main Rem nbaldwin 06/21/06 - Rem tzrose 10/13/05 - Rem tzrose 06/09/05 - Rem prichens 01/25/05 - bug #4038166 REM T. Rose November 2003 - Add WB_RT_AUDIT_STRUCT plus changes to support metadata info REM G. Watters 03/21/2002 - Based on WBRTAUDIT.SQL Rem Yu.Gong 04/06/2001 - Add "using index tablespace" for constraints. REM S.Nanjundeswaran 12/12/2000 - Updated for new runtime for 3i Rem Yu.Gong 12/06/2000 - Created. Rem -------------- -- TABLES -- -------------- CREATE TABLE OWB$WB_RT_AUDIT ( RTA_IID NUMBER(22) NOT NULL, RTE_ID NUMBER(22), RTA_MAPID NUMBER(22), RTA_LOB_UOID VARCHAR2(255) NOT NULL, RTA_LOB_NAME VARCHAR2(80), RTA_TYPE VARCHAR2(30), RTA_PURGE_GROUP VARCHAR2(80), RTA_PARENT NUMBER(22), RTA_DISCARDED NUMBER(10), RTA_SELECT NUMBER(10), RTA_INSERT NUMBER(10), RTA_UPDATE NUMBER(10), RTA_DELETE NUMBER(10), RTA_MERGE NUMBER(10), RTA_PRIMARY_SOURCE VARCHAR2(2000), RTA_PRIMARY_SOURCE_UOID VARCHAR2(2000), RTA_PRIMARY_TARGET VARCHAR2(2000), RTA_PRIMARY_TARGET_UOID VARCHAR2(2000), RTA_ERRORS NUMBER(10), RTA_CORRECTIONS NUMBER(10), RTA_PHYSICAL_NAME VARCHAR2(1024), RTA_DATE DATE, RTA_INFO VARCHAR2(2000), RTA_ELAPSE NUMBER(10), RTA_LOAD_DATE VARCHAR2(30), RTA_LOAD_TIME VARCHAR2(30), RTA_FKS_OK VARCHAR2(1), RTA_STATUS NUMBER(10), RTA_LOGICAL_ERRORS NUMBER(10), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_FEEDBACK ( RTF_IID NUMBER(22) NOT NULL, RTA_IID NUMBER(22) NOT NULL, RTF_STEP NUMBER(22), RTE_ROWKEY NUMBER(22) NOT NULL, RTE_STATUS VARCHAR2(30) NOT NULL, RTF_TABLE_NAME VARCHAR2(2000) NOT NULL, RTF_ROLE VARCHAR2(30) NOT NULL, RTF_ACTION VARCHAR2(30) NOT NULL, RTE_ROWID ROWID, RTF_KEY01 VARCHAR2(80), RTF_KEY02 VARCHAR2(80), RTF_KEY03 VARCHAR2(80), RTF_KEY04 VARCHAR2(80), RTF_KEY05 VARCHAR2(80), RTF_KEY06 VARCHAR2(80), RTF_KEY07 VARCHAR2(80), RTF_KEY08 VARCHAR2(80), RTF_KEY09 VARCHAR2(80), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_ERRORS ( RTE_IID NUMBER(22) NOT NULL, RTA_IID NUMBER(22) NOT NULL, RTM_ID NUMBER(22), RTA_STEP NUMBER(22), RTD_IID NUMBER(22), RTO_IID NUMBER(22), RTE_ROWKEY NUMBER(22) NOT NULL, RTE_ROWID ROWID, RTE_DEST_TABLE VARCHAR2(2000) NOT NULL, RTE_DEST_COLUMN VARCHAR2(80), RTE_VALUE VARCHAR2(2000), RTE_CORRECTION VARCHAR2(2000), RTE_STATEMENT VARCHAR2(2000), RTE_SQLERR NUMBER(10), RTE_SQLERRM VARCHAR2(2000), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_ERROR_ROWS ( RTR_IID NUMBER(22) NOT NULL, RTE_IID NUMBER(22) NOT NULL, RTE_ROWKEY NUMBER(22) NOT NULL, RTS_SEQ NUMBER(10) NOT NULL, CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_OPERATOR ( RTO_IID NUMBER(22) NOT NULL, RTA_IID NUMBER(22), RTO_NAME VARCHAR2(80) NOT NULL, RTO_STATUS NUMBER(10) NOT NULL, RTO_INPUT NUMBER(10), RTO_OUTPUT NUMBER(10), RTO_ERRORS NUMBER(10), RTO_EXPRESSION VARCHAR2(4000), RTO_ELAPSE NUMBER(10), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_AUDIT_STRUCT ( RTT_IID NUMBER(22) NOT NULL, RTD_IID NUMBER(22) NOT NULL, RTT_PARENT_OPERATOR_UOID VARCHAR2(32) NOT NULL, RTT_PARENT_OBJECT_UOID VARCHAR2(32) NOT NULL, RTT_PARENT_OBJECT_TYPE VARCHAR2(30), RTT_PARENT_OBJECT_LOC_UOID VARCHAR2(32), RTT_PARENT_OBJECT_NAME VARCHAR2(80), RTT_OBJECT_UOID VARCHAR2(32) NOT NULL, RTT_OBJECT_TYPE VARCHAR2(30), RTT_OBJECT_LOC_UOID VARCHAR2(32), RTT_OBJECT_NAME VARCHAR2(80), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_ERROR_SOURCES ( RTS_IID NUMBER(22) NOT NULL, RTS_SEQ NUMBER(10) NOT NULL, RTA_IID NUMBER(22) NOT NULL, RTS_STEP NUMBER(22), RTS_SOURCE_TABLE VARCHAR2(2000) NOT NULL, RTS_SOURCE_COLUMN VARCHAR2(80), RTE_ROWKEY NUMBER(22) NOT NULL, RTS_INSTANCE NUMBER(22) DEFAULT 1 NOT NULL, RTS_VALUE VARCHAR2(2000), RTS_ROLE VARCHAR2(30), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_AUDIT_AMOUNTS ( RTM_IID NUMBER(22) NOT NULL, RTA_IID NUMBER(22) NOT NULL, RTM_STEP NUMBER(22) NOT NULL, RTM_TYPE VARCHAR2(30) NOT NULL, RTM_TNAME VARCHAR2(2000) NOT NULL, RTM_CNAME VARCHAR2(80) NOT NULL, RTM_SPAMOUNT NUMBER(22,4), RTM_SNAMOUNT NUMBER(22,4), RTM_FPAMOUNT NUMBER(22,4), RTM_FNAMOUNT NUMBER(22,4), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; CREATE TABLE OWB$WB_RT_AUDIT_DETAIL ( RTD_IID NUMBER(22) NOT NULL, RTA_IID NUMBER(22) NOT NULL, RTO_IID NUMBER(22), RTD_STEP NUMBER(22) NOT NULL, RTD_NAME VARCHAR2(80), RTD_SOURCE VARCHAR2(2000), RTD_SOURCE_UOID VARCHAR2(2000), RTD_TARGET VARCHAR2(2000), RTD_TARGET_UOID VARCHAR2(2000), RTD_STATUS NUMBER(10) NOT NULL, RTD_STATEMENT VARCHAR2(2000), RTD_DISCARDED NUMBER(10), RTD_SELECT NUMBER(10), RTD_INSERT NUMBER(10), RTD_UPDATE NUMBER(10), RTD_DELETE NUMBER(10), RTD_MERGE NUMBER(10), RTD_ERRORS NUMBER(10), RTD_CORRECTIONS NUMBER(10), RTD_INFO VARCHAR2(2000), RTD_ELAPSE NUMBER(10), RTD_WHEN_CLAUSE VARCHAR2(2000), RTD_EXEC_MODE NUMBER(10), RTD_LOGICAL_ERRORS NUMBER(10), CREATED_BY VARCHAR2(30) DEFAULT sys_context('owb_workspace', 'usernotnull') NOT NULL, CREATION_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_UPDATE_DATE DATE DEFAULT SYSDATE, LAST_UPDATED_BY VARCHAR2(30) DEFAULT USER, WORKSPACE_ID NUMBER(22) DEFAULT sys_context('owb_workspace', 'workspaceId') NOT NULL ) ; ALTER TABLE OWB$WB_RT_AUDIT ADD CONSTRAINT PK_RTA PRIMARY KEY ( RTA_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_FEEDBACK ADD CONSTRAINT PK_RTFB PRIMARY KEY ( RTF_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_ERRORS ADD CONSTRAINT PK_RTER PRIMARY KEY ( RTE_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_ERROR_ROWS ADD CONSTRAINT PK_RTR PRIMARY KEY ( RTR_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_OPERATOR ADD CONSTRAINT PK_RTO PRIMARY KEY ( RTO_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_AUDIT_STRUCT ADD CONSTRAINT PK_RTT PRIMARY KEY ( RTT_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_ERROR_SOURCES ADD CONSTRAINT PK_RTS PRIMARY KEY ( RTS_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_AUDIT_AMOUNTS ADD CONSTRAINT PK_RTAM PRIMARY KEY ( RTM_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_AUDIT_DETAIL ADD CONSTRAINT PK_RTD PRIMARY KEY ( RTD_IID ) ENABLE ; ALTER TABLE OWB$WB_RT_AUDIT ADD CONSTRAINT FK_RTA_RTE FOREIGN KEY ( RTE_ID ) REFERENCES OWB$WB_RT_AUDIT_EXECUTIONS ( AUDIT_EXECUTION_ID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_FEEDBACK ADD CONSTRAINT FK_RTFB_RTA FOREIGN KEY ( RTA_IID ) REFERENCES OWB$WB_RT_AUDIT ( RTA_IID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_ERRORS ADD CONSTRAINT FK_RTER_RTA FOREIGN KEY ( RTA_IID ) REFERENCES OWB$WB_RT_AUDIT ( RTA_IID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_ERRORS ADD CONSTRAINT FK_RTER_RTM FOREIGN KEY ( RTM_ID ) REFERENCES OWB$WB_RT_AUDIT_MESSAGES ( AUDIT_MESSAGE_ID ) ENABLE ; ALTER TABLE OWB$WB_RT_ERROR_ROWS ADD CONSTRAINT FK_RTR_RTE FOREIGN KEY ( RTE_IID ) REFERENCES OWB$WB_RT_ERRORS ( RTE_IID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_OPERATOR ADD CONSTRAINT FK_RTO_RTA FOREIGN KEY ( RTA_IID ) REFERENCES OWB$WB_RT_AUDIT ( RTA_IID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_AUDIT_STRUCT ADD CONSTRAINT FK_RTT_RTD FOREIGN KEY ( RTD_IID ) REFERENCES OWB$WB_RT_AUDIT_DETAIL ( RTD_IID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_ERROR_SOURCES ADD CONSTRAINT FK_RTS_RTA FOREIGN KEY ( RTA_IID ) REFERENCES OWB$WB_RT_AUDIT ( RTA_IID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_AUDIT_AMOUNTS ADD CONSTRAINT FK_RTAM_RTA FOREIGN KEY ( RTA_IID ) REFERENCES OWB$WB_RT_AUDIT ( RTA_IID ) ON DELETE CASCADE ENABLE ; ALTER TABLE OWB$WB_RT_AUDIT_DETAIL ADD CONSTRAINT FK_RTD_RTA FOREIGN KEY ( RTA_IID ) REFERENCES OWB$WB_RT_AUDIT ( RTA_IID ) ON DELETE CASCADE ENABLE ; --------------- -- INDEXES -- --------------- CREATE INDEX WB_RT_AUDIT_RTE_ID_IND ON OWB$WB_RT_AUDIT (RTE_ID ASC) TABLESPACE %tndex; CREATE INDEX WB_RT_ERRORS_RTA_IID_IND ON OWB$WB_RT_ERRORS (RTA_IID ASC) TABLESPACE %tndex; CREATE INDEX WB_RT_ERRORS_RTD_IID_IND ON OWB$WB_RT_ERRORS (RTD_IID ASC) TABLESPACE %tndex; CREATE INDEX WB_RT_FEEDBACK_RTA_IID_IND ON OWB$WB_RT_FEEDBACK (RTA_IID ASC) TABLESPACE %tndex; CREATE INDEX WB_RT_AUDIT_STRUCT_RTD_IID_IND ON OWB$WB_RT_AUDIT_STRUCT (RTD_IID ASC) TABLESPACE %tndex; CREATE INDEX WB_RT_AUDIT_STRUCT_PAR_IND ON OWB$WB_RT_AUDIT_STRUCT (RTT_PARENT_OBJECT_UOID ASC, RTT_PARENT_OBJECT_TYPE ASC, RTT_PARENT_OBJECT_LOC_UOID ASC) TABLESPACE %tndex; CREATE INDEX WB_RT_AUDIT_STRUCT_OBJ_IND ON OWB$WB_RT_AUDIT_STRUCT (RTT_OBJECT_UOID ASC, RTT_OBJECT_TYPE ASC, RTT_OBJECT_LOC_UOID ASC) TABLESPACE %tndex; CREATE INDEX WB_RT_AUDIT_DETAIL_RTA_IID_IND ON OWB$WB_RT_AUDIT_DETAIL (RTA_IID ASC) TABLESPACE %tndex; -------------- -- VIEWS -- -------------- CREATE VIEW WB_RT_AUDIT AS SELECT OWB$WB_RT_AUDIT.RTA_IID, OWB$WB_RT_AUDIT.RTE_ID, OWB$WB_RT_AUDIT.RTA_MAPID, OWB$WB_RT_AUDIT.RTA_LOB_UOID, OWB$WB_RT_AUDIT.RTA_LOB_NAME, OWB$WB_RT_AUDIT.RTA_TYPE, OWB$WB_RT_AUDIT.RTA_PURGE_GROUP, OWB$WB_RT_AUDIT.RTA_PARENT, OWB$WB_RT_AUDIT.RTA_DISCARDED, OWB$WB_RT_AUDIT.RTA_SELECT, OWB$WB_RT_AUDIT.RTA_INSERT, OWB$WB_RT_AUDIT.RTA_UPDATE, OWB$WB_RT_AUDIT.RTA_DELETE, OWB$WB_RT_AUDIT.RTA_MERGE, OWB$WB_RT_AUDIT.RTA_PRIMARY_SOURCE, OWB$WB_RT_AUDIT.RTA_PRIMARY_SOURCE_UOID, OWB$WB_RT_AUDIT.RTA_PRIMARY_TARGET, OWB$WB_RT_AUDIT.RTA_PRIMARY_TARGET_UOID, OWB$WB_RT_AUDIT.RTA_ERRORS, OWB$WB_RT_AUDIT.RTA_CORRECTIONS, OWB$WB_RT_AUDIT.RTA_PHYSICAL_NAME, OWB$WB_RT_AUDIT.RTA_DATE, OWB$WB_RT_AUDIT.RTA_INFO, OWB$WB_RT_AUDIT.RTA_ELAPSE, OWB$WB_RT_AUDIT.RTA_LOAD_DATE, OWB$WB_RT_AUDIT.RTA_LOAD_TIME, OWB$WB_RT_AUDIT.RTA_FKS_OK, OWB$WB_RT_AUDIT.RTA_STATUS, OWB$WB_RT_AUDIT.RTA_LOGICAL_ERRORS, OWB$WB_RT_AUDIT.CREATED_BY, OWB$WB_RT_AUDIT.CREATION_DATE, OWB$WB_RT_AUDIT.LAST_UPDATE_DATE, OWB$WB_RT_AUDIT.LAST_UPDATED_BY, OWB$WB_RT_AUDIT.WORKSPACE_ID FROM OWB$WB_RT_AUDIT where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_AUDIT_AMOUNTS AS select RTM_IID,RTA_IID,RTM_STEP,RTM_TYPE,RTM_TNAME,RTM_CNAME,RTM_SPAMOUNT,RTM_SNAMOUNT,RTM_FPAMOUNT,RTM_FNAMOUNT,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_AUDIT_AMOUNTS where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_FEEDBACK AS select RTF_IID,RTA_IID,RTF_STEP,RTE_ROWKEY,RTE_STATUS,RTF_TABLE_NAME,RTF_ROLE,RTF_ACTION,RTE_ROWID,RTF_KEY01,RTF_KEY02,RTF_KEY03,RTF_KEY04,RTF_KEY05,RTF_KEY06,RTF_KEY07,RTF_KEY08,RTF_KEY09,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_FEEDBACK where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_ERRORS AS select RTE_IID,RTA_IID,RTM_ID,RTA_STEP,RTD_IID,RTO_IID,RTE_ROWKEY,RTE_ROWID,RTE_DEST_TABLE,RTE_DEST_COLUMN,RTE_VALUE,RTE_CORRECTION,RTE_STATEMENT,RTE_SQLERR,RTE_SQLERRM,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_ERRORS where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_ERROR_ROWS AS select RTR_IID,RTE_IID,RTE_ROWKEY,RTS_SEQ,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_ERROR_ROWS where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_ERROR_SOURCES AS select RTS_IID,RTS_SEQ,RTA_IID,RTS_STEP,RTS_SOURCE_TABLE,RTS_SOURCE_COLUMN,RTE_ROWKEY,RTS_INSTANCE,RTS_VALUE,RTS_ROLE,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_ERROR_SOURCES where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_OPERATOR AS select RTO_IID,RTA_IID,RTO_NAME,RTO_STATUS,RTO_INPUT,RTO_OUTPUT,RTO_ERRORS,RTO_EXPRESSION,RTO_ELAPSE,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_OPERATOR where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_AUDIT_STRUCT AS select RTT_IID,RTD_IID,RTT_PARENT_OPERATOR_UOID,RTT_PARENT_OBJECT_UOID,RTT_PARENT_OBJECT_TYPE,RTT_PARENT_OBJECT_LOC_UOID,RTT_PARENT_OBJECT_NAME,RTT_OBJECT_UOID,RTT_OBJECT_TYPE,RTT_OBJECT_LOC_UOID,RTT_OBJECT_NAME,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_AUDIT_STRUCT where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; CREATE VIEW WB_RT_AUDIT_DETAIL AS select RTD_IID,RTA_IID,RTO_IID,RTD_STEP,RTD_NAME,RTD_SOURCE,RTD_SOURCE_UOID,RTD_TARGET,RTD_TARGET_UOID,RTD_STATUS,RTD_STATEMENT,RTD_DISCARDED,RTD_SELECT,RTD_INSERT,RTD_UPDATE,RTD_DELETE,RTD_MERGE,RTD_ERRORS,RTD_CORRECTIONS,RTD_INFO,RTD_ELAPSE,RTD_WHEN_CLAUSE,RTD_EXEC_MODE,RTD_LOGICAL_ERRORS,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,WORKSPACE_ID from OWB$WB_RT_AUDIT_DETAIL where (workspace_id = wb_workspace_management.get_workspace_id or workspace_id = 0 or workspace_id is null) and wb_workspace_management.has_system_privilege_int('CONTROL_CENTER_VIEW') != 0; -------------- -- TRIGGERS -- -------------- CREATE OR REPLACE TRIGGER "WB_RT_ERRORS_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_ERRORS FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; END; / ALTER TRIGGER "WB_RT_ERRORS_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_ERROR_SOURCES_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_ERROR_SOURCES FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; END; / ALTER TRIGGER "WB_RT_ERROR_SOURCES_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_ERROR_ROWS_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_ERROR_ROWS FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; END; / ALTER TRIGGER "WB_RT_ERROR_ROWS_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_FEEDBACK_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_FEEDBACK FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; END; / ALTER TRIGGER "WB_RT_FEEDBACK_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_AUDIT_AMOUNTS_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_AUDIT_AMOUNTS FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; END; / ALTER TRIGGER "WB_RT_AUDIT_AMOUNTS_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_AUDIT_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_AUDIT FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; :new.rta_elapse := 0; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; if UPDATING then :new.rta_elapse := TO_NUMBER(:NEW.last_update_date - :OLD.creation_date)*24*60*60; end if; END; / ALTER TRIGGER "WB_RT_AUDIT_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_AUDIT_DETAIL_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_AUDIT_DETAIL FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; :new.rtd_elapse := 0; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; if UPDATING then :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; :new.rtd_elapse := TO_NUMBER(:NEW.last_update_date - :OLD.creation_date)*24*60*60; end if; END; / ALTER TRIGGER "WB_RT_AUDIT_DETAIL_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_AUDIT_STRUCT_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_AUDIT_STRUCT FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; if UPDATING then :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; end if; END; / ALTER TRIGGER "WB_RT_AUDIT_STRUCT_AUDIT" ENABLE; CREATE OR REPLACE TRIGGER "WB_RT_OPERATOR_AUDIT" BEFORE INSERT OR UPDATE ON OWB$WB_RT_OPERATOR FOR EACH ROW DECLARE runuser VARCHAR2(30); BEGIN select username into runuser from user_users; if INSERTING then if :NEW.created_by is null then :NEW.created_by := runuser; end if; if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if; :new.rto_elapse := 0; end if; :NEW.last_updated_by := runuser; :NEW.last_update_date := SYSDATE; if UPDATING then :new.rto_elapse := TO_NUMBER(:NEW.last_update_date - :OLD.creation_date)*24*60*60; end if; END; / ALTER TRIGGER "WB_RT_OPERATOR_AUDIT" ENABLE; ---------------------------------------------------- -- LOCK TABLES -- ---------------------------------------------------- Create Table WB_RT_FEEDBACK_LOCK AS SELECT * FROM DUAL ; Create Table WB_RT_ROWKEY_LOCK AS SELECT * FROM DUAL ; ---------------------------------------------------- -- TEMPORARY TABLE USED FOR READING SHADOW TABLES -- ---------------------------------------------------- create global temporary table wb_rt_temporary_exceptions ( shadow_rowid varchar2(18), audit_run_id number(22), audit_detail_id number(22), error_id number(22), error_object_name varchar2(30), error_reason varchar2(2000), severity varchar2(1), operator_name varchar2(30) ) on commit preserve rows; ---------------------------------------------------- -- TEMPORARY TABLE USED FOR READING TARGET TABLES -- ---------------------------------------------------- create global temporary table wb_rt_temporary_target ( schema_name varchar2(80), table_name varchar2(80), row_id varchar2(18), column_name varchar2(80), column_value varchar2(2000) ) on commit preserve rows; --------------- -- SEQUENCES -- --------------- CREATE SEQUENCE WB_RT_MAP_AUDIT_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_MAP_AUDIT_DETAIL_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_MAP_AUDIT_STRUCT_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_OPERATOR_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_ERRORS_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_ERROR_SOURCES_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_ERROR_ROWS_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_FEEDBACK_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_AUDIT_AMOUNTS_SEQ START WITH 1 INCREMENT BY 1 ORDER; CREATE SEQUENCE WB_RT_ROWKEY_SEQ START WITH 1 INCREMENT BY 1 ORDER;