python cx_Oracle cursor returns no rows for valid query

Question

Beating my head against my desk on this one.

My cx_Oracle cursor is returning no rows for a valid query and I cannot figure out why.

The same connection returns expected results from other tables in same schema... for example if I simply change the referenced table name in the query from TABLE_A to TABLE_B it works fine.

The query works fine in SQL Developer/SQL Plus using the same user/connection string. I copied/pasted from cursor.statement during debug to verify exactly same statement, no typos.

I have reduced the queries to the simplest form possible;

1. select * from SCHEMA.TABLE_A

and

2. select * from SCHEMA.TABLE_B

Query 1 returns no rows when executed using cx_Oracle, but DOES return expected rows in SQLPlus/SQL Developer, etc.

Query 2 works as expected using cx_Oracle

I have verified privileges - but this is obviously working as I am using (and verified) the user/connection is same in cx_Oracle as in SQL tools.

the code couldn't be reduced/simplified anymore I do not think to isolate the problem - but I am probably being blind to something really obvious:

import cx_Oracle

db_conn=cx_Oracle.connect('user/password@localhost:1521/TEST_PDB')
cur = db_conn.cursor()
qry = 'select * from SCHEMA_NAME.TABLE_A'
cur.execute(qry)

{cx_Oracle.Cursor on {cx_Oracle.Connection to user@localhost:1521/TEST_PDB}}

cur.fetchall()

[]

cur.rowcount

0

Yet, the other table/query works:

qry = 'select * from SCHEMA_NAME.TABLE_B'
cur.execute(qry)
cur.fetchall()

[(12320573611891L, '23.5.2126981', 0, 'NEW', 'UPDATE', datetime.datetime(2016, 5, 24, 9, 0), 48, 0, None, None )]

This is the ONLY table I have encountered this issue with - that I know of so far.

I am using Oracle 12c, python 2.7.11 64bit, and ojdbc6.jar, and 64bit InstantClient

Any ideas ? Spot anything where my eyes or brain is just not seeing the obvious ?

What are the actual names of the tables? Table_A might be an Oracle reserved word? — Parfait
– Parfait, Commented Jul 18, 2016 at 17:26

bbaley · Accepted Answer · 2016-07-18 17:31:02Z

2

I figured out where I was being blind to the issue.

The records were not COMMITED. I was able to query the records in the session in which I inserted them (SQL Developer), using the same user.

Which led to "what the heck is going on" I should have known / better.

answered Jul 18, 2016 at 17:31

bbaley

3191 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

toonice Over a year ago

You can help reduce the list of questions without accepted Answers, or even any answer, by accepting your own Answer. Please read meta.stackexchange.com/questions/16930/… for more information on answering your own question.

Bernard Esterhuyse Over a year ago

Thanks, this saved me some time! I Like you said - I also should have known this was the issue. Changes not commit has caught me out before!

shuster Over a year ago

So, for cx_oracle after execute function connection.commit() worked for me.

Collectives™ on Stack Overflow

python cx_Oracle cursor returns no rows for valid query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related