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

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: not a valid month

Below is how I send it as a parameter for updating it

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.

UPDATE

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;

回答1:

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.

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

Is implicitly converted to

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

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).

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' );

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;
/