23

I have this code:

    dsn = cx_Oracle.makedsn(hostname, port, sid)
    orcl = cx_Oracle.connect(username + '/' + password + '@' + dsn)
    curs = orcl.cursor()
    sql = "select TEMPLATE from my_table where id ='6'"
    curs.execute(sql)
    rows = curs.fetchall()
    print rows
    template = rows[0][0]
    orcl.close()
    print template.read()

When I do print rows, I get this:

[(<cx_Oracle.LOB object at 0x0000000001D49990>,)]

However, when I do print template.read(), I get this error:

cx_Oracle.DatabaseError: Invalid handle!

Do how do I get and read this data? Thanks.

Di Zou
  • 4,469
  • 13
  • 59
  • 88

5 Answers5

29

I've found out that this happens in case when connection to Oracle is closed before the cx_Oracle.LOB.read() method is used.

orcl = cx_Oracle.connect(usrpass+'@'+dbase)
c = orcl.cursor()
c.execute(sq)
dane =  c.fetchall()

orcl.close() # before reading LOB to str

wkt = dane[0][0].read()

And I get: DatabaseError: Invalid handle!
But the following code works:

orcl = cx_Oracle.connect(usrpass+'@'+dbase)
c = orcl.cursor()
c.execute(sq)
dane =  c.fetchall()

wkt = dane[0][0].read()

orcl.close() # after reading LOB to str
geowrw
  • 306
  • 3
  • 3
  • 1
    any idea why this is the case? – Andy Hayden Sep 26 '12 at 00:26
  • I can't even reproduce the error anymore, but this is good to know. Thanks! Edit: Actually, I just reproduced it and it worked after I move the `orcl.close()` to after the `read()`. Thanks! – Di Zou Sep 26 '12 at 13:48
  • I did some more testing. This only happens on LOB data. This is really interesting. I may ask another question about this. – Di Zou Sep 26 '12 at 13:58
  • 2
    The code shows 'streaming' lobs, which can require multiple accesses to the DB. This means the connection needs to stay open until the LOB is read. If you used the faster method of getting LOBs as Strings (or bytes), this wouldn't be necessary. See the cx_Oracle documentation: https://cx-oracle.readthedocs.io/en/latest/user_guide/lob_data.html – Christopher Jones Jan 20 '21 at 22:14
10

Figured it out. I have to do something like this:

curs.execute(sql)        
for row in curs:
    print row[0].read()
Di Zou
  • 4,469
  • 13
  • 59
  • 88
  • Just to add in to this, if the row is int only row[0] is required. For int there is no read(). – NIK Nov 29 '17 at 04:41
1

I had the same problem with in a slightly different context. I needed to query a +27000 rows table and it turns out that cx_Oracle cuts the connection to the DB after a while.

While a connection to the db is open, you can use the read() method of the cx_Oracle.Lob object to transform it into a string. But if the query brings a table that is too big, it won´t work because the connection will stop at some point and when you want to read the results from the query you´ll gt an error on the cx_Oracle objects.

I tried many things, like setting connection.callTimeout = 0 (according to documentation, this means it would wait indefinetly), using fetchall() and then putting the results on a dataframe or numpy array but I could never read the cx_Oracle.Lob objects.

If I try to run the query using pandas.DataFrame.read_sql(query, connection) The dataframe would contain cx_Oracle.Lob objects with the connection closed, making them useless. (Again this only happens if the table is very big)

In the end I found a way of getting around this by querying and creating a csv file inmediatlely after, even though I know it´s not ideal.

def csv_from_sql(sql: str, path: str="dataframe.csv") -> bool:
try: 
    with cx_Oracle.connect(config.username, config.password, config.database, encoding=config.encoding) as connection:
        connection.callTimeout = 0
        data = pd.read_sql(sql, con=connection)
        data.to_csv(path)
        print("FILE CREATED")
except cx_Oracle.Error as error: 
    print(error)
    return False
finally: 
    print("PROCESS ENDED\n")
    return True

def make_query(sql: str, path: str="dataframe.csv") -> pd.DataFrame:
if csv_from_sql(sql, path):
    dataframe = pd.read_csv("dataframe.csv")
    return dataframe
return pd.DataFrame()

This took a long time (about 4 to 5 minutes) to bring my +27000-rows table, but it worked when everything else didn´t.

If anyone knows a better way, it would be helpful for me too.

norman123123
  • 837
  • 6
  • 15
  • cx_Oracle does not 'cut connections'. Maybe you ran out of process memory. Maybe you hit a DBA-imposed user profile / resource manager limit. Review the cx_Oracle doc on LOB handling. (Also, since you have a question, why not post a new SO question instead of tagging it on here?) – Christopher Jones Jan 20 '21 at 22:11
0

You basically have to loop through the fetchall object 

dsn = cx_Oracle.makedsn(hostname, port, sid)
orcl = cx_Oracle.connect(username + '/' + password + '@' + dsn)
curs = orcl.cursor()
sql = "select TEMPLATE from my_table where id ='6'"
curs.execute(sql)
rows = curs.fetchall()
for x in rows:
   list_ = list(x)
   print(list_)
Jain26
  • 63
  • 1
  • 7
0

There should be an extra comma in the for loop, see in below code, i have supplied an extra comma after x in for loop.

dsn = cx_Oracle.makedsn(hostname, port, sid)
orcl = cx_Oracle.connect(username + '/' + password + '@' + dsn)
curs = orcl.cursor()
sql = "select TEMPLATE from my_table where id ='6'"
curs.execute(sql)
rows = curs.fetchall()
for x, in rows:
    print(x)
shradha
  • 1