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]
