get custom output from sqlplus using Python

Question

i am trying to fetch the results of a SQL query run on an Oracle Database. I have manged to get the output using the below code where the run_sqlplus function runs the query.

def run_sqlplus(sqlplus_script):
    p = subprocess.Popen(['sqlplus','/as sysdba'],stdin=subprocess.PIPE,
    stdout=subprocess.PIPE,stderr=subprocess.PIPE)
    (stdout,stderr) = p.communicate(sqlplus_script.encode('utf-8'))
    stdout_lines = stdout.decode('utf-8').split("\n")
    return stdout_lines

def main():
   sqlplus_script='select open_mode,protection_mode,switchover_status,database_role from v$database;'
   queryResult =run_sqlplus(sqlplus_script)
   print(type(queryResult))
   for line in queryResult:
     print(line)

the output is :

<class 'list'>

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 9 11:29:07 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>
OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  TO STANDBY           PRIMARY

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

The issue is i only need the output between the 2 'SQL>' , i.e:

 OPEN_MODE            PROTECTION_MODE      SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  TO STANDBY           PRIMARY

How can i get the desired result.Please suggest.Thanks.

I agree strongly with the various comments: don't call SQL*Plus. Just use native cx_Oracle functionality. To start with, you can connect like connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA) — Christopher Jones
– Christopher Jones, Commented Mar 9, 2022 at 22:03

user123664 · Accepted Answer · 2022-03-09 14:20:23Z

1

It’s a lot easier to use the cx_oracle driver for this but if you insist in using sqlplus, use the -s (silent) and the -L flags to make it a bit better.

Advice: switch to cx_Oracle since it gives better control and it is also easier to hide your credentials while the process is running.

answered Mar 9, 2022 at 14:20

user123664

Sign up to request clarification or add additional context in comments.

Comments

anand · Accepted Answer · 2022-03-09 16:03:32Z

1

Adding '-S' after sqlplus solved it.

p = subprocess.Popen(['sqlplus','-S','/as sysdba'],stdin=subprocess.PIPE, stdout=subprocess.PIPE,stderr=subprocess.PIPE)

answered Mar 9, 2022 at 16:03

anand

4710 bronze badges

1 Comment

Christopher Jones Over a year ago

For future readers: invoking SQL*Plus to do a query has security implications if the password needs to be passed. It is also 'fragile' and will fail if SQL*Plus isn't available. It is also slow. A better solution is to execute the query directly in Python using the cx_Oracle module.

Christopher Jones · Accepted Answer · 2022-03-14 02:22:46Z

To give a complete example of doing this in Python, try this:

import cx_Oracle
import os
import platform

if platform.system() == "Darwin":
    cx_Oracle.init_oracle_client(lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8")
elif platform.system() == "Windows":
     cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_14")



connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)

# Or, for a remote database, you'll need the SYS password:
# connection = cx_Oracle.connect(user="sys", password="*****", dsn="myhost.example.com/orclpdb1", mode = cx_Oracle.SYSDBA)

with connection.cursor() as cursor:
    try:

        sql = """select open_mode,protection_mode,switchover_status,database_role from v$database"""
        cursor.execute(sql)

        # column headings
        columns = [col[0] for col in cursor.description]
        print(columns)

        # rows
        for row in cursor:
            print(row)

    except cx_Oracle.Error as e:
        error, = e.args
        print(sql)
        print('*'.rjust(error.offset+1, ' '))
        print(error.message)

Collectives™ on Stack Overflow

get custom output from sqlplus using Python

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related