时间戳

首页 > 解决方案 > ORA-01843: 插入日期时月份无效

sql - ORA-01843: 插入日期时月份无效

问题描述

我正在使用 Apex Oracle 运行将数据/表与现有架构合并的脚本文件。这是完整的脚本文件。每个插入命令都会产生错误，说明not a valid month。在 Alter 命令说明时产生一个错误Column Type incompatible with referenced column type

脚本文件：

--A1_rr_upd.txt file 

--dropping the table if already exists
DROP TABLE RRSTAFF;

--creating new table for RRSTAFF

CREATE TABLE RRSTAFF (
    staff_num CHAR(4) PRIMARY KEY,
    name VARCHAR(20),
    gender CHAR(1),
    date_join DATE,
    date_resign DATE,
    contact_num NUMBER(11),
    address VARCHAR(255)
);

--Adding new hired staff
INSERT INTO RRSTAFF VALUES ('s001','Adrian','M','2021/07/02',null,'60122000000','6 Jalan BU6, Petaling Jaya,Selangor');
INSERT INTO RRSTAFF VALUES ('s002','Jewel','F','2021/07/12',null,'60123000000','2 Jalan PJS2, Sunway,Selangor');
INSERT INTO RRSTAFF VALUES ('s003','Sean','M','2021/07/12',null,'60166000000','100 Sunway South K,Selangor');

--Adding new customer details
INSERT INTO rrcustomer VALUES ('1011','Dr','Brendan');
INSERT INTO rrcustomer VALUES ('1012','Dr','Haya');

--Adding new records into models
INSERT INTO model VALUES ('LGC83','LG C1 83 in OLED 4K TV', '500');
INSERT INTO model VALUES ('LGG77','LG Gallery 77 in OLED 4K TV', '400');
INSERT INTO model VALUES ('SNY43','Sony 43 in X75 4K Ultra HD Android TV', '200');
INSERT INTO model VALUES ('SHA50','Sharp 50 in  Full HD Basic TV ', '80');

--Adding new records into appliance
INSERT INTO appliance VALUES ('2010','LGC83','E',null);
INSERT INTO appliance VALUES ('2011','LGC83','E',null);

--Altering the HIRE table to link it with RRSTAFF using staff_id as Foriegn Key
ALTER TABLE
    hire ADD(
        staff_id VARCHAR(4),
            FOREIGN KEY (staff_id) REFERENCES rrstaff(staff_num)
    );

--Adding new hire records
INSERT INTO hire VALUES ('2010','2021/08/02','1011','2021/08/08','s001');
INSERT INTO hire VALUES ('2010','2021/08/22','1012','2021/08/28','s001');
INSERT INTO hire VALUES ('2011','2021/08/12','1013',null,'s001');

标签： sqloracleoracle-apex

解决方案

在 Oracle 中，您使用ISO 标准 YYYY-MM-DD 格式date的关键字和字符串来表示常量。date例如：

INSERT INTO RRSTAFF
    VALUES ('s001', 'Adrian', 'M', DATE '2021-07-02', null, '60122000000', '6 Jalan BU6, Petaling Jaya,Selangor');

这是一个 db<>fiddle。

推荐阅读