Running SQL*Plus from a Python script

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

56 Responses to Running SQL*Plus from a Python script

  1. Anurag Lal says:

    Thanks man.. Helped me 🙂

  2. Great starting point for scripting python. Thanks!

  3. Anonymous says:

    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!

  4. Naren Vema says:

    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

    • Bobby says:

      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

    • Terry Porter says:

      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.

  5. Hassan says:

    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.

  6. Anuj says:

    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.

  7. My says:

    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 🙂

    • Bobby says:

      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

      • My says:

        Thanks for your reply. I get this error message in online compilers/intepreters and when I run the script on my Linux box!

        • Bobby says:

          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)

          • My says:

            Thanks, Bobby! You were right. I tried calling python script on remote server with SSH. I forgot to su – oracle 🙂

  8. sherif ashmawy says:

    Hi Bobby,
    do you have this on git ?
    apologies, it if was obvious and seems to be. I just can’t find the link

    • Bobby says:

      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

      • Sherif A Ashmawy says:

        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.

        • Bobby says:

          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

  9. Coco says:

    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.

    • Bobby says:

      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")

  10. In my case it simply returns, “session communicate is (b”, b”)” Any idea why?

    • Bobby says:

      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.

  11. Arun says:

    is there a way you can set the echo on ? i have tried setting it multiple ways and it doesnt seem to take effect

    • Bobby says:

      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

      • aditya says:

        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

        • Bobby says:

          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

          • aditya says:

            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

          • Bobby says:

            That is great! Thank you for your comment.

            Bobby

  12. Arun says:

    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.

  13. Jitesh says:

    Hi Bobby, how do I catch an sql error as an exception here in this code example

    • Bobby says:

      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

  14. Kaan says:

    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! 🙂

  15. Rex says:

    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.

    • Bobby says:

      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

  16. sonarika says:

    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”)

    • Bobby says:

      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

  17. satish says:

    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

    • Bobby says:

      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

  18. Anand Doraiswamy says:

    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 🙂

  19. Ivan P says:

    Thank you! Suscribing…

  20. Anjali shreya says:

    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.

    • Bobby says:

      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

  21. Jeff says:

    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

    • Bobby says:

      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

      • Jeff says:

        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!

        • Bobby says:

          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

          • Anonymous says:

            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+”””‘;

          • Bobby says:

            I can’t quite follow what you are doing. Thanks for your comment.

            Bobby

Leave a Reply to Bobby Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.