ORA-1843: 更新记录时月份无效

本教程将介绍ORA-1843: 更新记录时月份无效的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

ORA-1843: 更新记录时月份无效 教程 第1张

问题描述

出于某种目的,我想更新我的日期列.已经存储在列中的值如下所示

18-06-14

更新时如果我不更新任何东西并调试并检查它,它的格式为 2014-06-18T00:00:00

因此,在更新过程中,我收到如下错误.

ORA-1843: 月份无效

下面是我怎么将它作为参数发送来更新它

LAUNCH_DATE = P_LAUNCH_DATE,

P_LAUNCH_DATE 我有更新的值.这给了我上述错误.我该怎么解决.

更新

这是我使用它的整个存储过程.

PROCEDURE INSERT_INTO_RRSOC_MST
  ( 
 P_STORE_CODE IN NVARCHAR2,
 P_STATE IN NVARCHAR2,
 P_CITY IN NVARCHAR2,
 P_SITE_STORE_FORMAT IN NVARCHAR2,
 P_STORE_SITENAME IN NVARCHAR2,
 P_STORE_SITENAME_LANDL_1 IN NVARCHAR2,
 P_STORE_SITENAME_LANDL_2 IN NVARCHAR2,
 P_STORE_ASST_MANAGER_NAME IN NVARCHAR2,
 P_STORE_ASST_MANAGER_MOBNO IN NVARCHAR2,
 P_STORE_MANAGER_NAME IN NVARCHAR2,
 P_MANAGER_MOBNO IN NVARCHAR2,
 P_EMP_NEAREST_STORE IN NVARCHAR2,
 P_EMP_NEAREST_STORE_MOBNO IN NVARCHAR2,
 P_SUPERVISOR_NAME IN NVARCHAR2,
 P_SUPERVISOR_MOBNO IN NVARCHAR2,
 P_SECURITY_SUP_NAME_STORE IN NVARCHAR2,
 P_SECURITY_SUP_MOBNO_STORE IN NVARCHAR2,
 P_NAME_ALIGNED_LPO IN NVARCHAR2,
 P_LPO_MOBILENO IN NVARCHAR2,
 P_ALPM_ALPO_NAME IN NVARCHAR2,
 P_ALPM_ALPO_MOBNO IN NVARCHAR2,
 P_AREA_MANAGER_NAME IN NVARCHAR2,
 P_AREA_MANAGER_MOBNO IN NVARCHAR2,
 P_ZONAL_HEAD_NAME IN NVARCHAR2,
 P_ZONAL_HEAD_NO IN NVARCHAR2,
 P_DVR_IP_ADDRESS IN NVARCHAR2,
 P_SIGNET_IP_ADDRESS IN NVARCHAR2,
 P_NEAREST_POLICE_STN_NAME IN NVARCHAR2,
 P_NEAREST_POLICE_STN_CONTNO IN NVARCHAR2,
 P_NEAREST_HOSP_NAME IN NVARCHAR2,
 P_NEAREST_HOSP_CONTNO IN NVARCHAR2,
 P_NEAREST_FIRE_STN_CONTNAME IN NVARCHAR2,
 P_NEAREST_FIRE_STN_CONTNO IN NVARCHAR2, 
 P_STORE_ADDRESS IN CLOB, 
 P_STORE_SPACE_SQFT IN NUMBER,
 P_LAUNCH_DATE IN DATE,
 P_CST_TIN_NO IN NVARCHAR2,
 P_STORE_EMAILID IN NVARCHAR2,
 P_NO_OF_POS IN NUMBER,
 P_NO_OF_CAMERA IN NUMBER,
 P_DVR_MODEL_GESECURITY IN NVARCHAR2, 
 P_CAMERA_MODEL IN NVARCHAR2,
 P_ALIGNED_LPO_MAILDID IN NVARCHAR2,
 P_FACILTY_TEAMNAME IN NVARCHAR2,
 P_FACILITY_TEAMNO IN NVARCHAR2,
 P_STATE_HEAD_OPS_NAME IN NVARCHAR2,
 P_STATE_HEAD_OPS_NO IN NVARCHAR2,
 P_LPA IN NVARCHAR2,
 P_SLP_STATE_HEAD IN NVARCHAR2, 
 P_SLP_STATE_HEAD_NO IN NVARCHAR2,
 P_CREATED_BY IN NVARCHAR2,
 P_CREATED_DATE IN DATE,
 P_LAST_UPDATED_BY IN NVARCHAR2,
 P_LAST_UPDATED_DATE IN DATE, 
 P_ISACTIVE IN CHAR,
 P_LATITUDE IN NUMBER,
 P_LONGITUDE IN NUMBER, 
 TBLDATA OUT NUMBER
  ) 

  AS

  V_RRSOC_ID NUMBER:=0;

  BEGIN

 SELECT COUNT(RRSOC_ID) INTO V_RRSOC_ID FROM TBL_RRSOC_STORE_INFO WHERE STORE_CODE = P_STORE_CODE;

 IF V_RRSOC_ID > 0 THEN

