Not able to successfully insert the data into the oracle db table

2
votes

Please advise why i am getting error in below qracle insert qquery beow is the query

insert into RT_INVOICE (VERSION, PART_ID_BROKER, EMAIL_ID, ATTACHMENT_ID, INITIAL_AMOUNT, CURRENT_AMOUNT, GROSS_AMOUNT, 
RECEIVED_DATE, COMMITTED_DATE, CREATEDDATE, UPDATEDDATE, SETTLEMENT_DATE, CURR_ISO_CODE, WQTY_CODE_CURRENT, 
BROKER_REFERENCE, CREATED_BY, ENTITY, PRODUCT, BROKER_INV_REF, ASSIGNEE, RECEIPT_DATE, TRADE_COUNT, GST_PCT, 
IS_GST_APPLIED, SOURCE_SYSTEM, DISCOUNT, MANUAL_INDICATOR, MANUAL_REFERENCE, PAYMENT_REFERENCE, QUERY_ASSIGNMENT,
Remarks, PARENT_INVO_ID, INVOICE_MONTH, ID) values (0, 40158334066, 0, 0, 22.0, 22.0, 22.0, '10 October 2016',
null, '2016-10-10 03:51:15', '2016-10-10 03:51:15', null, 'HKD', 'BrokerageManualReconciliationWFQueue',
'IRDOCT169694', 'bro_admin', 'PLC LONDON', 'IRD', 'wwwqqsd', '', '10 October 2016', 0, 0.0, 'false', 'GDS', 0.0, 
'true', null, null, null, null, null, '2016-10', 13093090)

The error that i am getting is 

Error report:
SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

Just for the reference the structure of the table is 

Name                           Null     Type                                                                            
ID                             NOT NULL NUMBER(32)                      
VERSION                        NOT NULL NUMBER(20)                        
PART_ID_BROKER                 NOT NULL NUMBER(32)                        
INITIAL_AMOUNT                 NOT NULL NUMBER(25,5)                      
CURR_ISO_CODE                           VARCHAR2(20)                      
CURRENT_AMOUNT                 NOT NULL NUMBER(25,5)                      
SETT_ID                                 NUMBER(32)                        
RECEIVED_DATE                  NOT NULL DATE                              
COMMITTED_DATE                          DATE                              
SETTLEMENT_DATE                         DATE                              
CREATEDDATE                    NOT NULL TIMESTAMP(6)                      
UPDATEDDATE                             TIMESTAMP(6)                      
WQTY_CODE_CURRENT              NOT NULL VARCHAR2(100)                     
BROKER_REFERENCE                        VARCHAR2(100)                     
CREATED_BY                     NOT NULL VARCHAR2(30)                      
SOURCE_SYSTEM                  NOT NULL VARCHAR2(20)                      
INVOICE_MONTH                           VARCHAR2(30)                      
ENTITY                         NOT NULL VARCHAR2(50)                      
PRODUCT                        NOT NULL VARCHAR2(50)                      
BROKER_INV_REF                          VARCHAR2(50)                      
ASSIGNEE                                VARCHAR2(50)                      
RECEIPT_DATE                            TIMESTAMP(6)                      
TRADE_COUNT                             NUMBER                            
GST_PCT                                 NUMBER(25,5)                      
IS_GST_APPLIED                          CHAR(1)                           
PARENT_INVO_ID                          NUMBER(32)                        
DISCOUNT                                NUMBER(25,5)                      
MANUAL_INDICATOR                        CHAR(1)                           
MANUAL_REFERENCE                        VARCHAR2(100)                     
PAYMENT_REFERENCE                       VARCHAR2(100)                     
QUERY_ASSIGNMENT                        VARCHAR2(100)                     
REMARKS                                 VARCHAR2(500)                     
ATTACHMENT_ID                           NUMBER(32)                        
EMAIL_ID                                NUMBER(32)                        
GROSS_AMOUNT                   NOT NULL NUMBER(25,5)
2
sqloracle
You're trying to insert string values into date fields. Unless those string values match your NLS date format, you will get this error – Tibrogargan

2 Answers

1
votes

To convert string to date try to use to_date function:

insert into RT_INVOICE (VERSION, PART_ID_BROKER, EMAIL_ID, ATTACHMENT_ID, INITIAL_AMOUNT, CURRENT_AMOUNT, GROSS_AMOUNT, 
RECEIVED_DATE, COMMITTED_DATE, CREATEDDATE, UPDATEDDATE, SETTLEMENT_DATE, CURR_ISO_CODE, WQTY_CODE_CURRENT, 
BROKER_REFERENCE, CREATED_BY, ENTITY, PRODUCT, BROKER_INV_REF, ASSIGNEE, RECEIPT_DATE, TRADE_COUNT, GST_PCT, 
IS_GST_APPLIED, SOURCE_SYSTEM, DISCOUNT, MANUAL_INDICATOR, MANUAL_REFERENCE, PAYMENT_REFERENCE, QUERY_ASSIGNMENT,
Remarks, PARENT_INVO_ID, INVOICE_MONTH, ID) values (0, 40158334066, 0, 0, 22.0, 22.0, 22.0, to_date('10 October 2016', 'dd month yyyy', 'nls_date_language = american') ,
null, to_date('2016-10-10 03:51:15', 'yyyy-mm-dd hh24:mi:ss'), to_date('2016-10-10 03:51:15', 'yyyy-mm-dd hh24:mi:ss'), null, 'HKD', 'BrokerageManualReconciliationWFQueue',
'IRDOCT169694', 'bro_admin', 'PLC LONDON', 'IRD', 'wwwqqsd', '', to_date('10 October 2016', 'dd month yyyy', 'nls_date_language = american'), 0, 0.0, 'false', 'GDS', 0.0, 
'true', null, null, null, null, null, '2016-10', 13093090)
0
votes

'10 October 2016', '2016-10-10 03:51:15', and '2016-10-10 03:51:15' are strings. As your columns are datetimes, as they should be, Oracle needs to convert your strings and tries this as best as it can given your current settings. It's more or less a matter of luck whether Oracle understands your formats or not.

Use ANSI datetime literals instead:

date '2016-10-10', null, timestamp '2016-10-10 03:51:15', timestamp '2016-10-10 03:51:15'