Hi I am a beginner to PL/SQL and I have my PL/SQL code for importing a csv file to my GLOBALTERRORISM Database, this is the final version of my code until now. My whole code is at my github repo: https://github.com/bryanwong69420/PL-SQL.git
My problem is the script output kept giving this error:
Trigger BEFORE_INSERT_GLOBALTERRORISM compiled
LINE/COL ERROR
187/9 PL/SQL: Statement ignored 187/61 PLS-00382: expression is of wrong type Errors: check compiler log
Trigger AFTER_INSERT_GLOBALTERRORISM compiled
Trigger BEFORE_UPDATE_GLOBALTERRORISM compiled
LINE/COL ERROR
207/9 PL/SQL: Statement ignored 207/61 PLS-00382: expression is of wrong type Errors: check compiler log
Trigger AFTER_UPDATE_GLOBALTERRORISM compiled
Trigger BEFORE_DELETE_GLOBALTERRORISM compiled
Previously, I thought it might have been because I have "case" for checking whether the number of day for the month is correct. So the code was like this:
-- Validate day of the month
CASE
WHEN :NEW.IMONTH IN (1, 3, 5, 7, 8, 10, 12) THEN -- Months with 31 days
IF :NEW.IDAY < 1 OR :NEW.IDAY > 31 THEN
RAISE_APPLICATION_ERROR(-20003, 'Day must be between 1 and 31 for the given month.');
END IF;
WHEN :NEW.IMONTH IN (4, 6, 9, 11) THEN -- Months with 30 days
IF :NEW.IDAY < 1 OR :NEW.IDAY > 30 THEN
RAISE_APPLICATION_ERROR(-20004, 'Day must be between 1 and 30 for the given month.');
END IF;
WHEN :NEW.IMONTH = 2 THEN -- February, handle leap year
IF (:NEW.IYEAR MOD 4 = 0 AND :NEW.IYEAR MOD 100 <> 0) OR (:NEW.IYEAR MOD 400 = 0) THEN
IF :NEW.IDAY < 1 OR :NEW.IDAY > 29 THEN
RAISE_APPLICATION_ERROR(-20005, 'Day must be between 1 and 29 for February in a leap year.');
END IF;
ELSE
IF :NEW.IDAY < 1 OR :NEW.IDAY > 28 THEN
RAISE_APPLICATION_ERROR(-20006, 'Day (IDAY) must be between 1 and 28 for February in a non-leap year.');
END IF;
END IF;
END CASE;
I changed it to this:
-- Validate day of the month
IF :NEW.IMONTH IN (1, 3, 5, 7, 8, 10, 12) THEN -- Months with 31 days
IF :NEW.IDAY < 1 OR :NEW.IDAY > 31 THEN
RAISE_APPLICATION_ERROR(-20003, 'Day must be between 1 and 31 for the given month.');
END IF;
END IF;
IF :NEW.IMONTH IN (4, 6, 9, 11) THEN -- Months with 30 days
IF :NEW.IDAY < 1 OR :NEW.IDAY > 30 THEN
RAISE_APPLICATION_ERROR(-20004, 'Day must be between 1 and 30 for the given month.');
END IF;
END IF;
IF :NEW.IMONTH = 2 THEN -- February, handle leap year
IF (:NEW.IYEAR MOD 4 = 0 AND :NEW.IYEAR MOD 100 <> 0) OR (:NEW.IYEAR MOD 400 = 0) THEN
IF :NEW.IDAY < 1 OR :NEW.IDAY > 29 THEN
RAISE_APPLICATION_ERROR(-20005, 'Day must be between 1 and 29 for February in a leap year.');
END IF;
ELSE
IF :NEW.IDAY < 1 OR :NEW.IDAY > 28 THEN
RAISE_APPLICATION_ERROR(-20006, 'Day (IDAY) must be between 1 and 28 for February in a non-leap year.');
END IF;
END IF;
END IF;
But nothing changed. I tried to delete this to see if this code snippet is the problem and nothing changed again...
SELECT COUNT(*) s
INTO v_count
FROM GLOBALTERRORISM
WHERE IYEAR = :NEW.IYEAR
AND IMONTH = :NEW.IMONTH
AND IDAY = :NEW.IDAY;
-- Generate the EVENTID based on IYEAR, IMONTH, IDAY, and the occurrence count
:NEW.EVENTID := TO_CHAR(:NEW.IYEAR, 'FM0000') ||
TO_CHAR(:NEW.IMONTH, 'FM00') ||
TO_CHAR(:NEW.IDAY, 'FM00') ||
TO_CHAR(v_count + 1, 'FM0000');