UPDATE TBL_RRSOC_STORE_INFO 
SETSTATE = P_STATE,
CITY = P_CITY,
SITE_STORE_FORMAT = P_SITE_STORE_FORMAT,
STORE_SITENAME = P_STORE_SITENAME,
STORE_SITENAME_LANDL_1 = P_STORE_SITENAME_LANDL_1,
STORE_SITENAME_LANDL_2 = P_STORE_SITENAME_LANDL_2,
STORE_ASST_MANAGER_NAME = P_STORE_ASST_MANAGER_NAME,
STORE_ASST_MANAGER_MOBNO = P_STORE_ASST_MANAGER_MOBNO,
STORE_MANAGER_NAME = P_STORE_MANAGER_NAME,
MANAGER_MOBNO = P_MANAGER_MOBNO,
EMP_NEAREST_STORE = P_EMP_NEAREST_STORE,
EMP_NEAREST_STORE_MOBNO = P_EMP_NEAREST_STORE_MOBNO,
SUPERVISOR_NAME = P_SUPERVISOR_NAME,
SUPERVISOR_MOBNO = P_SUPERVISOR_MOBNO,
SECURITY_SUP_NAME_STORE = P_SECURITY_SUP_NAME_STORE,
SECURITY_SUP_MOBNO_STORE = P_SECURITY_SUP_MOBNO_STORE,
NAME_ALIGNED_LPO = P_NAME_ALIGNED_LPO,
LPO_MOBILENO = P_LPO_MOBILENO,
ALPM_ALPO_NAME = P_ALPM_ALPO_NAME,
ALPM_ALPO_MOBNO = P_ALPM_ALPO_MOBNO,
AREA_MANAGER_NAME = P_AREA_MANAGER_NAME,
AREA_MANAGER_MOBNO = P_AREA_MANAGER_MOBNO,
ZONAL_HEAD_NAME =P_ZONAL_HEAD_NAME,
ZONAL_HEAD_NO = P_ZONAL_HEAD_NO,
DVR_IP_ADDRESS = P_DVR_IP_ADDRESS,
SIGNET_IP_ADDRESS = P_SIGNET_IP_ADDRESS,
NEAREST_POLICE_STN_NAME = P_NEAREST_POLICE_STN_NAME,
NEAREST_POLICE_STN_CONTNO = P_NEAREST_POLICE_STN_CONTNO,
NEAREST_HOSP_NAME = P_NEAREST_POLICE_STN_NAME,
NEAREST_HOSP_CONTNO = P_NEAREST_HOSP_CONTNO,
NEAREST_FIRE_STN_CONTNAME = P_NEAREST_FIRE_STN_CONTNAME,
NEAREST_FIRE_STN_CONTNO = P_NEAREST_FIRE_STN_CONTNO,
STORE_ADDRESS = P_STORE_ADDRESS,
STORE_SPACE_SQFT = P_STORE_SPACE_SQFT,
  -- LAUNCH_DATE = P_LAUNCH_DATE,
