[Bug]: Database explorer - Can't get number of table rows from Oracle database

dave-csc commented 5 months ago

Apache Hop version?

2.8.0

Java version?

openjdk version "21.0.2" 2024-01-16

Operating system

Linux

What happened?

In Database Explorer you can't get the number of rows from an Oracle database table, the error reported is (I used generic <schema> and <table> as their names):

InvocationTargetException: Couldn't get a result because of an error :org.apache.hop.core.exception.HopDatabaseException: 
An error occurred executing SQL: 
select count(*) FROM "<schema>.<table>"
ORA-00942: table or view does not exist

https://docs.oracle.com/error-help/db/ora-00942/

Root cause: OracleDatabaseException: ORA-00942: table or view does not exist

That's because both user/schema and the table name have been enclosed together in double quotes (the same query in an Oracle client fails with the same reason). The double quotes enclosure should be removed to make the query work (or alternatively, user/schema and table name should be enclosed in double quotes separately, e.g. "<schema>"."<table>").

For additional info: the row preview buttons are working as expected instead.

Issue Priority

Priority: 2

Issue Component

Component: Hop Gui

nadment commented 5 months ago

Works well for me with an Oracle 12, but there must be something special about your context that needs to be identified. An advanced option?

dave-csc commented 5 months ago

Hello @nadment,

as additional info I'm exploring Oracle 19 servers, and using the OracleDriver 23.3.0.23.09 JDBC library.

I checked "Quote all identifiers in database" in the metadata advanced options, it seems to work now... thanks

hansva commented 5 months ago

I'm glad it's solved, we might want to keep this one open to see if we are adding quotes incorrectly when that option is not selected.

apache / hop