topic sql with cx_Oracle in Python Questions

sql with cx_Oracle

forestknutsen1 — Sat, 11 Dec 2021 22:18:20 GMT

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

Re: sql with cx_Oracle

TamiOnstad — Wed, 14 Oct 2020 20:24:46 GMT

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.

Re: sql with cx_Oracle

forestknutsen1 — Thu, 15 Oct 2020 01:07:57 GMT

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

Re: sql with cx_Oracle

TamiOnstad — Thu, 15 Oct 2020 15:30:14 GMT

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.