Invalid conversion requested with native oracle SQL query and date parameters

Get Help
1

Github question

I have a native SQL query run against ORACLE with date parameters.
Query is recursive and looks like:

WITH parameters AS (
SELECT
TO_DATE('{{dateFrom}}', 'yyyy-mm-dd') AS date_from,
LEAST(SYSDATE, TO_DATE('{{dateTo}}', 'yyyy-mm-dd')) AS date_to
FROM dual
)
.... blah blah blah (700+ lines of code)

SELECT * FROM ds WHERE 1=1 AND dateField <= (SELECT date_to FROM parameters)

when I try to execute it gives me an error of “Invalid conversion requested (code 17132)”.
When I change parameters type to text and enter data in appropriate format - everything is fine, but on UI user have to enter values manually - w/o datepicker widget.
How should I set up the things to get my query execute successfully?

Suggested solution (within github issue) does not help.

2

Hi @buckelieg
Which version of Metabase? Post “Diagnostic Info” from Admin > Troubleshooting.
And which version of the ojdbc8/11.jar driver?
What filter type are you using? Date or Field Filter?
For other databases, you shouldn’t enclose the variables with quotes. I’m guess it’s the same for Oracle. Are you getting a different error without the quotes?

3

I think you are tryng convert a date type to date. If you remove this to_date function and just leave {{dateTo}} and {{dateFrom}}, it’s works?

that quotes around variable it’s strange too. Meabe need remove

4

Hi @flamber.

Here is the info that you requested:

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) 
Chrome/67.0.3396.99 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "1.8.0_152-b16",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_152",
"java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
"java.vm.version": "25.152-b16",
"os.name": "Windows Server 2012 R2",
"os.version": "6.3",
"user.language": "en",
"user.timezone": "Europe/Moscow"
},
"metabase-info": {
"databases": [
  "postgres",
  "oracle"
 ],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
  "database": {
    "name": "PostgreSQL",
    "version": "10.3"
  },
  "jdbc-driver": {
    "name": "PostgreSQL JDBC Driver",
    "version": "42.2.8"
  }
},
"run-mode": "prod",
"version": {
  "tag": "v0.34.3",
  "date": "2020-02-25",
  "branch": "release-0.34.x",
  "hash": "1a83edb"
},
"settings": {
  "report-timezone": null
}
}
}

Oracle driver info:

Manifest-Version: 1.0
Specification-Title:    Oracle JDBC driver classes for use with JDK14
Sealed: true
Created-By: 1.4.2_08 (Sun Microsystems Inc.)
Implementation-Title:   ojdbc14.jar
Specification-Vendor:   Oracle Corporation
Specification-Version:  Oracle JDBC Driver version - "10.2.0.1.0"
Implementation-Version: Oracle JDBC Driver version - "10.2.0.1.0"
Implementation-Vendor:  Oracle Corporation
Implementation-Time:    Wed Jun 22 11:19:45 2005

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false

I am using date filter type.

@geovani,
I tried next things:

  1. TO_DATE('{{dateFrom}}', 'yyyy-mm-dd') AS date_from,
LEAST(SYSDATE, TO_DATE('{{dateTo}}', 'yyyy-mm-dd')) AS date_to

  2. TO_DATE({{dateFrom}, 'yyyy-mm-dd') AS date_from,
LEAST(SYSDATE, TO_DATE({{dateTo}}, 'yyyy-mm-dd')) AS date_to

  3. {{dateFrom}} AS date_from,
LEAST(SYSDATE, {{dateTo}}) AS date_to

None of the above works :frowning:

I also tried to chenge global date format and play with it in the to_date function and trying to modify as it is it shows in the datepicker widget but with no success

5

@buckelieg I would recommend that you upgrade Java and ojdbc:
https://adoptopenjdk.net/releases.html?variant=openjdk11&jvmVariant=hotspot
https://www.metabase.com/docs/latest/administration-guide/databases/oracle.html
It’s the first time I’ve seen this error, and given the amount of installations using Oracle, then I think it’s something specific to your setup.