[Oracle] implement service_name connexion with oracle

[nriss]

Problem

When trying to connect to an oracle db using pyrog, This error is raised: “(cx_Oracle.DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor\n(Background on this error at: http://sqlalche.me/e/4xp6)”

Description

The actual string connexion in pagai is {db_handler}://{login}:{password}@{host}:{port}/{database}{url_suffix} (see https://github.com/arkhn/fhir-river/blob/main/django/river/common/database_connection/db_connection.py)

On pycharm, the string connection has this format: jdbc:oracle:thin:@//{host}:{port}/{service_name}

Implementation

https://stackoverflow.com/questions/14140902/using-oracle-service-names-with-sqlalchemy

discussed with @Jasopaum and @tevariou High priority, we want to show the beginning of the mapping soon with @elsiehoffet-94

[nriss]

I tried to use service_name with v3.5.0 but I have the same error: "(cx_Oracle.DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor\n(Background on this error at: http://sqlalche.me/e/4xp6)"

[simonvadee]

@tevariou @nriss is this issue still active ? what's the client project related to this issue ?

[nriss]

This issue was for Foch, for now, we cannot connect pyrog to an oracle db using service_name. However the usage of airbyte and the postgres datalake resolved this problem.

If we confirm the systematic usage of the postgres datalake, we can close this issue @MiskoG ?

[MiskoG]

Yep to me it's a major benefit of using the datalake together with a EL (extract load) approach. We don't bother with every possible type of DB/API/Streaming source, for us later in the pipe it's just Postgres. Dealing with every type of source is Airbyte's (or equivalent) job.

[nriss]

In that case, we can simplify the pyrog connector and only set one global connection to the postgres database, nope ? Do we really need to have one db connection for each project=source ?

[MiskoG]

Very interesting point.

We could do two things based on what you said

(1) Remove Pyrog/River's backend logic that deals with anything else than Postgres

(2) Think about a new UX workflow around projects and db connection, as there may be only one (moreover the same) db connection in every project I don't know if we want to work on it right now as we're experimenting other stuff in parallel (DBT on FHIR), and even supposing that we are already using the new Pyrog, I don't see that issue as a major painpoint for the moment.

If you're okay with that I am closing this issue and let's discuss about the rest in this separate one https://github.com/arkhn/fhir-river/issues/692