LAUNCH_DATE = TO_DATE(P_LAUNCH_DATE, 'DD-MM-RR'), 
CST_TIN_NO = P_CST_TIN_NO,
STORE_EMAILID = P_STORE_EMAILID,
NO_OF_POS = P_NO_OF_POS,
NO_OF_CAMERA = P_NO_OF_CAMERA,
DVR_MODEL_GESECURITY = P_DVR_MODEL_GESECURITY,
CAMERA_MODEL = P_CAMERA_MODEL,
ALIGNED_LPO_MAILDID = P_ALIGNED_LPO_MAILDID,
FACILTY_TEAMNAME = P_FACILTY_TEAMNAME,
FACILITY_TEAMNO = P_FACILITY_TEAMNO,
STATE_HEAD_OPS_NAME = P_STATE_HEAD_OPS_NAME,
STATE_HEAD_OPS_NO = P_STATE_HEAD_OPS_NO,
LPA = P_LPA,
SLP_STATE_HEAD = P_SLP_STATE_HEAD,
SLP_STATE_HEAD_NO = P_SLP_STATE_HEAD_NO,
CREATED_BY = P_CREATED_BY,
CREATED_DATE = SYSDATE,
LAST_UPDATED_BY = P_LAST_UPDATED_BY,
LAST_UPDATED_DATE = SYSDATE,
ISACTIVE = P_ISACTIVE,
LATITUDE = P_LATITUDE,
LONGITUDE = P_LONGITUDE
WHERE STORE_CODE = P_STORE_CODE;
--RETURNING RRSOC_ID INTO TBLDATA; SELECT RRSOC_ID INTO TBLDATA FROM TBL_RRSOC_STORE_INFO WHERE STORE_CODE = P_STORE_CODE;

 ELSE

 INSERT INTO TBL_RRSOC_STORE_INFO
  (
STORE_CODE,
STATE,CITY,
SITE_STORE_FORMAT,
STORE_SITENAME,
STORE_SITENAME_LANDL_1,
STORE_SITENAME_LANDL_2,
STORE_ASST_MANAGER_NAME,
STORE_ASST_MANAGER_MOBNO,
STORE_MANAGER_NAME,
MANAGER_MOBNO,EMP_NEAREST_STORE,
EMP_NEAREST_STORE_MOBNO, 
SUPERVISOR_NAME,
SUPERVISOR_MOBNO,SECURITY_SUP_NAME_STORE, 
SECURITY_SUP_MOBNO_STORE,
NAME_ALIGNED_LPO,LPO_MOBILENO,
ALPM_ALPO_NAME, 
ALPM_ALPO_MOBNO,
AREA_MANAGER_NAME, 
AREA_MANAGER_MOBNO,
ZONAL_HEAD_NAME,
ZONAL_HEAD_NO,DVR_IP_ADDRESS, 
SIGNET_IP_ADDRESS, 
NEAREST_POLICE_STN_NAME, 
NEAREST_POLICE_STN_CONTNO,
NEAREST_HOSP_NAME,NEAREST_HOSP_CONTNO,
NEAREST_FIRE_STN_CONTNAME,
NEAREST_FIRE_STN_CONTNO,STORE_ADDRESS,
STORE_SPACE_SQFT,
LAUNCH_DATE,CST_TIN_NO,
STORE_EMAILID,
NO_OF_POS, 
NO_OF_CAMERA, 
DVR_MODEL_GESECURITY,CAMERA_MODEL, 
ALIGNED_LPO_MAILDID,
FACILTY_TEAMNAME,
FACILITY_TEAMNO, 
STATE_HEAD_OPS_NAME,
STATE_HEAD_OPS_NO,LPA, 
SLP_STATE_HEAD,SLP_STATE_HEAD_NO,CREATED_BY,
CREATED_DATE, 
LAST_UPDATED_BY, 
LAST_UPDATED_DATE,ISACTIVE,LATITUDE,LONGITUDE 
  )

  VALUES
  (
P_STORE_CODE,
P_STATE,
P_CITY,
P_SITE_STORE_FORMAT,
P_STORE_SITENAME,
P_STORE_SITENAME_LANDL_1,
P_STORE_SITENAME_LANDL_2,
P_STORE_ASST_MANAGER_NAME,
P_STORE_ASST_MANAGER_MOBNO,
P_STORE_MANAGER_NAME,
P_MANAGER_MOBNO,
P_EMP_NEAREST_STORE,
P_EMP_NEAREST_STORE_MOBNO,
P_SUPERVISOR_NAME,
P_SUPERVISOR_MOBNO,
P_SECURITY_SUP_NAME_STORE,
P_SECURITY_SUP_MOBNO_STORE,
P_NAME_ALIGNED_LPO,
P_LPO_MOBILENO,
P_ALPM_ALPO_NAME,
P_ALPM_ALPO_MOBNO,
P_AREA_MANAGER_NAME,
P_AREA_MANAGER_MOBNO,
P_ZONAL_HEAD_NAME,
P_ZONAL_HEAD_NO,
P_DVR_IP_ADDRESS,
P_SIGNET_IP_ADDRESS,
P_NEAREST_POLICE_STN_NAME,
P_NEAREST_POLICE_STN_CONTNO,
P_NEAREST_HOSP_NAME,
P_NEAREST_HOSP_CONTNO,
P_NEAREST_FIRE_STN_CONTNAME,
P_NEAREST_FIRE_STN_CONTNO, 
P_STORE_ADDRESS, 
P_STORE_SPACE_SQFT,
P_LAUNCH_DATE,
P_CST_TIN_NO,
P_STORE_EMAILID,
P_NO_OF_POS,
P_NO_OF_CAMERA,
P_DVR_MODEL_GESECURITY, 
P_CAMERA_MODEL,
P_ALIGNED_LPO_MAILDID,
P_FACILTY_TEAMNAME,
P_FACILITY_TEAMNO,
P_STATE_HEAD_OPS_NAME,
P_STATE_HEAD_OPS_NO,
P_LPA,
P_SLP_STATE_HEAD, 
P_SLP_STATE_HEAD_NO,
P_CREATED_BY,
SYSDATE,
P_LAST_UPDATED_BY,
SYSDATE, 
P_ISACTIVE,
P_LATITUDE,
P_LONGITUDE 
  ) 

  RETURNING RRSOC_ID INTO TBLDATA;

  END IF;

