ORA-01861 : literal does not match format string

Hello everyone.

I want to convert string to date.

My config, I use jdbc output plugin.

jdbc {
                                        ...		
												
					statement => ["INSERT INTO TB_REN016_020 (GATH_DTM) VALUES(?)",
								   "standard_result[gath_dtm]"]		
								   
					driver_class => "oracle.jdbc.driver.OracleDriver"
				}

the error is like this :

{:exception=>java.sql.SQLDataException: ORA-01861: literal does not match format string

So, I tried,

statement => ["INSERT INTO TB_REN016_020 (GATH_DTM) VALUES(?)",
    								   "TO_DATE('standard_result[gath_dtm]', 'yyyyMMddhh24miss')"]

but, it doesn't effect. Is there someone with an idea??

Are you trying to reference a nested field here?

Thank you for your replying @Badger.

What is a nested field?? How can I try to reference a nested field?

"standard_result[gath_dtm]"

Is this referencing a field within the event you are sending to the jdbc output?

Yes. That's right!

"standard_result[gath_dtm]" is made in ruby filter.

Does

"[standard_result][gath_dtm]"

work?

Yes. It works. But, the column GATH_DTM type is date, standard_result[gath_dtm] type is string..

So, it gives me an error too. ORA-01861: literal does not match format string

You have not explained why kind of db you are using. You might need to use a CAST in the SQL.

yeah.. I think so..

In UPDATE case, it works like this.

statement => [
UPDATE TB_MEAIN_SOLA SET FINAL_ACCE_DTM = TO_DATE(?, 'yyyyMMddhh24miss') WHERE CID = ?",
"[standard_result][gath_dtm]",
"[matchingCid][0][cid]
]

So, I want use TO_DATE function in INSERT SQL like this.

statement => ["INSERT INTO TB_REN016_020 (GATH_DTM) VALUES(?)",
    								   "TO_DATE('standard_result[gath_dtm]', 'yyyyMMddhh24miss')"]

But it doesn't work.. :cold_sweat: and I use Oracle db

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.