sql with cx_Oracle

577
3
05-02-2018 06:05 PM
forestknutsen1
MVP Regular Contributor

This sql works fine from pl/sql developer but fails when I try and do it with cx_Oracle.

Working fine....

call sde.version_util.set_current_version ('ARCFM8.vtest');
call sde.version_user_ddl.edit_version ('ARCFM8.vtest', 1);
update arcfm8.table_vw set REFERENCEDRAWING = 'my multiversion view update!!'
where OBJECTID = 3;
commit;
call sde.version_user_ddl.edit_version ('ARCFM8.vtest', 2);

Failing on line 3...

with cx_Oracle.connect("arcfm8/xxxx@xxxx") as connection:
    cursor = connection.cursor()
    cursor.callproc("sde.version_util.set_current_version", ('ARCFM8.vtest'))
    cursor.callproc("sde.version_user_ddl.edit_version", ('ARCFM8.vtest', 1))
    cursor.execute(
        "update arcfm8.table_vw set REFERENCEDRAWING = 'my SECOND multiversion view update!!!' where OBJECTID = 3")
    cursor.callproc("sde.version_user_ddl.edit_version", ('ARCFM8.vtest', 2))

cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SET_CURRENT_VERSION'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

0 Kudos
3 Replies
TamiOnstad
New Contributor III

Did you make this work?  My code works, except it seems to be ignoring the version I set and editing directly in the Default version which is NOT what I want.  

0 Kudos
forestknutsen1
MVP Regular Contributor

I did not. I ended up dropping the python wrapper and just running the SQL in pl/sql developer. If you get it working please post your solution

0 Kudos
TamiOnstad
New Contributor III

My solution, a snippet of code, is below

cursorUpd = db.cursor()

#set version

cursorUpd.callproc('sde.version_util.set_current_version',['MyVersion'])

#start editing

cursorUpd.callproc('sde.version_user_ddl.edit_version',['MyVersion',1])

#SQL for update

cursorUpd.execute("UPDATE xxx set REVIEWNOTES = 'TEST " + currDay + "' WHERE ORIGGUID = " + matchGUID)

#stop editing, save

cursorUpd.callproc('sde.version_user_ddl.edit_version',['MyVersion',2])

db.commit()

Hopefully this is helpful. I initially tried to do this through a single large execute block, with all the calls inside the execute block, but the bind variables for the update statement would not work, no matter how they were formatted. The above works for me.