I needed to write a new script that was running on a Red Hat Linux 6 virtual machine and that would connect to databases using SQL*Plus. I was going to write a bash shell script but decided to use Python instead to see if I could do it using the Python that came with this version of Linux. I wont paste the entire script here but the key was to run SQL*Plus from Python instead of a shell script. Here is a simple example showing how I did it:
$ cat test.py import subprocess """ Example of running a sqlplus script from python 2.6.6. """ def run_sqlplus(sqlplus_script): """ Run a sql command or group of commands against a database using sqlplus. """ p = subprocess.Popen(['sqlplus','/nolog'],stdin=subprocess.PIPE, stdout=subprocess.PIPE,stderr=subprocess.PIPE) (stdout,stderr) = p.communicate(sqlplus_script) stdout_lines = stdout.split("\n") return stdout_lines sqlplus_script=""" connect test/test select * from dual; exit """ sqlplus_output = run_sqlplus(sqlplus_script) for line in sqlplus_output: print line
Here is the output:
$ python test.py SQL*Plus: Release 220.127.116.11.0 Production on Fri Nov 4 15:44:30 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> SQL> Connected. SQL> D - X SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
The function run_sqlplus takes a multi-line string as input. This is the text of a SQL*Plus script. It runs sqlplus /nolog to get a SQL*Plus prompt and then passes the strings in as the lines typed at the prompt. The function returns SQL*Plus’s output as a list of strings.
Anyway, this is just an example. I built a real script using these concepts. In this case we are using Python 2.6.6 without cx_Oracle installed so I couldn’t connect directly to an Oracle database. Instead I just ran SQL*Plus from Python.