Q: Trying To Assign SQL*Plus Output To Tupple (Two-Dimensional Array) In Python 2.7 At work, we're unable to use the cx_oracle module due to the security environment. We're stuck using SQLPLUS. Finding online code, I was able to successfully run SQL queries but the issue is that the result of the query is not easy to work with. I was wondering if someone could assist with fixing the output so that it will just throw the query results into a tupple with two dimensions representing columns and rows. Here is the code that I have so far:
    from subprocess import Popen, PIPE
 
#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()

The function is invoked like this:
sqlCommand='select * from <database-name-removed> where payrollnumber in (1111);'
connectString=username + '/' + password + '@SERVER'
queryResult, errorMessage = runSqlQuery(sqlCommand, connectString)

When I perform a "print queryresult", I'll receive output in a kind of wonky format like so:
  PERSONID PAYROLLNUMBER EMPLOYEEPREFIX LASTNAME
---------- ------------- -------------- ------------------------------
FIRSTNAME                 MI       ACTIVE      UN_ID
------------------------- ---- ---------- ----------
     11111         99999                NEWMAN
ALFRED                             1

This is obviously not easy to parse. I would like to preserve the column names for row 0. In this example, the tupple would look something like this:
tupple=["PERSONID", "PAYROLLNUMBER", "EMPLOYEEPREFIX", "LASTNAME", "FIRSTNAME", "MI", "ACTIVE", "UN_ID"]
       ["11111", "99999", "", "NEWMAN", "ALFRED", "", "1", ""]

In the second row of data, the MI, EMPLOYEEPREFIX, and UN_ID are blank so this is why there are missing values.
Thanks!

A: Concatenate the columns and put separators between them. For example here is how I get the current SCN number using sqlplus in Python:
    scn_query = """
COLUMN SCN FORMAT 999999999999999
CREATE TABLE tempscnnumber ( scn varchar2(25));
INSERT INTO tempscnnumber VALUES(
TO_CHAR(userenv('commitscn'),999999999999999));
commit;
select 'S'||'CN:'||to_number(SCN) from tempscnnumber;
drop table tempscnnumber;
exit;
    """

Later in the code I search the output of sqlplus for SCN:
    """
Output is like:

'S'||'CN:'||CURRENT_SCN
--------------------------------------------
SCN:77262587

    """

# Find the line with SCN
    for line in output:
        if line[0:4]=="SCN:":
            scn = line[4:]
            return int(scn)

    return -1

It is not pretty. If you want a bunch of columns just concatenate them with ||':'|| if your output does not have colons or use ||'%*%%^&'|| or any string not in your output.
Bobby
