get custom output from sqlplus using Python-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

Adding '-S' after sqlplus solved it.

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