Trying to get AWS lambda to connect to oracle, is hard. Like knocking your own teeth out with a rock, Hey Wilson!

This is the long explanation… https://www.youtube.com/watch?v=FOpqiZI4KXg

But, he also provides a download

https://drive.google.com/file/d/1XU9tRWJsz3O64_JA3vzj3xKon4r_75xJ/view

Copy here https://github.com/mellerbeck/cx_Oracle_aws_lambda

If you don’t want to mess with the tnsnames.ora file just delete the network folder and use the Easy Connect syntax

https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html

Easy Connect Syntax for Connection Strings

An Easy Connect string is often the simplest connection string to use for the data source name parameter dsn of cx_Oracle.connect() and cx_Oracle.SessionPool(). This method does not need configuration files such as tnsnames.ora.

connection = cx_Oracle.connect(user="hr", password="userpwd",
                               dsn="dbhost.example.com:1984/orclpdb1",
                               encoding="UTF-8")

But, please put the credentials into SSM or secrets manager. If your DB is in a VPC you need to configure the VPC for your lambda.

So, to use it as a layer. Create a folder named python and copy everything into it. Zip that folder up, and load it as a lambda layer.

One last step. As it is, the lambda won’t be able to find the libraries.

So from here https://aws.amazon.com/blogs/database/automating-file-transfers-to-amazon-rds-for-oracle-databases/

# When creating the Lambda function, ensure the following setting for LD_LIBRARY_PATH # LD_LIBRARY_PATH = /var/lang/lib:/lib64:/usr/lib64:/var/runtime:/var/runtime/lib:/var/task:/var/task/lib:/opt/lib:/opt/python

Ok, quick sample lambda

import cx_Oracle

def lambda_handler(event, context):

    try:
        connection = cx_Oracle.connect(user="auser",password="apassword",dsn="adsn",encoding="UTF-8")
        cursor = connection.cursor()
        cursor.execute("SELECT * from something")
        rows = cursor.fetchall()
        for row in rows:
            print(row)
    except Exception as e:
        message += " {Error in connection} " + str(e)
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

    return {
        "statusCode": 200,
        "body": context.invoked_function_arn
    }