DbApiHook.test_connection() does not work with Oracle db

[gbonazzoli]

Apache Airflow version

2.2.0 (latest released)

Operating System

Ubuntu 20.04.3 LTS

Versions of Apache Airflow Providers

apache-airflow-providers-oracle==2.0.1

Deployment

Other

Deployment details

What happened

The title and screenshot are self explaining

What you expected to happen

To have a successful message similar to what I got with SQL Server.

How to reproduce

No response

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[potiuk]

cc: @msumit

[hkjang]

first thanks. also i got that error message ORA-00923

[uranusjr]

ORA-00923 is FROM keyword not found where expected. Currently we are testing a database connection with select 1, but it seems like Oracle does not support this. From this table, there is likely not any single query that works accross all implementations. Perhaps instead of treating all exceptions as a failure, we should selectively catch some and treat them as success. This button is only meant to test if the conenction config works, and when the backend responds something (even a SQL error), the connection is definitely working.

[gbonazzoli]

For all Oracle Database's versions you can replace "select 1" with "select 1 from dual"

dual is a logical table present in every oracle schema you're connecting to.

Probably is a simpler implementation of the test.

[potiuk]

For all Oracle Database's versions you can replace "select 1" with "select 1 from dual"

dual is a logical table present in every oracle schema you're connecting to.

Probably is a simpler implementation of the test.

Surely for Oracle yes so this mean that you have to add oracle specific 'test_connection" method in https://github.com/apache/airflow/blob/main/airflow/providers/oracle/hooks/oracle.py to override "test_connection" in https://github.com/apache/airflow/blob/main/airflow/hooks/dbapi.py#L376

Would you @hkjang @gbonazzoli care to make a PR fixing it for Oracle Hook? Sounds super easy and since you already use Oracle it wll be easy for you to test the fix.

[hubert-pietron]

Hi, can i finish it @gbonazzoli ?

[gbonazzoli]

Yes, you are welcome, very welcome !!!