How to Connect to Oracle Database from an AWS Lambda using Thin mode

By Deiby Gómez

After exploring How to Connect to Oracle Database using AWS Lambda in Thick Mode, we now move on to Thin Mode.

With so many companies using AWS as their Cloud Provider, AWS Lambda is a popular solution to process Serverless Code. This guide was written to help people that need to work with Oracle Database using AWS Lambda.

For this scenario, I will use the following components:

Connector: oracledb
Lambda language: Python 3.10
Lambda Architecture: x86-64
Oracle Client: Oracle 21c x86-64
Oracle Database Target: Oracle Database 21c 
Connection mode: Thin

Using oracledb and Thin mode:

This is the most frequently recommended way to interact with Oracle Databases from AWS Lambda. It is easier to create the Lambda Layer and easier to establish a connection to Oracle database. Next, I explain the steps on how to use the “thin mode” for the oracledb:

Creating the Lambda Layer for Oracle Database:

The first step is to create the Lambda Layer. To do so, I usually follow these steps:

Create a Virtual Env:
$ python3 -m venv  oracle_layer

Check the version of Python:
$ python --version
Python 3.10.12

Activate the Virtual env:
$ source oracle_layer/bin/activate

Adding the libraries:

I have created layers in the past few years, and before I didn’t have to specify “--platform manylinux2010_x86_64”. I think something changed recently in the Linux machines used by the Lambdas. This is how I made it work:

$ cd oracle_layer
$ mkdir -p python
$ cd python
$ pip install --platform manylinux2010_x86_64 oracledb  
--only-binary=:all: --upgrade -t ./

Creating the zip for the layer:
$ cd ..
$ zip -r oracledb_layer.zip python

Deactivate the virtual env:
$ deactivate

Delete the virtual env:
$ cd ..
$ rm -rf oracle_layer

Creating the Lambda Layer in AWS

Go to the Lambda section in AWS and click on “Create Layer” button:

Specify the name for your Layer.

Select “Upload a .zip file” and select the zip we just created for the Layer and try to upload it, like I did in the following image.

Select architecture “x86_64”

Select runtime “Python 3.10”

Click on the “Create” Button.

Creating the Lambda

Now it’s time to create the lambda. Go to the Lambda section in AWS and click on the “Create function” button:

Specify the name for your lambda.
Select runtime “Python 3.10” – this should be the same version that we selected for our Layer.
Both versions should match.
Select architecture “x86_64” – this should be the same architecture that we selected for our Layer.
Both architectures should match.
Click on the “Create Function” button.

In the bottom part of your lambda configuration, you will find the section “Layers.” Click on the “Add a layer” button.

Now click on the option “Custom layers”
Select the Layer we just created,

in this article is “oracledb_layer”
Select the version we created, in this article is “1”.
Click on the “Add” button. 

Create the connection to Oracle Database
import oracledb

oracle_connection =  oracledb.connect(user='dgomez',
password='mypassword',   dsn='oracledbserver:1521/orcl')

cursor = oracle_connection.cursor()

cursor.execute('SELECT COL1, COL2 FROM TABLE')

for (col1,col2) in cursor:
print ("Oracle Row: "+str(col1)+ ","+ str(col2))

If you get the following error when you try to open a connection, even when you have confirmed that the user & password are correct, you will probably have to use the “tick mode”:
ORA-01017: invalid username/password; logon denied

Refer to my last article to see how to use “thick mode.”

When should you use thick mode?

• When you get the following error, even when you confirmed that all the credentials and connection string is correct: ORA-01017: invalid username/password; logon denied
  

This is common in a scenario where Oracle Database was upgraded to a most recent version, but the user you are using in the connection string still has an old version of the password. For example, your database is in 21c, but the password user is still in 10g. You can check the password version for your user with the following query:

select username,password_versions from dba_users where username = 'YOUR_USERNAME'

• When you need advanced features of Oracle in your connection.

Deiby Gómez is the first Oracle ACE Director of Guatemala (2015-2023). He holds 35 technology certifications, including the highest certifications for Oracle Database (Oracle Certified Master, OCM) and 3 Advanced Snowflake Certifications (Architect, Administrator, and Data Engineer). An engineer with 2 master’s degrees and a future lawyer, Deiby has been a speaker at technology events in more than 12 countries, including Oracle Open World in San Francisco, USA. He is the technical reviewer of the book Oracle Database 12cR2 Multitenant – Oracle Press and co-author of the book Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability – Oracle Press.