无效的数字格式模型 oracle 错误插入一个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16859508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
invalid number format model oracle error inserting a char
提问by indianapolymath
I am trying to create an Oracle stored procedure that simply inserts a row into a table. There is a column in the table (MEAS_IND) that is type char(1 byte) and cannot be null. When I try to insert any one character into the column/table I get the error:
我正在尝试创建一个简单地将一行插入到表中的 Oracle 存储过程。表 (MEAS_IND) 中有一个类型为 char（1 字节）且不能为空的列。当我尝试在列/表中插入任何一个字符时，出现错误：
ORA-01481: invalid number format model
ORA-06512: at "IFDC.PKG_FIELD_ASSISTANT", line 326
ORA-06512: at line 3
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
*Action: Consult your manual.
I have tried inserting ' ', 'N' and to_char(' '). There are other columns that cannot be null and seem to work fine. When I comment out MEAS_IND I get the error that it cannot be null, so I am assuming that the other columns are fine. Here is the code:
我试过插入 ' '、'N' 和 to_char(' ')。还有其他列不能为空并且似乎工作正常。当我注释掉 MEAS_IND 时，我得到它不能为空的错误，所以我假设其他列没问题。这是代码：
INSERT INTO myTable
(
cont_id
, cms_uid
, dwr_dt
, prj_nbr
, ln_itm_nbr
, loc_seq_nbr
, loc_instld
, vend_id
, rpt_qty
, itm_cd
, last_modfd_uid
, last_modfd_dt
, catg_nbr
, rmrks_id, plan_pg_ref_nbr, ref_doc, fr_sta_itm, fr_sta_dstnc, fr_sta_offst_t, fr_sta_offst_dstnc
, to_sta_itm, to_sta_dstnc, to_sta_offst_t, to_sta_offst_dstnc, meas_ind
)
VALUES
(
pContractId
, pUSER_ID
, pDRW_DATE
, pProject_num
, pPLN
, vMaxLocSeq
, pLocation
, pVend_id
, pAmount
, pItemCode
, pUSER_ID
, to_number(sysdate, 'YYYYMMDD')
, vCatigoryNumber
, ' ', ' ', ' ', ' ', 0, ' ', 0
, ' ', 0, ' ', 0, ' '
) ;
Thanks in advance
提前致谢
回答by Ed Gibbs
Instead of to_number(sysdate, 'YYYYMMDD'), do this:
而不是to_number(sysdate, 'YYYYMMDD')，这样做：
to_number(to_char(sysdate, 'YYYYMMDD'))
Better yet, if it's not too late in the project then change the last_modfd_dtcolumn to a DATEtype. The DATEtype will include the date andthe time of day (hours, minutes, seconds but no fractional seconds). If you just want the date without the time, set it as trunc(sysdate).
更好的是，如果项目还不算太晚，则将last_modfd_dt列更改为DATE类型。该DATE类型将包括日期和时间（小时、分钟、秒，但没有小数秒）。如果您只想要没有时间的日期，请将其设置为trunc(sysdate).
