It’s been a while since I’ve updated my blog. I feel guilty :-)

Lately, I’ve been using a lot of Python to do my Oracle research and I needed a way to do simple selects across multiple versions and platforms from the same IDLE shell. On top of that, I need to connect as SYSDBA. Using cx_Oracle is problematic because I cannot connect directly (not through the listener) to both 64 and 32 bit Oracle.

So, to solve this problem, I came up with a lame implementation of running queries using SQL*Plus and emulating the same interface of cx_Oracle results. I’m sure that many out there did something similar but it was easier to write than to search.

Here it is in all of its glory:

#!/usr/bin/env python
#
# Implement selects using SQL*Plus
# Author:  Slavik Markovich (http://www.sentrigo.com)
# Version: 1.0
# Date:    2008-12-13

import os
import sys
import subprocess

if ‘win’ in sys.platform:
win = True
else:
win = False

class OraSQLPlus(object):
def __init__(self, home, sid):
self.home = home
self.sid = sid
if win:
cmd = ‘sqlplus.exe’
else:
cmd = ‘sqlplus’
self.sqlplus = os.path.join(self.home, ‘bin’, cmd)

def getEnv(self):
env = os.environ
env['ORACLE_HOME'] = self.home
env['ORACLE_SID'] = self.sid
if not win:
env['LD_LIBRARY_PATH'] = os.path.join(self.home, ‘lib’)
return env

def runSelect(self, stmt):
p = subprocess.Popen([self.sqlplus, '-s', '/ as sysdba'],
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE,
env=self.getEnv())
(out, err) = p.communicate(‘set head off ver off lines 200 pages 0 feed off colsep |\n’ + stmt + ‘;\nexit\n’)
# Get lines and strip away the prefix and post-fix of SQL*Plus
lines = out.strip().split(‘\n’)
return [[col.strip() for col in line.split('|')] for line in lines]

def version(self):
res = self.runSelect(‘select banner from v$version’)
return res[0][0].split(‘ ‘)[-3]

Also, you can download the file here