Connect Oracle DB from an AWS Lambda Using Thick Mode

By Deiby Gómez

Many companies use AWS as their Cloud Provider to implement solutions, and AWS Lambda is a popular choice for processing Serverless Code. In a world where data is king, it’s common to need to pull or push from Oracle Databases. For that reason, I wrote this guide to help anyone that needs 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: Thick

Using oracledb and Thick mode:

Thick mode is my second choice when I have to create connections to Oracle Database, because it requires additional libraries to create the layer, which increases the risk to exceed the maximum MBs allowed by AWS for a Layer (250 MBs).

When Should You Use Thick Mode?

1. When you get the following error, even when the credentials and connection strings are correct:

ORA-01017: invalid username/password; logon denied

This is extremely common in the scenario where the Oracle Database was upgraded to a more 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 what’s the password version for your user with the following query:

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

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

Downloading the Oracle Client Library:

Visit the official website for Oracle Clients and select the version you are interested in, just don’t select the versions for “OL9/RH9 only” because we want to use this Oracle Client in a Lambda.

A screenshot of a computer Description automatically generated

I have selected the 21c version. I suggest selecting the “Basic Light Package (ZIP)” as this is the most basic Oracle client and ensures the smallest zip file. This is important when we are working with lambdas, because Lambdas have a limitation for libraries of 250 MB (unzipped).

Creating the Lambda Layer for Oracle Database:

First, create the Lambda Layer by following 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

Add the libraries:
I have created layers in the last few years, and previously 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

Installing oracledb:
$ pip install --platform manylinux2010_x86_64 oracledb --only-binary=:all: --upgrade -t ./

Transfer the Oracle Client Zip file to the python folder:
cp /downloads/instantclient-basiclite-linux.x64-21.15.0.0.0dbru.zip oracle_layer/python

Your python folder should look like the following output:
$ ls -1

cffi cffi-1.15.0.dist-info _cffi_backend.cpython-310-x86_64-linux-gnu.so cffi.libs cryptography cryptography-37.0.4.dist-info instantclient-basiclite-linux.x64-21.15.0.0.0dbru.zip oracledb oracledb-1.0.1.dist-info pycparser pycparser-2.22.dist-info

Unzip Oracle client libraries:
$ unzip instantclient-basiclite-linux.x64-21.15.0.0.0dbru.zi

Delete the zip file:
$ rm -rf instantclient-basiclite-linux.x64-21.15.0.0.0dbru.zip

Install libaio:
$ sudo apt-get install libaio1

Copy the library “libaio.so.1” into the oracle client folder:
$ cp /usr/lib/x86_64-linux-gnu/libaio.so.1.0.1 instantclient_21_15/libaio.so.1

Your python directory should now look like this:
$ ls -1 cffi cffi-1.15.0.dist-info _cffi_backend.cpython-310-x86_64-linux-gnu.so cffi.libs cryptography cryptography-37.0.4.dist-info instantclient_21_15 oracledb oracledb-1.0.1.dist-info pycparser pycparser-2.22.dist-info

And the libaio library should be inside the oracle client folder:
$ ls instantclient_21_15/libaio.so.1 instantclient_21_15/libaio.so.1

Creating the zip for the layer:
It’s important that you specify the value “–symlinks” to keep the zip file as small as possible, remember that we have a size limitation for our libraries in Lambdas.
$ cd .. $ zip --symlinks -r oracledb_layer.zip python

If you do not use “–symlinks”, look at impact on the zip file:

With “–symlinks”:
$ du -sh oracledb_layer.zip 52M oracledb_layer.zip
Without “–symlinks”:
$ du -sh oracledb_layer.zip 227M oracledb_layer.zip

Deactivate the virtual env:
$ deactivate

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

2. 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. If you receive any error about the zip size, then you should first upload the zip file to a S3 bucket, and then select the option “Upload a file from Amazon S3”, like I did in the following image.

Select architecture “x86_64”

Select runtime “Python 3.10”

Click on the “Create” Button.

Creating the Lambda

Create 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.

Creating Environment Variables for Lambda

The lambda is not able to find and load the Oracle client libraries by default, we have to tell the lambda where those libraries are located, including the libaio library as well. To do so, we will create one environment variable called “LD_LIBRARY_PATH” and include the directory of our oracle client into the path.

In the lambda configuration click on “Environment Variables” in the left menu and click on “Edit” button:

Add the variable and the path to find the libraries:

Opening the Connection to Oracle

It’s important to include the line “oracledb.init_oracle_client()” – this is actually the line that activates the “thick mode.”

import oracledb

oracledb.init_oracle_client()

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))

For more guidance, check out our Snowflake Series and links.

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.

Is your organization adapting to remain competitive? In The Architect Room, we design a roadmap for the future of your digital organization, while capitalizing on current technology investments. Our knowledge spans a variety of offerings across all of the major public cloud providers. Visit Red Pill Analytics or reach out on Twitter, Facebook, and LinkedIn.

How to Connect to Oracle Database from an AWS Lambda Using Thick Mode

About The Author

Red Pill Analytics