Connecting Oracle with Python via cx_Oracle extension

Installation Prerequisites

• Python 2.7, 3.5 or above
• Oracle Client / Oracle Instant Client "Basic" or "Basic Light"
• libaio / libaio1 package

Installation Reference

• cx_Oracle
• Installation guide

Sample Python Code

import cx_Oracle

# Make DSN address. Real values for host address, port and service name were replaced by '<>'.
dsn = cx_Oracle.makedsn('<host_address>', '<port>', service_name='<service_name>')

# Connect to DSN
orcl = cx_Oracle.connect(user='<username>', password='<password>', dsn=dsn)

# Get cursor
cursor = orcl.cursor()

# Execute SQL statement
cursor.execute('<SQL SELECT STATEMENT>')

# Fetch result
data = cursor.fetchone()

Column aggregation in Oracle

Suppose we have a table (TBL) in Oracle with columns (CATEGORY, ATTR). Consider the following:

CATEGORY	ATTR
1	A
1	B
1	C
2	D
2	E
2	F
3	X
3	Y
3	Z

We want to convert the table as follows:

CATEGORY	ATTR
1	A,B,C
2	D,E,F
3	X,Y,Z

This can be done via the SQL below:

select CATEGORY, 
       xmlagg(xmlelement(c, ATTR || ',')).extract('//text()') 
from TBL 
group by CATEGORY