oracle - 尝试将日期值插入新表时出错(ORA-01858 : a non-numeric character was found where a numeric was expected )

Question

我正在尝试将值插入到表中，但我不断收到错误消息

ORA-01858: a non-numeric character was found where a numeric was expected

这是我的代码:

INSERT INTO PRODUCT_DIM_HIS
(PRODUCTKEY, PRODUCTID, PRODUCTDESCRIPTION, PRODUCTLINEID, PRODUCTLINENAME, EFF_START_DATE, EFF_END_DATE)
SELECT PRODUCT_KEY_SEQ.NEXTVAL, nvl(to_char(p.PRODUCTID), 'Undefined'), nvl(to_char(p.PRODUCTDESCRIPTION), 'Undefined'),
nvl(to_char(p.PRODUCTLINEID), 'Undefined'), nvl(to_char(pl.PRODUCTLINENAME), 'Undefined'), 
nvl(to_date(o.ORDERDATE), 'Undefined'), nvl(to_date(o.FULFILLMENTDATE), 'Undefined')
FROM PRODUCTLINE_T pl, PRODUCT_T p, ORDER_T o, ORDERLINE_T ol
WHERE p.PRODUCTLINEID = pl.PRODUCTLINEID
AND o.ORDERID = ol.ORDERID 
AND ol.PRODUCTID = p.PRODUCTID;

要求的PRODUCT_DIM_HIS CODE:

CREATE TABLE PRODUCT_DIM_HIS
(
    PRODUCTKEY integer NOT NULL,
    PRODUCTID integer,
    PRODUCTDESCRIPTION VARCHAR2(50 BYTE),
    PRODUCTLINEID integer,
    PRODUCTLINENAME VARCHAR2(50 BYTE),
    EFF_START_DATE DATE,
    EFF_END_DATE DATE,
    CONSTRAINT PRODUCT_DIM_HIS_PK PRIMARY KEY (PRODUCTKEY)
);

来自ORDER_T的ORDERDATE和FULLFILlMENTDATE具有DATE数据类型，所以我不明白问题是什么？

@BOB_JARVIS

CREATE TABLE PRODUCT_DIM
(
    PRODUCTKEY integer NOT NULL,
    PRODUCTID integer,
    PRODUCTDESCRIPTION VARCHAR2(50 BYTE),
    PRODUCTLINEID integer,
    PRODUCTLINENAME VARCHAR2(50 BYTE),
    CONSTRAINT PRODUCT_DIM_PK PRIMARY KEY (PRODUCTKEY)
);

INSERT INTO PRODUCT_DIM
(PRODUCTKEY, PRODUCTID, PRODUCTDESCRIPTION, PRODUCTLINEID, PRODUCTLINENAME)
SELECT PRODUCT_KEY_SEQ.NEXTVAL, nvl(to_char(p.PRODUCTID), 'Undefined'), nvl(to_char(p.PRODUCTDESCRIPTION), 'Undefined'),
nvl(to_char(p.PRODUCTLINEID), 'Undefined'), nvl(to_char(pl.PRODUCTLINENAME), 'Undefined')
FROM PRODUCTLINE_T pl, PRODUCT_T p
WHERE p.PRODUCTLINEID = pl.PRODUCTLINEID;

INSERT INTO PRODUCT_DIM
(PRODUCTKEY, PRODUCTID, PRODUCTDESCRIPTION, PRODUCTLINEID, PRODUCTLINENAME)
VALUES (PRODUCT_KEY_SEQ.NEXTVAL, -99, 'Undefined', -99, 'Undefined');

Answer 1

删除NVL()并只保留to_date():

因此，

INSERT INTO PRODUCT_DIM_HIS
(PRODUCTKEY, PRODUCTID, PRODUCTDESCRIPTION, PRODUCTLINEID, PRODUCTLINENAME, EFF_START_DATE, EFF_END_DATE)
SELECT PRODUCT_KEY_SEQ.NEXTVAL, nvl(to_char(p.PRODUCTID), 'Undefined'), nvl(to_char(p.PRODUCTDESCRIPTION), 'Undefined'),
nvl(to_char(p.PRODUCTLINEID), 'Undefined'), nvl(to_char(pl.PRODUCTLINENAME), 'Undefined'), 
to_date(o.ORDERDATE), to_date(o.FULFILLMENTDATE)
FROM PRODUCTLINE_T pl, PRODUCT_T p, ORDER_T o, ORDERLINE_T ol
WHERE p.PRODUCTLINEID = pl.PRODUCTLINEID
AND o.ORDERID = ol.ORDERID 
AND ol.PRODUCTID = p.PRODUCTID;