Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Help with cx_Oracle
#1
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  cx_oracle Error - AttributeError: 'function' object has no attribute 'cursor' birajdarmm 1 2,214 Apr-15-2023, 05:17 PM
Last Post: deanhystad
  Installing cx_Oracle 64-bit on AIX Nash 0 1,972 Dec-10-2019, 12:20 AM
Last Post: Nash
  cx_Oracle.DatabaseError: ORA-12541: TNS:no listener ARV 2 10,928 Oct-03-2019, 12:53 PM
Last Post: ARV
  Oracle Merge using cx_oracle raulp2301 0 4,734 Mar-15-2019, 12:56 AM
Last Post: raulp2301
  [split] Help with cx_Oracle gnishakamath 0 2,771 Jan-24-2019, 07:01 PM
Last Post: gnishakamath
  Getting an error while connecting oracle DB from Python : cx_Oracle.DatabaseError: OR dinesh1393 0 3,502 Oct-25-2018, 02:44 PM
Last Post: dinesh1393
  cx_Oracle module not found error PRADEEP 1 3,253 Sep-12-2018, 11:10 AM
Last Post: Larz60+
  Windows server 2008 SP2 - Python cx_Oracle connection DLL error tpanagoda 2 4,999 Mar-05-2018, 04:35 AM
Last Post: tpanagoda
  Pip install error for cx_Oracle kdamundson 2 10,592 Dec-01-2016, 08:11 PM
Last Post: kdamundson
  ORA-03113: end-of-file on communication channel Python Cx_Oracle akansha 3 7,488 Nov-17-2016, 08:50 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020