错误代码[20001];ORA-2001:ERROR-ORA-01722:编号ORA-06512无效

Question

我想向表中插入数据。所有列中有两列为日期类型。但我明白了错误代码[20001]；ORA-2001：ERROR-ORA-01722：无效编号ORA-06512

这是我的存储过程

PROCEDURE PR_SP_FAHMI (P_T_TABLE_UPLD_EXCEL IN PARAMS.EXCEL)
is 
P_LOGID VARCHAR2(255);
BEGIN
BEGIN
INSERT INTO 
PARAMS.EMPTY
SELECT 
C.PARA_OBJT_GROUP            ,
C.PARA_OBJT_CODE             ,
C.PARA_PROD_MATRIX_ID        ,
C.PARA_PROD_CHANNEL_ID       ,
C.PARA_PROD_SALES_GROUP_ID   ,
C.PARA_CUST_GROUP            ,
C.PARA_SLS_THROUGH_ID        ,
C.ACTIVE                     ,
C.LOGID                      ,
TO_CHAR (C.CREATED_DATE,'dd/MM/yyyy HH24:MI:SS')  ,
C.CREATED_BY                 ,
TO_CHAR (C.MODIFIED_DATE,'dd/MM/yyyy HH24:MI:SS')              ,
C.MODIFIED_BY                ,
C.PARA_APPL_TAG
FROM TABLE(P_T_TABLE_UPLD_EXCEL) C;
EXCEPTION
--  WHEN NO_DATA_FOUND THEN
WHEN DUP_VAL_ON_INDEX THEN
NULL;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'ERROR-' || SQLERRM);
END;
END PR_SP_FAHMI;

这是我的类型：

CREATE OR REPLACE TYPE EXCEL AS TABLE OF PARAMS.T_OBJECT_FROM_EXCEL

这是我的目标：

CREATE OR REPLACE TYPE "T_OBJECT_FROM_EXCEL" FORCE AS OBJECT (
para_objt_group            VARCHAR2(3),
para_objt_code             VARCHAR2(3),
para_prod_matrix_id        VARCHAR2(5),
para_prod_channel_id       VARCHAR2(5),
para_prod_sales_group_id   VARCHAR2(5),
para_cust_group            VARCHAR2(3),
para_sls_through_id        VARCHAR2(2),
active                     NUMBER(1),
logid                      NUMBER,
created_date               VARCHAR2(255),
created_by                 VARCHAR(20),
modified_date              VARCHAR2(255),
modified_by                VARCHAR(20),
para_appl_tag              VARCHAR(8)
)

Answer 1

类型中的C.MODIFIED_DATE和C.CREATED_DATE是varchar2，要将其插入某个date列，需要将其转换为日期，如下所示：

TO_DATE (C.MODIFIED_DATE,'dd/MM/yyyy HH24:MI:SS')
-- same for C.CREATED_DATE

确保上面表达式中使用的日期格式与类型中的字符串(C.MODIFIED_DATE(格式匹配。