Ada beberapa cara utk solve the problem: 1. Coba di depan DATA_SEQUENCE.NEXTVAL tambahkan:HIS, jadi : HIS.DATA_SEQUENCE.NEXTVAL
Pastikan sequence DATA_SEQUENCE bisa diakses oleh user. Kalau belum bisa juga, sbg SYS atau DBA atau user HIS, jalankan: GRANT SELECT ON DATA_SEQUENCE TO PUBLIC; 2. Dengan membuat PUBLIC SYNONYM dari sequence tsb, sbg SYS atau DBA jalankan: CREATE PUBLIC SYNONYM DATA_SEQUENCE FOR HIS.DATA_SEQUENCE; cmiiw, bw --- In [email protected], "Syam Ach. S." <[EMAIL PROTECTED]> wrote: > > Yth. > Saya mempunyai implementasi ORACLE DATABASE sbb: > 1. SCHEMA=>HIS_DATA=>TRIGGERS=>DATA_AFTER_IDU status VALID > 2. SCHEMA=>HIS_DATA=>TRIGGERS=>DATA_BEFORE_IDU status INVALID > 3. SCHEMA=>HIS_DATA=>TRIGGERS=>JOB_DATA_BEFORE_I status INVALID > 4. SCHEMA=>HIS_DATA=>PACKAGE BODIES=>TIME_SERIES_MASK status INVALID > 5. SCHEMA=>HIS=>SEQUENCES=>DATA_SEQUENCE dependent on HIS.IMPORT_UNIT_UNIT_BEFORE_I, HIS.JOB_TS_BEFORE_I, HIS.JOB_TS_BEFORE_IDU, HIS.PARAMETER_AFTER_I and HIS.STATION_AFTER_I > > Muncul ORA-04098 Trigger HIS.JOB_DATA_BEFORE_I > Saya sdh mencoba ALTER TRIGGER [OBJECT] COMPILE, muncul error > JOB_DATA_BEFORE_I, show errors: DATA_SEQUENCE.NEXTVAL must be DECLARED > DATA_BEFORE_IDU, show errors: DATA_SEQUENCE.NEXTVAL, TIME_SERIES_MASK.INSERTTSBASIC, TIME_SERIES_MASK.CALCULATEDERIVEDTS, TIME_SERIES_MASK.ADAPTAVAILABLEINTERVAL must be DECLARED > Mohon bantuan PARA PAKAR ORACLE utk menjadikan INVALID menjadi VALID dan kami sertakan file scriptnya , terima Kasih > > Hormat saya, > SYAM > > > _____________________________________________________________________ _______________ > Get easy, one-click access to your favorites. > Make Yahoo! your homepage. > http://www.yahoo.com/r/hs > ---------- > > tabel JOB_DATA_BEFORE_I > > BEGIN > DECLARE NextPk NUMBER(15); > BEGIN > IF INSERTING THEN > IF :NEW.JOB_DATA_PK IS NULL THEN > SELECT DATA_SEQUENCE.NEXTVAL > INTO NextPk > FROM SYS.DUAL; > :NEW.JOB_DATA_PK := NextPk; > END IF; > END IF; > END; > END JOB_DATA_BEFORE_I; > > Tabel DATA_BEFORE_IDU > > DECLARE > > boolDerivedInsert BOOLEAN := FALSE; > boolDerivedDelete BOOLEAN := FALSE; > boolTSOldAdaptAvailable BOOLEAN := FALSE; > boolTSNewAdaptAvailable BOOLEAN := FALSE; > boolIncAvailableCount BOOLEAN := FALSE; > boolDecAvailableCount BOOLEAN := FALSE; > NextPk NUMBER(15); > lngTSOldBasicNumber NUMBER; > lngTSNewBasicNumber NUMBER; > > BEGIN > > DBMS_OUTPUT.PUT_LINE('BEFORE TRIGGER'); > > IF (INSERTING) THEN > > DBMS_OUTPUT.PUT_LINE(' In INSERTING ' || :NEW.TIME_SERIES_PK); > > IF (:NEW.DATA_PK IS NULL) THEN > SELECT DATA_SEQUENCE.NEXTVAL > INTO NextPk > FROM SYS.DUAL; > :NEW.DATA_PK := NextPk; > END IF; > > boolIncAvailableCount := (:NEW.VERSION = 1); > boolDerivedInsert := (:NEW.VALID_STATUS_PK = -1) AND (:NEW.ACTUAL = -1); > > TIME_SERIES_MASK.InsertTSBasic(:NEW.TIME_SERIES_PK); > boolTSNewAdaptAvailable:= TRUE; > > ELSIF (DELETING) THEN > > DBMS_OUTPUT.PUT_LINE('In DELETING'); > > IF (:OLD.ACTUAL = -1) THEN > RAISE_APPLICATION_ERROR(-20000,'You cannot delete an actual data point'); > END IF; > > -- IF you have 3 versions of a data point, you must do this: > -- VERSION 3 --> ACTUAL = 0 > -- REMOVE V3 > -- REMOVE V2 > -- REMOVE V1 --> decrease the number of data points > > IF (:OLD.VERSION = 1) Then > TIME_SERIES_MASK.InsertTSBasic (:OLD.TIME_SERIES_PK); > boolDecAvailableCount := TRUE; > boolTSOldAdaptAvailable:= TRUE; > END IF; > > ELSIF (UPDATING) THEN > > DBMS_OUTPUT.PUT_LINE(' In UPDATING'); > > IF (:OLD.VERSION <> :NEW.VERSION) THEN > RAISE_APPLICATION_ERROR(-20001,'You cannot modify the version of a data point'); > END IF; > > IF (:OLD.TIME_SERIES_PK = :NEW.TIME_SERIES_PK) THEN > > TIME_SERIES_MASK.InsertTSBasic (:NEW.TIME_SERIES_PK); > boolTSOldAdaptAvailable:= TRUE; > > IF (:OLD.TIME <> :NEW.TIME) OR (:OLD.VALUE <> :NEW.VALUE) OR > (:OLD.VALID_STATUS_PK <> :NEW.VALID_STATUS_PK) OR (:OLD.ACTUAL <> :NEW.ACTUAL) THEN > > boolDerivedDelete := (:OLD.VALID_STATUS_PK = -1) AND (:OLD.ACTUAL = - 1); > boolDerivedInsert := (:NEW.VALID_STATUS_PK = -1) OR (:NEW.ACTUAL = -1); > > END IF; > > ELSE > > TIME_SERIES_MASK.InsertTSBasic (:NEW.TIME_SERIES_PK); > TIME_SERIES_MASK.InsertTSBasic (:OLD.TIME_SERIES_PK); > > boolTSOldAdaptAvailable:= TRUE; > boolTSNewAdaptAvailable:= TRUE; > > boolDerivedDelete := (:OLD.VALID_STATUS_PK = -1) AND (:OLD.ACTUAL = - 1); > boolDerivedInsert := (:NEW.VALID_STATUS_PK = -1) OR (:NEW.ACTUAL = -1); > > -- If there is more than one version from a data point and you change the > -- Time_Series_PK, then the number of data points for the old time series must > -- not be decreased. Only if there is only one Version. > -- Problem: The one version could be for example number 4, if the recent three > -- Versions were deleted. Select Count (*) must be executed. > > IF (:OLD.VERSION = 1) THEN > boolDecAvailableCount :=TRUE; > END IF; > > -- Problem: In the new Time Series, there could already exist a data point with > -- this time --> the Version would have to be found with SELECT MAX(VERION) > -- IF :NEW.ACTUAL = -1 THEN > -- dbl_INC_AVAILABLE_COUNT := 1; > -- END IF; > END IF; > > END IF; > > IF (boolDerivedInsert) THEN > TIME_SERIES_MASK.CalculateDerivedTS (:NEW.TIME_SERIES_PK,'INSERT',:NEW.VALUE,:NEW.TIME); > END IF; > IF (boolTSNewAdaptAvailable) THEN > TIME_SERIES_MASK.AdaptAvailableInterval (:NEW.TIME_SERIES_PK,boolIncAvailableCount,boolDecAvailableCount,:NEW .TIME); > END IF; > > -- IF (boolDerivedDelete) THEN > -- TIME_SERIES_MASK.CalculateDerivedTS (:OLD.TIME_SERIES_PK,'DELETE',:OLD.VALUE,:OLD.TIME); > -- END IF; > IF (boolTSOldAdaptAvailable) THEN > TIME_SERIES_MASK.AdaptAvailableInterval (:OLD.TIME_SERIES_PK,boolIncAvailableCount,boolDecAvailableCount,:OLD .TIME); > END IF; > > END; > > tabel DATA_AFTER_IDU > DECLARE > i NUMBER; > boolUpdate BOOLEAN; > TSBasic DATA_TRIGGER.Type_TSBasic; > TSDerived DATA_TRIGGER.Type_TSDerived; > TSInterval DATA_TRIGGER.Type_TSInterval; > TSBasic_NUM BINARY_INTEGER := 0; > dbl_DATA_AVAILABLE NUMBER:=0; > BEGIN > > --DBMS_OUTPUT.PUT_LINE(' '); > DBMS_OUTPUT.PUT_LINE('AFTER TRIGGER'); > DBMS_OUTPUT.PUT_LINE(' Anzahl TSBasic ' || DATA_TRIGGER.TSBasic_NUM); > DBMS_OUTPUT.PUT_LINE(' Anzahl TSDerived ' || DATA_TRIGGER.TSDerived_NUM); > DBMS_OUTPUT.PUT_LINE(' Anzahl TSInterval ' || DATA_TRIGGER.TSInterval_NUM); > > FOR i IN 1..DATA_TRIGGER.TSInterval_NUM LOOP > > TSInterval:=DATA_TRIGGER.TSInterval(i); > > -- DBMS_OUTPUT.PUT_LINE('TS: ' || DATA_TRIGGER.TSInterval(lng_LOOPINDEX).TIME_SERIES_PK); > DBMS_OUTPUT.PUT_LINE('DATA_RESULT_PK: ' || TSInterval.DATA_RESULT_PK ); > > IF (TSInterval.OPERATION_PK = 1081987) THEN > > -- DBMS_OUTPUT.PUT_LINE('MINIMUM neu eruieren'); > > UPDATE DATA_RESULT > SET VALUE =( SELECT /*+ INDEX(data data_ts_time_actual_vs_i) */ MIN(VALUE) > FROM DATA > WHERE DATA.TIME_SERIES_PK = TSInterval.TIME_SERIES_PK_BASIC > AND TIME >= TSInterval.TIME_FROM > AND TIME < TSInterval.TIME_TO > AND ACTUAL = -1 > AND VALID_STATUS_PK = -1) > WHERE DATA_RESULT_PK = TSInterval.DATA_RESULT_PK; > > ELSIF (TSInterval.OPERATION_PK = 1081988) THEN > > -- DBMS_OUTPUT.PUT_LINE('Maximum neu eruieren'); > -- DBMS_OUTPUT.PUT_LINE (DATA_TRIGGER.V_UpdateTS(i).TIME_FROM); > -- DBMS_OUTPUT.PUT_LINE (DATA_TRIGGER.V_UpdateTS(i).TIME_TO); > > UPDATE DATA_RESULT > SET VALUE =( SELECT /*+ INDEX(data data_ts_time_actual_vs_i) */ MAX(VALUE) > FROM DATA > WHERE DATA.TIME_SERIES_PK = TSInterval.TIME_SERIES_PK_BASIC > AND TIME >= TSInterval.TIME_FROM > AND TIME < TSInterval.TIME_TO > AND ACTUAL = -1 > AND VALID_STATUS_PK = -1) > WHERE DATA_RESULT_PK = TSInterval.DATA_RESULT_PK; > > ELSIF (TSInterval.OPERATION_PK = 1081985) THEN > > UPDATE DATA_RESULT > SET VALUE =( SELECT /*+ INDEX(data data_ts_time_actual_vs_i) */ SUM(VALUE) > FROM DATA > WHERE DATA.TIME_SERIES_PK = TSInterval.TIME_SERIES_PK_BASIC > AND TIME >= TSInterval.TIME_FROM > AND TIME < TSInterval.TIME_TO > AND ACTUAL = -1 > AND VALID_STATUS_PK = -1) > WHERE DATA_RESULT_PK = TSInterval.DATA_RESULT_PK; > > ELSIF (TSInterval.OPERATION_PK = 1081992) THEN > > UPDATE DATA_RESULT > SET VALUE =( SELECT /*+ INDEX(data data_ts_time_actual_vs_i) */ COUNT(*) > FROM DATA > WHERE DATA.TIME_SERIES_PK = TSInterval.TIME_SERIES_PK_BASIC > AND TIME >= TSInterval.TIME_FROM > AND TIME < TSInterval.TIME_TO > AND ACTUAL = -1 > AND VALID_STATUS_PK = -1) > WHERE DATA_RESULT_PK = TSInterval.DATA_RESULT_PK; > > END IF; > END LOOP; > > > FOR i IN 1..DATA_TRIGGER.TSDerived_NUM LOOP > TSDerived:=DATA_TRIGGER.TSDerived(i); > IF (TSDerived.CHANGED = -1) THEN > DBMS_OUTPUT.PUT_LINE(' Adapt Change Interval ' || i || ' - ' || TSDerived.TIME_SERIES_PK); > UPDATE TIME_SERIES > SET DATE_CHANGED_MIN = TSDerived.DATE_CHANGED_MIN, > DATE_CHANGED_MAX = TSDerived.DATE_CHANGED_MAX, > DATE_CHANGED_COUNT = DATE_CHANGED_COUNT + 1, > DATE_CHANGED_LAST = SYSDATE, > CHANGED = -1 > WHERE TIME_SERIES_PK = TSDerived.TIME_SERIES_PK; > END IF; > END LOOP; > > > FOR i IN 1..DATA_TRIGGER.TSBasic_NUM LOOP > TSBasic:=DATA_TRIGGER.TSBasic(i); > TSBasic_NUM:=DATA_TRIGGER.TSBasic_NUM; > > --DBMS_OUTPUT.PUT_LINE(' TSBasic ' || i || ' - ' || TSBasic.TIME_SERIES_PK); > --DBMS_OUTPUT.PUT_LINE(' COUNT : ' || TSBasic.DATE_AVAILABLE_COUNT); > --DBMS_OUTPUT.PUT_LINE(' FROM : ' || TSBasic.DATE_AVAILABLE_FROM); > --DBMS_OUTPUT.PUT_LINE(' TO : ' || TSBasic.DATE_AVAILABLE_TO); > > IF (TSBasic.CHANGED) THEN > --DBMS_OUTPUT.PUT_LINE(' Count ' || TSBasic.TIME_SERIES_PK || ' ' || TSBasic.DATE_AVAILABLE_COUNT ); > > IF (TSBasic.DATE_AVAILABLE_COUNT <> 0) OR (TSBasic.DATE_AVAILABLE_FROM IS NOT NULL) THEN > dbl_DATA_AVAILABLE:=0; > IF (TSBasic.DATE_AVAILABLE_COUNT > 0) THEN > dbl_DATA_AVAILABLE:=-1; > END IF; > > UPDATE TIME_SERIES > SET DATE_AVAILABLE_COUNT = TSBasic.DATE_AVAILABLE_COUNT, > DATA_AVAILABLE = dbl_DATA_AVAILABLE, > DATE_AVAILABLE_FROM = TSBasic.DATE_AVAILABLE_FROM, > DATE_AVAILABLE_TO = TSBasic.DATE_AVAILABLE_TO, > DATE_CHANGED_LAST = SYSDATE > WHERE TIME_SERIES_PK = TSBasic.TIME_SERIES_PK; > END IF; > > IF (TSBasic.DATA_POINT_DELETED = TRUE) THEN > --DBMS_OUTPUT.PUT_LINE(' DP DELETED'); > UPDATE TIME_SERIES > SET DATE_AVAILABLE_FROM = ( SELECT /*+ INDEX(data data_ts_time_actual_vs_i) */ MIN(TIME) > FROM DATA > WHERE TIME_SERIES_PK = TIME_SERIES.TIME_SERIES_PK), > DATE_AVAILABLE_TO = ( SELECT /*+ INDEX(data data_ts_time_actual_vs_i) */ MAX(TIME) > FROM DATA > WHERE TIME_SERIES_PK = TIME_SERIES.TIME_SERIES_PK) > WHERE TIME_SERIES_PK = TSBasic.TIME_SERIES_PK; > END IF; > END IF; > END LOOP; > > DATA_TRIGGER.TSDerived_NUM:=0; > DATA_TRIGGER.TSBasic_NUM:=0; > DATA_TRIGGER.TSInterval_NUM:=0; > DATA_TRIGGER.TSBasic_TSDerived_NUM:=0; > > END; > > [Non-text portions of this message have been removed] >