推荐答案

日期没有格式 - 它在数据库内部存储为 7 字节(代表年、月、日、小时、分钟和秒),直到您使用的任何用户界面(即 SQL/Plus、SQL Developer、Java 等)尝试将其显示给您(用户),并将其转换为您认为有意义的内容(通常是字符串),日期具有格式.

如果您向过程提供格式化日期,那么它将是一个字符串,Oracle 将尝试使用 NLS_DATE_FORMAT 会话参数将其隐式转换为日期:

UPDATE your_table
SET your_date_column = '18-06-14'; -- or equivalently via a bind parameter

隐式转换为

UPDATE your_table
SET your_date_column = TO_DATE(
 '18-06-14',
 ( SELECT value
FROMNLS_SESSION_PARAMETERS
WHERE  parameter = 'NLS_DATE_FORMAT' )
  );

如果 NLS_DATE_FORMAT 不匹配,那么 Oracle 将引发异常(并且该参数可以由每个用户设置,因此您不应该依赖它是一致的 - 特别是在国际组织中,当默认日期格式取决于您的地区和语言).

如果您要更新值,则使用 DATE 文字而不是字符串:

UPDATE your_table
SET your_date_column = DATE '2014-06-18';

或者显式转换字符串并提供格式模型:

UPDATE your_table
SET your_date_column = TO_DATE( '18-06-14', 'DD-MM-RR' );

将参数传递给函数也是如此.使用 DATE 文字:

BEGIN
  your_procedure(
 p_launch_date => DATE '2014-06-18'
  );
END;
/

或将字符串显式转换为日期(不依赖隐式转换):

BEGIN
  your_procedure(
 p_launch_date => TO_DATE( '18-06-14', 'DD-MM-RR' )
  );
END;
/

好了关于ORA-1843: 更新记录时月份无效的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。