Connecting to Oracle Cloud database with Deepnote

DatabaseError: DPI-1047

Connecting to an Oracle Cloud Database from Deepnote can be challenging, particularly due to the requirement for the Oracle Client libraries. We will guide you through the process of setting up the connection using cx_Oracle, and provide solutions to a common issue: DPI-1047 error.

Problem statement

DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: 
"[libclntsh.so](<https://libclntsh.so/>): cannot open shared object file: No such file or directory".

This error indicates that the required Oracle Client library is not found in the environment.

Solution

pip install cx_Oracle

Set up the Oracle client libraries

import os
os.environ['LD_LIBRARY_PATH'] = '/path/to/your/instantclient'

Connect to the Oracle Cloud database

import cx_Oracle
import pandas as pd
import json

with open('config_DB.json') as f:
    credentials = json.load(f)

host = credentials.get("Hostname")
port = credentials.get("Port")
service = credentials.get("Service Name")
username = credentials.get("Username")
password = credentials.get("Password")

dsn = cx_Oracle.makedsn(host, port, service_name=service)
connection = cx_Oracle.connect(username, password, dsn, encoding="UTF-8")

query = "SELECT * FROM your_table"
df = pd.read_sql(query, con=connection)

print(df.head())

Conclusion

By following these steps, you should be able to resolve the DPI-1047 error and successfully connect to your Oracle Cloud Database from Deepnote. If you encounter further issues, please get in touch with our support. Happy querying in Deepnote!