Trouble connecting to Oracle 11g database

Get Help
1

Hello Metabase Community!

I am trying to connect the latest Metabase build to an Oracle 11g database. All of my configuration details are shown below.

After establishing the database connection, I can see the various schemas and tables in Metabase. Yet, when I try to do a “Simple question” query, I get the following error:

Method oracle/jdbc/driver/OracleResultSetImpl.getObject(ILjava/lang/Class;)Ljava/lang/Object; is abstract

Thanks in advance for any help!

Chris

CONFIGURATION DETAILS

Metabase:
Metabase version v0.36.6 (cb258fb release-0.36.x)

Oracle Database:
Oracle Database 11g Enterprise Edition, 11.2.0.4.0 64bit Production

JDBC Driver:
Using ojdbc6.jar, via
Oracle Database 11.2.0.4 JDBC Driver & UCP Downloads
https://www.oracle.com/database/technologies/jdbcdriver-ucp-downloads.html

Using this driver as Oracle said it was compatible with Oracle Database 11g
This driver also works with JRE 1.8, according to Oracle Site; thus, using JRE 1.8 obtained from https://adoptopenjdk.net/releases.html?variant=openjdk8&jvmVariant=hotspot

JRE/OS:
{“file.encoding” “Cp1252”,
“java.runtime.name” “OpenJDK Runtime Environment”,
“java.runtime.version” “1.8.0_265-b01”,
“java.vendor” “AdoptOpenJDK”,
“java.vendor.url” “http://java.oracle.com/”,
“java.version” “1.8.0_265”,
“java.vm.name” “OpenJDK 64-Bit Server VM”,
“java.vm.version” “25.265-b01”,
“os.name” “Windows Server 2016”,
“os.version” “10.0”,
“user.language” “en”,
“user.timezone” “GMT-05:00”}

Still using the H2 database

2

Hi @cfarr
Change to AdoptOpenJDK11 and latest ojdbc8.jar (19.7)
Then run sync+scan manually in Admin > Databases > (your-db)
And check the errors in Admin > Troubleshooting > Logs.

3

Thanks much for your response. I didn’t want to share out the entire log but I see the following, which I hope is helpful (<> used for placeholders):

- In Admin > Troubleshooting > Logs:

Error metabase.sync.util Error fingerprinting Table <some number> 'ORACLE.QSI_ALERT_TAIL_<long sequence of numbers>'

- At the Windows command prompt:

A number of runtime stack traces with the following pattern:

  • Reference to the fingerprinting error above
  • ORA-29913: error in executing ODCIEXTTABLEOPEN callout
  • ORA-29400: data cartridge error
  • KUP-04040: file alert_.log in Q_ALERT_LOG__LOC not found

Other notes:

  • Please note that I didn’t know what to put in the Oracle SID field when I established the data connection in Metabase. Left it blank. It didn’t seem to matter as able to see schema/tables but perhaps an issue?

  • There is a reference to a timezone error of some sort in the Command Prompt logs:

java.lang.Exception: Unable to parse date string '2020-10-04 14:33:05.970 ' for database engine 'oracle'

4

@cfarr
Did you update to ojdbc8.jar 19.7 and JVM11?
The full stacktrace is needed to understand where the problem occurs.

As for SID - that’s probably this issue:
https://github.com/metabase/metabase/issues/11217 - upvote by clicking :+1: on the first post

There’s no timezone on that datetime string, but there is a space in the end, which is probably what is causing the problem.

I haven’t seen the other errors before, so not sure what is causing that, but you can lookup the Oracle reference to find more details.

5

Thanks again for your reply. Your points:

  1. I did update to ojdbc8.jar 19.7 and JRE11 (not JVM) before sharing my previous post.
  2. I looked at the SID post. It says to keep SID blank on the configuration screen, which I'm already doing.
  3. I googled the ORA-29913 error and found this:

