Python Forum
Help with cx_Oracle - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Help with cx_Oracle (/thread-14506.html)

Help with cx_Oracle - anandoracledba - Dec-04-2018

Hello Gurus,

I started coding in Python 5 days ago and have questions/doubts with cx_Oracle module that I hope can get some help with. I have a SQL script(run.sql) to run and spool the output to a file. This is the script:

Issue # 1:

I have this python code to run the above run.sql

import sys
import os
import getopt
import cx_Oracle

main_sql = """SELECT name, bytes, maxbytes FROM dba_data_files||"""

fh = open('/tmp/run.sql', 'w')
fh.write ("set markup html on               ||\n")
fh.write ("set feed off echo off termout off||\n")
fh.write ("spool "+ HTM + "||\n")
fh.write (main_sql + "||\n")
fh.write ("spool off||\n")
fh.write ("exit     ||\n")
fh.close()

r = open('/tmp/run.sql', "r")
dbconn = cx_Oracle.connect(mode=cx_Oracle.SYSDBA)
dbcur = dbconn.cursor()
sql_cmd = r.read().split('||')
for i in sql_cmd:
    print i
    dbcur.execute(i)
When I run this code, I get this error message:

Error:
$ ./tabspc_check.py -z XE -t 1 -b

Env set to XE and /u01/app/oracle/product/11.2.0/xe...

set markup html on
Traceback (most recent call last):
  File "./tabspc_check.py", line 130, in <module>
    dbcur.execute(i)
cx_Oracle.DatabaseError: ORA-00922: missing or invalid option
It's not accepting any SET or SPOOL commands. What is the way around? This is how "run.sql" looks like after it's generated:

Output:
set markup html on||

set feed off echo off termout off||

spool /tmp/tablespace.html||

SELECT name, bytes, maxbytes FROM dba_data_files;||

spool off||

exit||
Issue #2

How do I spool the output of the SQL script to a text file or a HTML file? Since, python doesn't accept "SET MARKUP HTML ON" command, I am unsure how to handle that. Also, I would like the output in non-tuple format. This is what I see currently in the log file which I don't want:

Output:
[('SYSAUX', 0.594912109375, 31.99998046875, 1.86, 31.4, 98.14)]
[('SYSTEM', 0.344423828125, 0.5859375, 58.78, 0.2, 41.22)]
How do I get the output in normal format that Oracle SQLPlus throws like this...

Output:
SYSAUX  0.594912109375  31.99998046875  1.86  31.4  98.14
SYSTEM  0.344423828125  0.5859375      58.78   0.2  41.22
Anand