Error executing logstash pipeline with jdbc select SQLDataException: ORA-01846: not a valid day of the week

[cperzrt10 (Cristian)]

We have into logstash pipeline the config to search into database and get the data, after the first search we want only select the new data, to do this we use the config of jdbc plugin, my pipeline config.

input {
  jdbc {
    jdbc_driver_library => "/usr/share/logstash/jdbc-drivers/ojdbc10.jar"
    jdbc_driver_class => "oracle.jdbc.OracleDriver" 
    jdbc_connection_string => "*********"
    jdbc_user => "user"
    jdbc_password => "pass"
    statement => "SELECT transaction_pk, sndiso, receivingiso, poc, servicetype, duration, start_date, end_date FROM  table vk WHERE start_date > :sql_last_value"
    use_column_value => true
    tracking_column => "start_date"
    tracking_column_type => "timestamp"
    schedule => "* * * * *"
    last_run_metadata_path => "/etc/logstash/test-jdbc-int-sql_last_value.yml"
  }
}
filter {
  ....
}
output {
  ....
}

the file /etc/logstash/test-jdbc-int-sql_last_value.yml contains this

cat /etc/logstash/test-jdbc-int-sql_last_value.yml
--- 1970-01-01 00:00:00.000000000 Z

and in the execution log apears this error

[2023-08-08T14:13:00,971][ERROR][logstash.inputs.jdbc     ] Java::JavaSql::SQLDataException: ORA-01846: not a valid day of the week: SELECT transaction_pk, sndiso, receivingiso, poc, servicetype, duration, start_date, end_date FROM  table vk WHERE start_date > TIMESTAMP '1970-01-01 00:00:00.000000 +00:00'
[2023-08-08T14:13:00,972][WARN ][logstash.inputs.jdbc     ] Exception when executing JDBC query {:exception=>Sequel::DatabaseError, :message=>"Java::JavaSql::SQLDataException: ORA-01846: not a valid day of the week\n", :cause=>"#<Java::JavaSql::SQLDataException: ORA-01846: not a valid day of the week\n>"}

Thanks

[eMitch (Eddie Mitchell)]

Oracle looks angry about the formatting of the date/timestamp used.
Are you able to run the select query that was generated directly against Oracle?
If you are, make sure the use that you're using for Logstash doesn't have defaults that are messing with how the timestamp is functioning.

[cperzrt10 (Cristian)]

Hi, if i run the SQL query directly against Oracle works fine.

[cperzrt10 (Cristian)]

Hi, i have see tha the problen is the parameter "NLS_DATE_LANGUAGE" in my database is Latin American Spanish, i dont know how do the select setting the parameter NLS_DATE_LANGUAGE.

Thanks

[cperzrt10 (Cristian)]

Finaly i've find the solution, to do correctly the sql queries to oracle i've to change the jvm.option the value of -Duser.language and Duser.country

## Locale
# Set the locale language
-Duser.language=es

# Set the locale country
-Duser.country=ES

this settings must meet with the oracle bbdd settings of NLS_DATE_LANGUAGE in our case.

To get the NLS_DATE_LANGUAGE settings of the database you mai execute this sql query in your database.

SELECT * FROM V$NLS_PARAMETERS;

or this

SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER IN ( 'NLS_LANGUAGE', 'NLS_DATE_LANGUAGE', 'NLS_SORT' );