I am receiving ORA-01843 when I run the Insert command below. Is there something wrong with my code? And got the below error messages:
SQL Error:
ORA-01843: not a valid month
ORA-06512: at "MDSYS.SUPERHEROES_AUDIT", line 8
ORA-04088: error during execution of trigger 'MDSYS.SUPERHEROES_AUDIT'
01843. 00000 - "not a valid month"
What seems to be wrong with my declaration? Thank you.
/* TABLE CREATED */
CREATE TABLE EVENTLOGS(
Eventlog_id Number(3,0) NOT NULL,
User_name Varchar2(20),
Date_done Date,
Action_done Varchar2(50),
CONSTRAINT PK_EVENTLOGS PRIMARY KEY (Eventlog_id));
/* SEQUENCE CREATED */
CREATE SEQUENCE EVENTLOG_ID_SEQ
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCYCLE
CACHE 10;
/* TRIGGER CREATED */
CREATE OR REPLACE TRIGGER SUPERHEROES_AUDIT
AFTER INSERT OR DELETE OR UPDATE ON SUPERHEROES
FOR EACH ROW
ENABLE
DECLARE
V_LOGID NUMBER;
V_USER VARCHAR(30);
V_DATE VARCHAR(30);
BEGIN
SELECT EVENTLOG_ID_SEQ.NEXTVAL, USER, TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') INTO V_LOGID, V_USER, V_DATE FROM DUAL;
IF INSERTING THEN
INSERT INTO EVENTLOGS(Eventlog_id, User_name, Date_done, Action_done)
VALUES (V_LOGID, V_USER, V_DATE, 'INSERT');
END IF;
END;
/
INSERT INTO SUPERHEROES VALUES ('TEST');
Your column in the table is defined as
Date_done Date,
You are storing the date into a VARCHAR2
variable
V_DATE VARCHAR(30);
....
SELECT EVENTLOG_ID_SEQ.NEXTVAL, USER,
TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') INTO V_LOGID, V_USER, V_DATE
...
And you are inserting this variable into the DATE
column.
...
VALUES (V_LOGID, V_USER, V_DATE, 'INSERT');
Keep datatype
consistent to avoid confusion.