[apache-airflow-providers-oracle] inconsistent setup when using Extra Fields

[merlinux]

Apache Airflow Provider(s)

oracle

Versions of Apache Airflow Providers

apache-airflow-providers-celery 3.14.0
apache-airflow-providers-common-compat 1.10.0
apache-airflow-providers-common-io 1.7.0
apache-airflow-providers-common-sql 1.30.0
apache-airflow-providers-fab 3.0.3
apache-airflow-providers-oracle 4.3.0
apache-airflow-providers-postgres 6.5.0
apache-airflow-providers-smtp 2.4.0
apache-airflow-providers-ssh 4.2.0
apache-airflow-providers-standard 1.10.0

Apache Airflow version

Airflow 3.1.6

Operating System

Red Hat Enterprise Linux release 8.6 (Ootpa)

Deployment

Virtualenv installation

Deployment details

Python venv with RabbitMQ cluster.

What happened

When you create an Oracle conn fron UI, filling the form as indicated and Extra Fields with Oracle thick client configured as capture shows:

Your get_uri field looks like:
oracle://user:password@HOST:PORT/SCHEMA?thick_mode=TRUE&thick_mode_lib_dir=%2Fairflow%2Finstantclient_23_26%2F

Then, from CLI, run airflow connections test testing_ORA and it fail with Connection failed!
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

On the other hand, filling the UI form that way:

Your get_uri field looks like:
oracle://user:password@SCHEMA/SCHEMA?thick_mode=TRUE&thick_mode_lib_dir=%2Fairflow%2Finstantclient_23_26%2F

Again, from CLI, run airflow connections test testing_ORA and it wil return
Retrieving connection: 'testing_ORA'
Testing...
Connection success!

Even more, if you add the port to the config like (please note the added port number):

Your get_uri field looks like:
oracle://user:password@SCHEMA:PORT?thick_mode=TRUE&thick_mode_lib_dir=%2Fairflow%2Finstantclient_23_26%2F

Again, from CLI, run airflow connections test testing_ORA and it wil return:

Connection failed!
ORA-12262: Cannot connect to database. Could not resolve hostname RAC8.WORLD in Easy Connect connection string SCHEMA:PORT

What you think should happen instead

I guess when you fill Extra Fields with Oracle thick client (as capture 1 show) the provider should look for the SCHEMA field into the tnsnames.ora NET_SERVICE_NAME:

NET_SERVICE_NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = protocol) (HOST = host) (PORT = port))
(CONNECT_DATA =
(SERVICE_NAME = service_name)
)
)

Instead it's matching the field Host with NET_SERVICE_NAME, wich is wrong because if you have diferents NET_SERVICE_NAME on the same Host, you can't define multiples entries with the same name (in this case, HOST)

How to reproduce

Please create different Oracle conn as descripted and create a POC tnsnames.ora like:

SCHEMA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = protocol) (HOST = host) (PORT = port))
(CONNECT_DATA =
(SERVICE_NAME = service_name)
)
)

HOST=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = protocol) (HOST = host) (PORT = port))
(CONNECT_DATA =
(SERVICE_NAME = service_name)
)
)

You will note your conn is working fine on HOST entry and fail on SCHEMA entry

Anything else

This Oracle provider (v4 3.0) should work fine as v3.6.0 over Airflow v2.5.1 and v2.9.2, wich I´m running on other productive set smooth and well. I believe it was broken in v4.1.0 (just an opinion)

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

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[eladkal]

cc @guan404ming can you check if #49533 caused regression?