qgis with Oracle Cloud Database

qgis is A Free and Open Source Geographic Information System. It can use several spatial backends, and Oracle is just one of them. From what I see it is quite often used with other databases like PostgreSQL or even SpatiaLite. And even Oracle is quite well supported, there sem to be some loose ends, especially for complex situations.

One of these complex situations is the connection to an Always Free Autonomous Database. Oracle tries to secure the database access, so by default it does not allow a simple connection with host, port, service_name and username/password. Instead a wallet is provided to secure the network access before providing the credentials. 

The zip contains several files which can be used for OCI or JDBC connections. In it's tnsnames.ora the aliases are slightly more complex than normally expected:

db1234_medium = 
  (description= 
    (retry_count=20)(retry_delay=3)
    (address=
      (protocol=tcps)
      (port=1522)
      (host=adb.eu-frankfurt-1.oraclecloud.com)
    )
    (connect_data=
      (service_name=pxyz_db1234_medium.adb.oraclecloud.com)
    )
    (security=
      (ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
    )
  )

On the other hand, qgis (3.24.1) provides these input fields:
Name, Database, Host and Port define the connection endpoint. But there is no field available for additional details like protocol=tcps or ssl_server_cert_dn=.... Also the specific wallets location can not be provided. 

But it is possible to add all these details. To understand the method, it's required to understand how qgis simply concatenates the given strings to one connection URL which is then handed over to the Oracle client libraries. This client also accepts an Easy Connect String.  

With this information, I managed to generate these values: 

Database	pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin
Host	tcps://adb.eu-frankfurt-1.oraclecloud.com
Port	1522

Here are 3 important details: in the Database field, you can add information about the certificates DN and the wallet location. In the Host field, the prefix tcps:// defines the specific protocol used. 

This alltogether generates an URL

tcps://adb.eu-frankfurt-1.oraclecloud.com:1522/pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin

which is sufficient to connect to my ATP Database 
∎