A Python recipe to check automatically all connections configured in your Oracle SQL Developer

check_connections.py

import time
import sys
import cx_Oracle as db
from pyDes import des, PAD_PKCS5, CBC
from lxml import etree

def to_bytes(pas):
    return [ int(pas[i: i + 2], 16) for i in range(0,len(pas),2)]

def decrypt_ora(result):
    result = to_bytes(result)
    constant = result[0]
    assert( constant == 5)
    key = result[1:9]
    encryptedPassword = result[9:]
    key = "".join(map(chr,key))
    k = des(key, CBC, "\0\0\0\0\0\0\0\0", pad=None, padmode=PAD_PKCS5)
    encryptedPassword = "".join(map(chr,encryptedPassword)) 
    return k.decrypt(encryptedPassword)
        
def get_con(fn):
    parser = etree.XMLParser(remove_blank_text=True)
    C = []
    root = etree.XML(open(fn).read(), parser)
    for e1 in root:
        c = {}
        for e2 in e1:
            if e2.tag == "RefAddresses":
                for e3 in e2:
                    key = e3.attrib["addrType"]
                    if key in ("user", "serviceName", "port", "password","hostname","customUrl","ConnName"):
                        c[key] = e3[0].text
        c["password"] = decrypt_ora(c["password"])
        C.append(c)
    return C            

def make_connect_string(c):
    return """%s/%s@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=%s)(PORT=%s)))(CONNECT_DATA=(SERVICE_NAME=%s)))""" % (c["user"],c["password"],c["hostname"],
                            c["port"],c["serviceName"])
    
def test_con(constr):
    try:
        con= db.Connection(constr)
        cursor = con.cursor()
        print con.version
        R = cursor.execute("select sysdate from dual")
        cols = [x[0] for x in cursor.description]
        print cols
        for r in R:
            print r
        return True;
    except db.DatabaseError, exc:
        error, = exc.args
        print "Oracle-Error-Code:", error.code
        print "Oracle-Error-Message:", error.message
    return False

if __name__=="__main__":
    C = get_con(r"C:\Users\howfun\AppData\Roaming\SQL Developer\system3.0.04.34\o.jdeveloper.db.connection.11.1.1.4.37.59.31\connections.xml")
    for c in C:
        print c["ConnName"]
        cs = make_connect_string(c)
        if not test_con(cs):
            print "Error"
        else:
            print "Ok"