Python/Oracle Connectivity

cx_oracle

Install

python -m pip install cx_Oracle --upgrade

Collecting cx_Oracle

Downloading cx_Oracle-6.0.1-cp36-cp36m-win_amd64.whl (138kB)

100% |████████████████████████████████| 143kB 2.6MB/s

Installing collected packages: cx-Oracle

Successfully installed cx-Oracle-6.0.1

Making a connection

import cx_Oracle


# If needed, place an 'r' before any parameter in order to address any special character such as '\'.

# For example, if your user name contains '\', you'll need to place 'r' before the user name: user=r'User Name'


dsn_tns = cx_Oracle.makedsn('myhost', 'myport', service_name='mySID')

con = cx_Oracle.connect(user=r'myuser', password=r'mypassword', dsn=dsn_tns)


print (con.version)

con.close()

If you save this is OracleTest.py and run it, you should get this output...

python OracleTest.py

12.1.0.2.0

Calling Stored Procedures

import cx_Oracle


p_parameter1 = 'param1'

p_parameter2 = 9999


try:

# Create a connection

db = cx_Oracle.Connection("myuser/mypassword@//myhost:myport/mySID")

# Create a cursor

cursor = db.cursor()

# Call stored procedure

cursor.callproc("schema.procedure", [ p_parameter1, p_parameter2])


except cx_Oracle.DatabaseError as e:

error, = e.args

print(error.code)

print(error.message)

print(error.context)

# Close cursor and connection.

print('Closing cursor & db')

cursor.close()

db.close()