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
}