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:
Here is the output:
$ python test.py SQL*Plus: Release 12.1.0.2.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 12.1.0.2.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.
Bobby
P.S. I edited this on 2/12/2018 to make it work with both Python 2 and 3. I added the encode and decode and made the print statement use parentheses.
P.P.S. Here is similar code on GitHub: runutils.py
Thanks man.. Helped me 🙂
Great! Thank you for your comment.
Bobby
Great starting point for scripting python. Thanks!
Thanks!
Thank you! It helped me! I struggled trying to make something work for our custom application with just popen and communicate. This just opened the application and did nothing after that. Adding multi-line strings did the trick. Thanks a lot!
Great! I am glad that it was helpful to you.
Bobby
Thanks Bobby, it helped a lot, as i was struggling to install cx_Oracle on our server.
Also, is there anyway that i can fetch only the query output from print instead of the output with the version and other things??
Regards,
Naren Vema
I have been using a prefix in the query output so that I can find the row output and ignore the other lines. For example:
This uses “SCN:” as a prefix to the row of output so I can find it. If you can get cx_Oracle working you will have a lot more flexibility in using the returned data.
Bobby
If you start sqlplus with the ‘-s’ (silent) switch, it will suppress the version banner.
In your sql script, you should use sqlplus SET options to suppress other unwanted elements that are normally in the output. As a start, try:
— Do not show column headers or “# of rows”
set head off feedback off
— No page breaks and wide terminal to prevent inadvertent line wraps,
— AND do not pad with trailing spaces which sqlplus does by default
set pages 0 lines 300 trimspool on trimout on
Hope that helps.
Thanks really helped me wanted to convert my shell scripts to Python on production databases but was not allowed to install CX_ORACLE this will be great.
Glad that it was helpful.
Bobby
Hi Bobby,
Thanks , your solution helped me a lot.
I am trying to provide input through console to get the o/p from db but unable to get the answer.
what is wrong in this code ?
I am accessing all_db_links table;
#!/usr/bin/env python2
import subprocess
from subprocess import Popen, PIPE
value = raw_input()
#function that takes the sqlCommand and connectString and returns the queryReslut and errorMessage (if any)
def runSqlQuery(sqlCommand, connectString):
session = Popen(['sqlplus', '-S', connectString], stdin=PIPE, stdout=PIPE, stderr=PIPE)
session.stdin.write(sqlCommand)
return session.communicate()
connectString = 'username/password@db_name'
sqlCommand ="""SET HEADING OFF
(select * from all_db_links where OWNER='?' ,(value) )
"""
queryResult = runSqlQuery(sqlCommand, connectString)
#print(queryResult)
#'SELECT * FROM stocks WHERE symbol=?', t
for i in queryResult:
print(i)
I am getting nothing in the o/p and no error as well.
This works for me:
Bobby
Hello Bobby,
I was happy to find your solution. However, I get this error ( I basically did a copy/paste of your script).
File “/usr/lib/python2.7/subprocess.py”, line 1343, in _execute_child
raise child_exception
OSError: [Errno 2] No such file or directory
What to do?? Thanks 🙂
Thanks for your comment. My guess is that sqlplus is not in your path. I would go to a windows command prompt and type sqlplus and see if it finds it. You could look at your PATH variable to see if it contains the Oracle client bin directory which is where sqlplus should be.
Bobby
Thanks for your reply. I get this error message in online compilers/intepreters and when I run the script on my Linux box!
It is the same in Linux as in Windows. What happens when you run sqlplus from the Linux command line?
Here is what I get when I get the error on Linux with a bogus program name:
$ python test.py
Traceback (most recent call last):
File "test.py", line 3, in
session = Popen(['asdfadf','-S','x/y@d'], stdin=PIPE, stdout=PIPE, stderr=PPE)
File "/usr/lib64/python2.6/subprocess.py", line 642, in __init__
errread, errwrite)
File "/usr/lib64/python2.6/subprocess.py", line 1238, in _execute_child
raise child_exception
OSError: [Errno 2] No such file or directory
<1pldrtsddb02>
$ cat test.py
from subprocess import Popen, PIPE
session = Popen(['asdfadf','-S','x/y@d'], stdin=PIPE, stdout=PIPE, stderr=PIPE)
Thanks, Bobby! You were right. I tried calling python script on remote server with SSH. I forgot to su – oracle 🙂
Hi Bobby,
do you have this on git ?
apologies, it if was obvious and seems to be. I just can’t find the link
Thank you for the question. I do have the same code on GitHub. I have added a link to it at the end of the post.
Here is the link. runutils.py
Bobby
you’re the man Bobby.
same code for you is not same for me . the one I saw on git is more generic for any kind of CMD line enrtry.
The one above is more SQLPLUS specific…. for the newbs like me, it’s a big difference.
you’re just too advanced so both scripts are simply “same” to you.
I noticed the difference after I said it was the same but my blog was too slow for me to get back in and fix it. Not sure what the deal is. I might need an upgrade. Sorry about the confusion but I’m glad that you find it helpful.
Bobby
Hi Bobby,
How do I check if the script was executed successfully or there were errors?
I want to do some actions if the sql script failed.
You can do the same thing I did with stdout with stderr. Maybe add something like this:
(stdout,stderr) = p.communicate(sqlplus_script.encode('utf-8'))
stdout_lines = stdout.decode('utf-8').split("\n")
stderr_lines = stderr.decode('utf-8').split("\n")
In my case it simply returns, “session communicate is (b”, b”)” Any idea why?
Thanks for your comment. I am not sure what you mean. Maybe you could post the output? Maybe you are saying that session.communicate returns (b”, b”)? If so that probably means that whatever you ran did not return any output on stdout or stderr.
is there a way you can set the echo on ? i have tried setting it multiple ways and it doesnt seem to take effect
Thank you for your comment. That is a good question. The code in this post just returns the output of the sqlplus command. But to see the echoed SQL statements you need to spool the output to a file and then cat it out. There are more elegant ways to do it I am sure, but here is a simple example that includes both the sqlplus output without the echoed command and the spooled output with the echoed commands:
Bobby
Hi Bobby,
I copy pasted the above script and I still get no output. The script doesn’t fail but it doesn’t seem to be doing anything either. My end goal is to use this script to run .sql files against a database. I’m using Oracle 12c and python 3.8 on a windows 10 machine
The only way I was able to run the above script and get no output was to replace sqlplus with the name of a script that did nothing. Maybe you have something in your path besides the real sqlplus executable? You could replace sqlplus with the full path to sqlplus.exe such as C:\\app\\myuser\\product\\11.2.0\\client_1\\bin\\sqlplus.exe.
If it actually runs sqlplus you will get output unless you run it with the -s (silent) option.
Bobby
i was able to get it to work with the full path for sqlplus and by passing the connection string as a parameter to Popen. Thank you so much for your help, Bobby. I spent a good amount of time trying to get this to work and therefore, I really appreciate your help
That is great! Thank you for your comment.
Bobby
Thanks a ton . this worked. i did a compare and trial/error from what you posted and figured out the termout on setting made the difference.
Sounds like I didn’t exactly answer your question but it worked out anyway! Thanks for the update.
Hi Bobby, how do I catch an sql error as an exception here in this code example
I check for either errors or the expected result.
Here is code from a real script:
It is similar to what we do with a Unix shell script where we run sqlplus and then look for errors in its output.
Bobby
Hi Bobby, I get an ORA-12154: TNS: could not resolve the connect identifier specified. My connection scrpit is:
sqlplus_script=”””
connect ciafis/afis@GI0040FE001.goc.local/pdbafis1
select syscardno from mv_tpdata_t_105 where afis_tag=105 and tag_val= GI0016PC059000021;
exit
“””
I think it is because of ‘@’ character in my password. I could not find any solution.
Thank you for your help! 🙂
I have not tried a password with an @ in it but this post says you can put quotes around it:
https://serverfault.com/questions/163006/sqlplus-connect-command-with-an-symbol-in-the-password
Thanks for your comment!
Bobby
Hi Bobby, this is really helpful, is it compulsory to install oracle client for this to work? Because I keep getting “FileNotFoundError: [Errno 2] No such file or directory: ‘sqlplus’: ‘sqlplus'”, like someone mention above, I’m not sure what I’m doing wrong, thanks in advance.
Thanks for your comment. Yes, this post assumes that you have the Oracle client installed and in your path so that it can run sqlplus.
Bobby
Hi Bobby, i am having problem when I am executing multiple grants from a sql script with this program and I am getting below error:
*\r\nERROR at line 1:\r\nORA-00933: SQL command not properly ended\r\n\r\n\r\n’, b”)
I am not sure without seeing your script. My guess is that you do not have newlines between your SQL statements. For example:
SQL> select * from dual;select * from dual;
select * from dual;select * from dual
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Hi Bobby,
I copied the same code what you gave and pasted in my github and tried calling from Jenkins build but getting below error. when i call in local it is absolutely working fine. Other python scripts are executing fine from Jenkins only this causing issue. So please suggest.
ERROR:
C:\Program Files (x86)\Jenkins\workspace\Track_decl_python>python track_decl.py Traceback (most recent call last): File “track_decl.py”, line 32, in sqlplus_output = run_sqlplus(sqlplus_script) File “track_decl.py”, line 19, in run_sqlplus p = subprocess.Popen([‘sqlplus’,’/nolog’],stdin=subprocess.PIPE, File “C:\Users\vabog2\AppData\Local\Programs\Python\Python38\lib\subprocess.py”, line 854, in __init__ self._execute_child(args, executable, preexec_fn, close_fds, File “C:\Users\vabog2\AppData\Local\Programs\Python\Python38\lib\subprocess.py”, line 1307, in _execute_child hp, ht, pid, tid = _winapi.CreateProcess(executable, args, FileNotFoundError: [WinError 2] The system cannot find the file specified
It looks like it cannot find sqlplus. Maybe sqlplus is not in the path when you run the Python script through Jenkins? You could try dumping out the PATH environment variable in a script run through Jenkins and compare it to the PATH when you run it outside Jenkins. Just a guess.
Bobby
I was struggling with oracle scripting with Python and subprocess module. Your webpage came like a breath of fresh air. I am very thankful to you. Saved my day 🙂
Thanks! I am glad that the post was helpful.
Bobby
Thank you! Suscribing…
Thanks! I hope that you find some of this to be helpful.
Bobby
Hi can anyone help ke here.. I have python script where I will using sqlplus to connect to the data to fecth output of sql query but problem is when I run my script manually it is working as expected but when I am running through cron job sqlplus is not getting trigger even I don’t know why it is happening. Can any one help me right way to schedule it’s not like I have to give env given everything don’t anything is missing which is not noticeable.
Thank you for your comment.
Your cron job is probably running with different environment variables or out of a different directory or as a different user than you are manually.
Otherwise it would work that same way.
I always have to tweak things like that when I setup a cron job.
Bobby
Bobby,
Thank you so much for the script!
The script run fine with SELECT statements but hung with an UPDATE statement. Here is the message after the manual intervention.
^CTraceback (most recent call last):
File “connect_db.py”, line 60, in
sqlplus_output = run_sqlplus(sqlplus_script)
File “connect_db.py”, line 25, in run_sqlplus
(stdout,stderr) = p.communicate(sqlplus_script.encode(‘utf-8’))
File “/usr/lib64/python2.7/subprocess.py”, line 483, in communicate
return self._communicate(input)
File “/usr/lib64/python2.7/subprocess.py”, line 1124, in _communicate
stdout, stderr = self._communicate_with_poll(input)
File “/usr/lib64/python2.7/subprocess.py”, line 1178, in _communicate_with_poll
ready = poller.poll()
KeyboardInterrupt
Any help is highly appreciated.
Best regards,
Jeff
Thank you for your comment. It looks like you interrupted the program while it was waiting for the update to finish. Could it have been running for a long time or hung up on a lock?
Bobby
Bobby,
Thank you for the quick reply.
The UPDATE statement was an one-row test run. It should be complete quickly. But it got hung instead. Did you try your script with any UPDATE statement?
Thanks a lot!
Yes. Any SQL statements that run in SQL*Plus will work.
C:\temp>python test.py
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 26 14:17:00 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL>
1 row updated.
SQL>
Commit complete.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Thank you for the confirmation!
I used the following UPDATE statement in the sqlplus_script string. I tried in SQLPlus by replacing the variables and it worked. But it didn’t in the script. Any idea?
Thanks again!
UPDATE test_table SET CUR_NUMBER=to_number(‘”””+str(curnum)+”””‘), PRIOR_NUMBER=to_number(‘”””+str(priornum)+”””‘), UPDATE_DATE=to_date(to_number(‘”””+curdate+”””‘), ‘YY
YYmmddHHMISS’) WHERE PREFIX='”””+prefix+”””‘;
I can’t quite follow what you are doing. Thanks for your comment.
Bobby