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
When I run this code, I get this error message:
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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) |
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