cx_Oracle LDAP Connection String syntax

Question

With JDBC, we can use the following syntax to connect to an Oracle database over an LDAP connection:

jdbc:oracle:thin:@ldap://host:1234/service_name,cn=OracleContext,dc=org,dc=com

How can I connect over LDAP using cx_oracle?

Answer 1

I ended up going with jaydebeapi.

import pandas as pd 
import jaydebeapi
import jpype
import os
import sys

def run(f_name,command,username,pw ):
    jar='ojdbc8.jar'
    args = '-Djava.class.path=%s' % jar
    jvm_path = jpype.getDefaultJVMPath()
    jpype.startJVM(jvm_path, args)
    con = jaydebeapi.connect("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@ldap://server.prod.company.com:3060/service,cn=OracleContext,dc=prod,dc=company,dc=com",[username, pw], jar)
    try:
        df= pd.read_sql(command,con)
        df.to_excel(f_name)
        print(df)
    except Exception as e:
        print(e)
    finally:
        con.close()



def Run_Program(myvars):
    os.chdir(sys._MEIPASS)    
    f_name = myvars.MyFileName
    command = myvars.plainTextEdit_CSVString.toPlainText()
    username = myvars.lineEdit_UserName.text()
    pw = myvars.lineEdit_Password.text()
    run(f_name,command,username,pw )

Saving the ojdbc8.jar file from Oracle Client in the same folder and specifying the location in the code. And also downgrading the module JPype1 to JPype1==0.6.3 (its installed as a requirement for jaydebeapi )

This worked well for packaging using pyinstaller so that it could be shared. (i created a pyqt5 UI for user to use.

Answer 2

Here are my two cents using Python 3.7 and cx_Oracle v.8.2.0 on Win 10.

I wanted to issue queries to an Oracle database using Python, and what I already had was :

• a username (or schema)
• a password
• a JDBC connection string that looked like: jdbc:oracle:thin:@ldap://[LDAPHostname1]:[LDAPPort1]/[ServiceName],[DomainContext] ldap://[LDAPHostname2]:[LDAPPort2]/[ServiceName],[DomainContext] where the [DomainContext] was of the form cn=OracleContext,dc=foo,dc=bar

First, you have to install cx_Oracle by following the Oracle documentation.

Note that:

• cx_Oracle requires a series of library files that are part of the Oracle Instant Client "Basic" or "Basic Light" package (available here). Let's say we unzip the package under C:\path\to\instant_client_xx_yy
• Depending on the platform you're on, some other requirements are to be filled (like installing some Visual Studio redistributable on Windows)

For the LDAP part, there are two configuration files that are required:

• sqlnet.ora : This is the profile configuration file for Oracle, but mine was simply containing :

  NAMES.DIRECTORY_PATH = (LDAP)
  

  It tells the library to resolve names using LDAP only.

• ldap.ora : This file tells where to look for when resolving names using LDAP. I knew I was accessing two OID servers, so mine was of the form :

  DIRECTORY_SERVERS=([LDAPHostname1]:[LDAPPort1], [LDAPHostname2]:[LDAPPort2])
  DEFAULT_ADMIN_CONTEXT="dc=foo,dc=bar"
  DIRECTORY_SERVER_TYPE=oid
  

  Important Note : I had to remove the cn=OracleContext from the DEFAULT_ADMIN_CONTEXT entry in order to make the name resolution work

  Let's say those two files were saved under C:\path\to\conf

And now comes the Python part. I used the cx_Oracle.init_oracle_client() method in order to point to the library and configuration files. (Note that there are other ways to give cx_Oracle access to those files, like setting environment variables or putting those in predefined places. This is explained under the install guide)

Here is a little sample code:

import cx_Oracle

# username and password retrieved here

cx_Oracle.init_oracle_client(lib_dir=r'C:\path\to\instant_client_xx_yy', config_dir=r'C:\path\to\conf')

try:
    with cx_Oracle.connect(user=username, password=password, dsn='[ServiceName]') as connection:
        cursor = connection.cursor()
        cursor.execute('SELECT * FROM ALL_TAB_COLUMNS')
        # Outputs tables and columns accessible by the user
        for row in cursor:
            print(row[1], '-', row[2])
        cursor.close()

except cx_Oracle.DatabaseError as e:
    print("Oracle Error", e)