Dec-04-2018, 02:08 AM
(This post was last modified: Dec-04-2018, 02:08 AM by anandoracledba.)
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
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:
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 #2How 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