CODEWITHSUNDEEP
oracle-databasedatemulemulesoftmule4
GettingStarted With123
GettingStarted With123

Reputation: 421

Mule 4 Database Insert into Oracle Table with Date field issues

Am using Mule 4 ( runtime 4.3 ) running in anypoint studio ( 7.15.0 )

Am trying to do a simple database insert ( Database is Oracle )

Here is the relevant code for insert :

    <db:insert doc:name="Insert emp record"  config-ref="Emp_Database_Config" >
                <db:sql ><![CDATA[ insert into emp ( emp_id, emp_name, start_date ) values ( :empId, :empName , TO_DATE(:startDate, 'YYYY-MM-DD') ) ]]></db:sql>
                <db:input-parameters ><![CDATA[#[%dw 2.0
output application/java
---
{
    'empId' : vars.empId,
    'empName' : vars.empName,
    'startDate' : '3000-01-01'  
}]]]></db:input-parameters>
            
</db:insert>

Here start_date is a Date field in emp table. Am trying to do a date insert using following code:

TO_DATE('3000-01-01', 'YYYY-MM-DD')

However it fails at runtime with following error:

ERROR 2023-12-02 20:10:45,224 [[MuleRuntime].uber.08: [emp-sys-v1_to_checkin].process-emp-flow.BLOCKING @7d642671] org.mule.db.commons.shaded.internal.domain.executor.AbstractExecutor: An SQLException exception has occurred. java.sql.SQLException: An SQLException was provoked by the following failure: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

Google search provided this mulesoft help article: https://help.mulesoft.com/s/article/Database-Connector-Timestamp-format-must-be-yyyy-mm-dd-hh-mm-ss-fffffffff-Oracle-DB-Mule-4

Based on this tried appending as :

'startDate' : '3000-01-01'  ++ " 00:00:00.00000"

However then started getting this error:

'ORA-01858 a non-numeric character was located where a digit was expected'

Database Connector - Mule 4 1.14.6

Database table is in Oracle and in the table 'emp' , the field 'start_date' is a Date field

If I do a direct database insert via sql plus I can do an insert without issues using :

TO_DATE('3000-01-01', 'YYYY-MM-DD')

Not sure why such a simple activity is failing due to Date issues ....

Edit1: am aware that Date does hold time

Edit2: also tried to send data as application/java and still encountering this issue

Upvotes: 0

Views: 758

Answers (2)

Karthik
Karthik

Reputation: 2431

I also faced the same error during inserting date field in oracle db.

An SQLException was provoked by the following failure: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

@aled answer will definitely will work for you but the below format worked for me. See if it helps for you

%dw 2.0
output application/java
---
'startDate' : ("'" ++ (|3000-01-01| as Date{format: "yyyy-MM-dd"} as String{format: "dd-MMM-yyyy"}}) ++ "'")

'startDate' : '01-Jan-3000'

Upvotes: 1

aled
aled

Reputation: 25812

Try to match the query format with the argument format:

TO_DATE(:date, 'YYYY-MM-DD HH:mm:ss')

'startDate' : '3000-01-01 00:00:00'

Upvotes: 1

Related Questions