Reputation: 823
I am getting oci_execute(): ORA-01843: not a valid month invalid. Why it is giving it?
INSERT INTO DETAIL (PPD_ID, PPD_ID,PPD_ENTRY_DATE, PPD_IGM_ID, PPD_DFM_ID, PPD_DRM_ID, PPD_HEIGHT, PPD_UOM_ID, PPD_FTM_ID, PPD_FRQ_ID, PPD_START_DATE_TIME, PPD_END_DATE_TIME, PPD_STATUS, PPD_STM_ID)
VALUES ('WS00318229',2440900,'29-12-2015',350,1,1,50,46,1,1,'29-12-2015','29-12-2015','PRESCRIBED',6)
When i am using the above query from php to insert record in to oracle database it is giving following error.
oci_execute(): ORA-01843: not a valid month invalid
But when i use the following query it is not giving any error and it is inserting record.How to solve this? This error was already is there but i am not clear about the answers.Any ideas why it is like this.
INSERT INTO DETAIL (PPD_ID, PPD_ID,PPD_ENTRY_DATE, PPD_IGM_ID, PPD_DFM_ID, PPD_DRM_ID, PPD_HEIGHT, PPD_UOM_ID, PPD_FTM_ID, PPD_FRQ_ID, PPD_START_DATE_TIME, PPD_END_DATE_TIME, PPD_STATUS, PPD_STM_ID)
VALUES ('WS00318229',2440900,'29-DEC-2015',350,1,1,50,46,1,1,'29-DEC-2015','29-DEC-2015','PRESCRIBED',6)
Upvotes: 0
Views: 4946
Reputation: 1167
You are inserting strings into DATE
columns. When you do that, oracle performs implicit conversion based on your NLS settings, specifically, nls_date_format
. Evidently when you connect from php script and from some client you use (SQL*Plus, SQL Developer, what have you), your NLS settings differ.
You can check session parameters using following query.
SELECT * FROM nls_session_parameters;
For date presented in format '29-DEC-2015'
nls_date_format
should be 'DD-MON-YYYY'
. If it has some other value, you can change it using following query.
alter session set nls_date_format='DD-MON-YYYY'
Or you can explicitly convert string to date using TO_DATE
function and passing format as second parameter, but this will require your application code to be consistent in using only specified date format.
TO_DATE('29-DEC-2015', 'DD-MON-YYYY')
Upvotes: 1