ORA-1843: 更新记录时月份无效 [英] ORA-1843: not a valid month while updating record
问题描述
出于某种目的，我想更新我的日期列.已经存储在列中的值如下所示
I want to update my date column for some purpose. The value already stored in the column is like below
18-06-14
更新时如果我不更新任何东西并调试并检查它，它的格式为 2014-06-18T00:00:00
and while updating If I don't update any thing and debug and check it, it takes format as 2014-06-18T00:00:00
因此，在更新过程中，我收到如下错误.
So while Updating I get error as below in my procedure.
ORA-1843: 月份无效
ORA-1843: not a valid month
下面是我如何将它作为参数发送来更新它
Below is how I send it as a parameter for updating it
LAUNCH_DATE = P_LAUNCH_DATE,
在 P_LAUNCH_DATE 我有更新的值.这给了我上述错误.我该如何解决.
in P_LAUNCH_DATE I have values for updating. which is giving me the above error. How do I resolve it.
更新
这是我使用它的整个存储过程.
Here is my whole storedproceudre the way I am using it.
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
SET
STATE = 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 等)尝试将其显示给您(用户)，并将其转换为您认为有意义的内容(通常是字符串)，日期具有格式.
A date does not have a format - it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date has a format.
如果您向过程提供格式化日期，那么它将是一个字符串，Oracle 将尝试使用 NLS_DATE_FORMAT 会话参数将其隐式转换为日期:
If you are providing a formatted date to a procedure then it will be a string and Oracle will try to implicitly cast it to a date using the NLS_DATE_FORMAT session parameter:
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
FROM NLS_SESSION_PARAMETERS
WHERE parameter = 'NLS_DATE_FORMAT' )
);
如果 NLS_DATE_FORMAT 不匹配，那么 Oracle 将引发异常(并且该参数可以由每个用户设置，因此您不应该依赖它是一致的 - 特别是在国际组织中，当默认日期格式取决于您的地区和语言).
If the NLS_DATE_FORMAT does not match then Oracle will raise an exception (and the parameter can be set by each user so you should not rely on it being consistent - especially in international organisations when the default date format depends on your territory and language).
如果您要更新值，则使用 DATE 文字而不是字符串:
If you are updating the value then use a DATE literal and not a string:
UPDATE your_table
SET your_date_column = DATE '2014-06-18';
或者显式转换字符串并提供格式模型:
Or explicitly convert the string and provide the format model:
UPDATE your_table
SET your_date_column = TO_DATE( '18-06-14', 'DD-MM-RR' );
将参数传递给函数也是如此.使用 DATE 文字:
The same is true for passing parameters to your function. Either use a DATE literal:
BEGIN
your_procedure(
p_launch_date => DATE '2014-06-18'
);
END;
/
或将字符串显式转换为日期(不依赖隐式转换):
or explicitly convert the string to a date (and do not rely on implicit conversion):
BEGIN
your_procedure(
p_launch_date => TO_DATE( '18-06-14', 'DD-MM-RR' )
);
END;
/
这篇关于ORA-1843: 更新记录时月份无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