The most common causes of the ORA-29913 error are that Oracle fails to open OS file that is required. These files might contain output, input, log, discard or bad files. The main reason behind the ORA-29913 error is that an Oracle executable has denied the permission or the file granted the permission or directory itself does not exist for accessing the directory properly.

via http://www.dba-oracle.com/t_ora_29913_external_table_error.htm

Any ideas?

6

@cfarr JRE/JDK is a JVM. I don’t know if the error comes from the Oracle driver or the database, but it comes from Oracle. Revoke the privileges for the user you’re using to connect with, so it only has access to the tables you need.

7

Sorry, confused about JRE/JDK/JVM …

Not sure what to do about the privileges as not DBA. Can you perhaps describe why this should resolve the issue and provide a little bit more detail on what you’d recommend? I can then reach out to our DBA.

Thanks in advance!

Chris

8

@cfarr Tell your DBA that you only need access to specific tables. Until we have a way to exclude tables, which there’s actually no read-access to, then you’ll see such errors during sync+scan process:
https://github.com/metabase/metabase/issues/8955

9

Thanks much for this additional detail. An additional clarification if I could.

I read the post in your link, and one of the commenters said:

... create a user in Oracle that only has access to the schemas you want

I believe that is what my DBA did. That is, if I open another SQL client (Oracle SQL Developer) and connect to the DB with the user credentials I'm using for Metabase, I see only the tables in a particular schema. The DBA is giving this user access to a schema and thereby access to the tables associated with that schema.

I think you though are recommending that the DBA give my Metabase user read access to only particular tables, not a particular schema. Is that correct?

Thanks.

Chris

10

@cfarr Oracle uses schemas differently than Postgres, so I don’t know. If you try with DBeaver.io then it should produce the same results as Metabase, since they’re both using the same style of driver, but haven’t checked.
In other words - until we have a way to figure out if tables provided are actually readable, then the credentials needs to not provide any type of access to non-readable tables. There’s a whole slew of related issues:
https://github.com/metabase/metabase/issues/2146#issuecomment-631995455

11

Got it. Thanks.

12

@flamber I have yet to talk to my DBA, but I did put in the same Oracle credentials into Tableau Desktop, just to see what would happen. Tableau Desktop allows me to see all the available schemas in the database server but defaults to the one I think my DBA intended me to see. No errors whatsoever. I can even query tables in those other schemas. Not sure why Tableau can handle the Oracle connection but Metabase cannot. Additional thoughts welcome, as I continue my “setup journey.” Thanks. Chris

13

@cfarr Try 0.37.0-rc2 - it has implemented some of the things - do not use that in production and make a backup first.
Perhaps Tableau has implemented select-testing - who knows.

14

OK, thanks. BTW, on Tableau’s driver page, they are also advocating use of ojdbc8.jar (19.3?) via https://www.oracle.com/database/technologies/appdev/jdbc-ucp-19c-downloads.html

15

@cfarr Because they haven’t updated the page, since Oracle has made several releases the past couple of months. We haven’t updated either: https://www.metabase.com/docs/latest/administration-guide/databases/oracle.html

16

@flamber okay, went to version you mentioned, added oracle connection, hit the “sync” a couple of times, not seeing error messages but get “The database doesn’t have any tables,” maybe working? thanks Chris

17

@cfarr I don’t know. You’ll have to ask the DBA to investigate, since they can see the queries.

18

@flamber Good news. I can see the database tables now and querying seems to work. After I upgraded to 0.37.0-rc2. I guess it takes Metabase some time to scan everything (reason I was seeing “The database doesn’t have any table”). Thanks for your help! Chris

1 Like
19

Same here...
Many Problems with Metabase 0.36 and Oracle 11....
Where can In found the Metabase 0.37.0-rc2?

20

@Andreas_SQL Make sure you’re using the latest ojdbc8.jar.
https://github.com/metabase/metabase/releases/tag/v0.37.0-rc2

1 Like