How to use Oracle CX to perform SQL operations in Oracle Database

 The first step in order to use Oracle CX is to create a AWS Layer that can be use with our Lambda.

In this example I created the Layer called "cx_oracle", I configured my Lambda to use Python 3.9 and Architecture x86-64, As you can see in the following image:

 

 If you don't know how to create an AWS Layer for Python 3.9 you can check my previous article:

How to create a Lambda Layer for Oracle Database

NOTE: The following lines of code are for Python.

The first step is to import the library:

import cx_Oracle

Creating a Connection:

I am using Environment Variables in the Lambda to set the connection values, I have the following Env Variables:

• DB_USERNAME
• DB_PASSWORD
• DB_SERVER
• DB_PORT  
• ORACLE_SID

To create a new connection to the oracle database you can use the following line:

connection = cx_Oracle.connect(user=os.environ.get('DB_USERNAME'), password=os.environ.get('DB_PASSWORD'), dsn=os.environ.get('DB_SERVER')+':'+os.environ.get('DB_PORT')+'/'+os.environ.get('ORACLE_SID'), encoding="UTF-8")

Opening a cursor:

cursor_days = connection.cursor()

Submitting a SELECT statement to the Oracle Database:

cursor_days.execute("SELECT DISTINCT LOG_START_DATE ROW_DAY FROM SCHEMA01.TABLE_NAME where LOG_START_DATE < trunc(SYSDATE) ORDER BY 1")

Iterating the results:

rows_day = cursor_days.fetchall()
for ROW_DAY in rows_day:
    --Logic to handle each day--

Don't forget to close the cursor and connection:

cursor.close()
connection.close()