-1

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
Nad
  • 4,605
  • 11
  • 71
  • 160
  • What is the data type of the column? What is the data type of the parameter? Hint: They should be the same. – Gordon Linoff Nov 10 '17 at 12:00
  • **[Edit]** your question and show us the **complete** procedure. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Nov 10 '17 at 12:00
  • @GordonLinoff: the datatype is `date` which I am using for my requirement. yes the datatype is same `P_LAUNCH_DATE IN DATE,` – Nad Nov 10 '17 at 12:01
  • So is the error coming from the procedure, or the call *to* the procedure (from implicit/explicit conversion of the argument)? Show how you're calling the procedure,as well as the procedure itself and the table definition, and the full error stack. – Alex Poole Nov 10 '17 at 12:14
  • @AlexPoole: i have updated my question, have a look – Nad Nov 10 '17 at 12:26
  • If column LAUNCH_DATE is date type, and P_LAUNCH_DATE is date type, this shouldn't raise any error. In your example on JSFiddle 'TO_DATE(P_LAUNCH_DATE, 'DD-MM-RR'),' is meaningless, since P_LAUNCH_DATE is already a date. LAUNCH_DATE = P_LAUNCH_DATE should work. – Goran Stefanović Nov 10 '17 at 12:33
  • @GoranStefanović: but still I am getting that error – Nad Nov 10 '17 at 12:34
  • @VVVV Is column's LAUNCH_DATE data type in table TBL_RRSOC_STORE_INFO really date? BTW. why are you updating CREATE_BY and CREATED_DATE columns? Surely these two should not be overwritten? Similarly, setting LAST_UPDATED_BY and LAST_UPDATED_DATE when inserting data is not logical - they should stay empty until UPDATE actually takes place. – Goran Stefanović Nov 10 '17 at 12:43

1 Answers1

5

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;
/
MT0
  • 143,790
  • 11
  • 59
  • 117
  • i tried like this, `LAUNCH_DATE = TO_DATE(P_LAUNCH_DATE, 'DD-MM-RR'),` but still giving the same error. i have also updated the question with the procedure. have a look – Nad Nov 10 '17 at 12:29
  • 1
    @VVVV You are passing a `DATE` into the procedure - you do not need to convert it to a date in the `UPDATE` or `INSERT` statements as it already is. **Instead**, what you need to do is ensure that when you call the procedure the `P_LAUNCH_DATE` argument is passed as a date and not as a string that is going to be implicitly converted. – MT0 Nov 10 '17 at 12:53
  • see while binding the date in textbox i bind it through json. so there is the main problem – Nad Nov 10 '17 at 13:07
  • please suggest what should I do now ? – Nad Nov 10 '17 at 13:12
  • 1
    @VVVV Fix your server-side scripts that parse the JSON and call the procedure to pass dates not strings. – MT0 Nov 10 '17 at 13:14
  • now, I am passing it like this `18-6-2014` still getting the same error – Nad Nov 10 '17 at 13:40
  • @VVVV You have not shared that part of the code and it is outside the scope of this (Oracle) question so I would suggest making a new question and posting the relevant code there. – MT0 Nov 10 '17 at 13:56