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

**URL:** <https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368>\
**Category:** Logstash\
**Tags:** jdbc\
**Created:** [August 8, 2023, 12:20pm UTC](https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368 "2023-08-08T12:20:22Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![cperzrt10](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cperzrt10/32/116152_2.png) [@cperzrt10](https://discuss.elastic.co/u/cperzrt10)\
**Post date:** [August 8, 2023, 12:20pm UTC](https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368/1 "2023-08-08T12:20:22Z")

</div>

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.

```auto
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

```auto
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

```auto
[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

---

<div class="post-metadata">

**Author:** ![eMitch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emitch/32/93607_2.png) [@eMitch](https://discuss.elastic.co/u/eMitch)\
**Post date:** [August 9, 2023, 8:27pm UTC](https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368/2 "2023-08-09T20:27:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![cperzrt10](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cperzrt10/32/116152_2.png) [@cperzrt10](https://discuss.elastic.co/u/cperzrt10)\
**Post date:** [August 10, 2023, 5:59am UTC](https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368/3 "2023-08-10T05:59:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cperzrt10](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cperzrt10/32/116152_2.png) [@cperzrt10](https://discuss.elastic.co/u/cperzrt10)\
**Post date:** [August 10, 2023, 7:22am UTC](https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368/4 "2023-08-10T07:22:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cperzrt10](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cperzrt10/32/116152_2.png) [@cperzrt10](https://discuss.elastic.co/u/cperzrt10)\
**Post date:** [August 11, 2023, 6:23am UTC](https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368/5 "2023-08-11T06:23:14Z")

</div>

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

```auto
## 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.

```auto
SELECT * FROM V$NLS_PARAMETERS;

```

or this

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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 8, 2023, 6:23am UTC](https://discuss.elastic.co/t/error-executing-logstash-pipeline-with-jdbc-select-sqldataexception-ora-01846-not-a-valid-day-of-the-week/340368/6 "2023-09-08T06:23:54Z")

</div>

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