Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Problem: Commit no longer works

408518Sep 14 2010 — edited Sep 16 2010
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/mypword@myserver'
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

Comments

EdStevens

This really has nothing to do with SQL Developer (the subject of this forum). It is a much better fit in SQL & PL/SQL.
And just as a side comment, storing 'age' as data is a flawed design. The 'age' of everyone and every thing is increasing by the day, if not by the second. What is your plan to keep 'age' current? Better to store 'date of birth' and the calculate 'age' when needed at run-time.

User_H3J7U

The 'age' of everyone and every thing is increasing by the day, if not by the second.
Sometimes the age stops increasing.
изображение.png

EdStevens

Pour me one!
Of course, that's not actually the age, but the amount of time it was 'aged' in the barrel before bottling.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 14 2010
Added on Sep 14 2010
4 comments
5,219 views