Problem: Commit no longer works

This is a puzzler - we have many python apps the do inserts and updates to Oracle (10.1.0.4) using cx_oracle 4.4.1 and python 2.5.1 under Windows XP Pro - they all work

writing a new app I found I could no longer do an insert - to be more specific: I can do an insert or update but the commit that fails!

after performing a commit, the changes do not seem to be committed to the db and do not show up in another oracle session. They do not persist.

this behavior happens on 3 separate computers, against 3 separate oracle instances, yet the previously written py apps are working just fine as are apps written in C and C++.

I can perform a create table command (DDL), and a select statement (DML) but cannot commit DML changes

any ideas?

is there a way to test error or sql execution status from within python similar to sqlca.sqlcode in C?

here is sample code:

insert_test.py
//////////////////////////////////////////////////////////////////

import cx_Oracle

## params
CX_LOGON = 'myuser/[email protected]'
ORACLE_CHUNK=1000

## open 2 separate connections (oracle sessions)
dbc1 = cx_Oracle.Connection(CX_LOGON)
dbc2 = cx_Oracle.connect(CX_LOGON)

## create 2 separate cursors
crsr1 = dbc1.cursor()
crsr1.arraysize = ORACLE_CHUNK
crsr2 = dbc2.cursor()
crsr2.arraysize = ORACLE_CHUNK

## result lists
result1=[]
count1=[]
count2=[]

## our sql statements
sql_drop = "DROP TABLE PYTEST"
sql_create = "CREATE TABLE PYTEST (name varchar2(20))"
sql_trunc = "TRUNCATE TABLE PYTEST"
sql_select = "SELECT name FROM PYTEST"
sql_insert1 = "INSERT INTO PYTEST VALUES ('xxx')"
sql_insert2 = "INSERT INTO PYTEST VALUES ('yyy')"
sql_count = "SELECT COUNT(*) FROM PYTEST"
sql_update = "UPDATE PYTEST SET NAME = 'zzz' WHERE NAME = 'yyy'"

## first create the table - we only do this once
##crsr1.execute(sql_drop)
##crsr1.execute(sql_create)

## clear out the table
crsr1.execute(sql_trunc)

## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows = 0
print "before insert we have ", nrows, " rows"

## now insert a row using same connection
crsr1.execute(sql_insert1)
dbc1.commit;

## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows = 0
print "after insert we have ", nrows, " rows using same connection"

## now insert another row using same connection
crsr1.execute(sql_insert2)
dbc1.commit;

## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows=0
print "after insert we have ", nrows, " rows using same connection"

## get table contents
crsr1.execute(sql_select)
result1 = crsr1.fetchall()
if(nrows>0):
print "here are the rows: "
for result in result1:
name = result[0]
print " name = ", name


## now do an update using same connection
crsr1.execute(sql_update)
dbc1.commit;

## check how many rows
crsr1.execute(sql_count)
count1 = crsr1.fetchall()
if(len(count1)>0):
nrows = int(count1[0][0])
else:
nrows=0
print "after update we have ", nrows, " rows using same connection"

## get table contents
crsr1.execute(sql_select)
result1 = crsr1.fetchall()
if(nrows>0):
print "here are the rows after update: "
for result in result1:
name = result[0]
print " name = ", name


## now check how many rows using 2nd connection
crsr2.execute(sql_count)
count2 = crsr2.fetchall()
if(len(count2)>0):
nrows2 = int(count2[0][0])
else:
nrows2 = 0
print "second connection sees ", nrows2, " rows"

## lets' try swinging a chicken over our heads and commit both connects again
dbc1.commit
dbc2.commit

print "end - after dual commits - check row count externally with sql*plus"

/////////////////////////////////////


Mike