Oracle DB 23ai Free Connection Pool in Python

I describe how to connect to Oracle DB from Python. I explain why DB connection pool is important for better performance. Connection is done through thin oracledb mode, without installing Oracle Client.

 

FastAPI and Oracle DB Client in Docker

I describe how to dockerize Oracle DB Client with FastAPI and Uvicorn. The end result - you will be able to connect to Oracle Cloud DB and expose REST services through FastAPI in Python.

 

Oracle Database Docker Image in Docker Cloud (Digital Ocean)

Red Samurai is using Docker for our internal development environment. We are running Oracle Database and WebLogic server (with ADF support) on Docker Cloud. It is much easier to manager Docker containers than to maintain our own server.

In this post I will describe how to setup Oracle Database in Docker Cloud. Of course you could run Docker container locally on your machine, but main power of Docker comes with the option to be able to run container in the Cloud.

If you are new to Docker and Docker Cloud, it is very easy to be lost - there are so many various blogs and articles, hard to understand from where to start. I would recommend to start from Get Started, Part 1: Orientation and setup tutorial. You will learn most of the important Docker commands and push Docker image to the registry. Next check this tutorial - Create your first service. It describes how to create Cloud Node through Docker Cloud interface. Keep in mind - Docker itself doesn't run Docker Container, it provides connectors to various Cloud providers for Docker - Azure, Amazon, Digital Ocean, etc. I did some research and it looks like Digital Ocean is the best option to run Docker container - pricing model is straightforward, registration and management processes are simple and clear.

First step should be to create Docker Node in Docker Cloud (you must be connected to Cloud provider). Here is the info for our Digital Ocean node instance:

Node creation process is straightforward. Once node is created, it becomes part of Node cluster:

Docker Cloud node creation process triggers droplet (Cloud instance) creation in Digital Ocean. This process takes around 5 minutes in total.

Digital Ocean provides very nice UI to manage and monitor droplet:

Graphs to monitor droplet performance:

Digital Ocean provides option to run administration console directly in the browser, without even using separate SSH connection (this is very convenient for quick administration).

I found it to be the easiest way to create Oracle DB docker image in Digital Cloud droplet by executing docker run command directly in droplet administration console. I'm using official Oracle Database Enterprise Edition Docker image.

With docker run command, i can pull and run Docker image (referencing official Oracle DB Docker registry). Where detach=true means container will run without blocking console:

docker run --detach=true --name RedSamuraiDB -p 1521:1521 -p 5500:5500 -e ORACLE_SID=RedSamuraiDB -e ORACLE_PDB=ORCLPDB1 store/oracle/database-enterprise:12.2.0.1

Command is executed from Digital Ocean droplet console:

Image is downloaded directly from Docker Store into Digital Ocean droplet:

With docker run command, image is not only downloaded, but also container is started. We can execute docker ps -l to see if container was started successfully:

Finally we need to reset default password (Oradoc_db1) set for Oracle DB Docker container. This can be done by logging into sqlplus from Digital Ocean droplet console. First we need to enter into Docker container prompt by executing (use Docker container name):

docker exec -it RedSamuraiDB bash

Execute:

ALTER USER SYS IDENTIFIED BY newpass
ALTER USER SYSTEM IDENTIFIED BY newpass

DB is accessible from outside:

This brings power of Docker - Oracle DB setup in few minutes.

Simple Way to Export Your Data from Oracle Cloud

You should not get stuck in the Cloud. There are various options to create Oracle Cloud backup, but is very important to keep a local copy of your data. One of the simplest options to create a local copy of data from the Oracle Cloud - use Oracle SQL Developer.

Define Oracle Cloud DB connection in SQL Developer (the same as regular DB connection):

Use Database Export utility from Oracle SQL Developer:

Allows to export schema DDL, together with data (various formats, SQL INSERT statements one of them):

You can choose from long list of DB objects to export, this includes indexes, triggers, constraints, tables, etc.:

In my use case I select all objects to export (except PS_TXN):

There is option to filter exported data, again I will export all data:

Schema structure along with data is exported successfully:

Different Approach for DB Constraint Error Handling in ADF

Let's be honest - no matter how developer friendly and stable validation rules support would be in ADF BC, there will be always use cases when validation logic will be executed directly in DB, by check constraints for example. There is one problem in ADF, when validation logic is executed by DB check constraints. As there will be error received in doDML, while posting row and violating check constraint - transaction will stop and no other edited rows will be verified, until currently failed row data will be fixed. I will explain in this post, how to bypass such behaviour and have to report failed rows to ADF UI.

Key part to understand - there are two transient attributes defined on VO level. These two should be defined on VO level, if you would define them on EO level, transient attributes from EO level would not be included into passivation and values would be lost. One attribute is used as a flag to indicate DB constraint error for current row and other attribute keeps error text:

Both attributes are set to be included into passivation cycle. This would not be possible for transient attributes defined on EO level:

Method doDML is overriden on EO level to catch error from DB check constraint. In this example, I'm processing only Salary > 0 DB check constraint. In case of error from DB, error text is saved into ErrorText transient attribute from current row. ErrorIndicator is set to be 1, if error happened, and 0 otherwise. I'm getting current VO row for the current EO, by accessing it from root AM:

On UI side, we add Partial Trigger to the surrounding Panel Collection from Save/Cancel buttons. This will ensure error code and error text for the affected rows will be updated:

There are client and server listeners defined for the table to enable row double click support, this is how error message will be displayed to the user (instead of annoying popup showing up constantly):

Error message from currently selected row is retrieved and displayed through Faces message. I'm checking if error exists for the current row and only then display it - quite straightforward:

Here is example, where user was trying to submit changes in three rows, all set with negative salary. Of course DB check constraint for positive salary failed and we can see failed rows highlighted in red. You can double click on row highlighted in red and then error text popup will be displayed:

Let's fix salary in the second failed row to be positive and press Save again. Two rows will remain in failed state, but data from the fixed row will be saved to DB successfully:

We can double click on the first failed row to see the error message:

Fix salary to be positive in the first row and commit again - only one row with error will remain in pending state:

Download sample application - DoDMLExceptionProcessingApp.zip.