Connecting to Oracle RDS

Question

I am trying to connect to Oracle using AWS lambda and python.

These are the step I followed. (Everything has done EC2 instance)

1. Downloaded the instantclient-basic-linux.x64-12.2.0.1.0.zip and
   instantclient-sdk-linux.x64-12.2.0.1.0.zip
2. Created this folder structure ~/lambda/lib/
3. Extracted the zip files in the ~/lambda/lib/
4. copied the libaio.so.1.0.1 from /lib64/ into ~/lambda/lib/
5. Created symbolic link of libaio.so.1.0.1 as libaio.so in ~/lambda
6. using pip installed cx_Oracle in ~/lambda
7. written below index.py script in ~lambda

`

import cx_Oracle

def handler(event, context):
    message = ""
    cursor = None
    connection = None    
    try:
        connection = cx_Oracle.connect("USERNAME", "PASSWORD", "DOMAIN/orcl")
        cursor = connection.cursor()
        cursor.execute("""QUERY""")
    except Exception as e:
        message += " {Error in connection} " + str(e)
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()
    return {'message' : message}

`

8. Then zipped it using zip -r9 ~/upload.zip *

After running the code on AWS lambda it gives the following error.

Error while trying to retrieve text for error ORA-01804

I tried setting ENV ORACLE_HOME=/var/task and /var/task/lib but did not worked

I looked below answers but did not find help yet

Error while trying to retrieve text for error ORA-01019

Oracle with node-oracle: Error while trying to retrieve text for error ORA-01804

Canst

Also related; #12838311 – Spleenwort

The answers there are not descriptive and they don't help in solving the problem. Is this because of some VPC issues – Canst

Answer 1

I solved this by simply zipping the files properly with the symbolic links

First I created three symbolic links (with respect to above dir structure):

ln -s ./lib/libaio.so.1.0.1 ./lib/libaio.so.1

ln -s ./lib/libaio.so.1.0.1 ./lib/libaio.so

ln -s ./lib/libaio.so.1.0.1 ./libaio.so.1.0.1

ln -s ./lib/libclntsh.so.12.1 ./lib/libclntsh.so

then i was zipping it incorretly I did it like this:

zip --symlinks -r9 ~/lamda.zip *

it worked! properly then.Hope it helps somebody.

Canst

I had to provide the complete path /var/task/lib/libaio.so.1.0.1 and then it worked – Crackle

Thank you! I tried many approaches to this, but yours worked like a charm. No need to set env vars or fiddle with host files. – Hover

Answer 2

This post was much helpful for me to use cx_Oracle using Lambda functions. It worked as expected. Thanks for creating this post @Sayed Zainul Abideen

But i got another error from lambda stating 'cx_Oracle.DatabaseError: ORA-24454: client host name is not set'

I got it resolved by adding the below lines in my python code:

import os

with open('/tmp/HOSTALIASES', 'w') as hosts_file:
    hosts_file.write('{} localhost\n'.format(os.uname()[1]))

After which i added below ENVIRONMENTAL VARIABLE to my Lambda function:

HOSTALIASES = /tmp/HOSTALIASES

Hope it helps somebody.

Please add comments if any issues relating to cx_Oracle. I would be happy to help as i struggled a lot to fix this.

Argal

Thanks this did help. I can't help but feel there must be a better way, but this worked. – Chomp

I am using a similar setup, but with nodejs lambda using oracledb_for_lambda. It always throws ORA-21561: OID generation failed. The lambda functions are within a VPC and connects over to the on-prem oracle database(RAC Cluster). Connectivity is verified though. – I