sql with cx_Oracle

forestknutsen1

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

Working fine....

<SPAN class="keyword token">call</SPAN> sde<SPAN class="punctuation token">.</SPAN>version_util<SPAN class="punctuation token">.</SPAN>set_current_version <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'ARCFM8.vtest'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">call</SPAN> sde<SPAN class="punctuation token">.</SPAN>version_user_ddl<SPAN class="punctuation token">.</SPAN>edit_version <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'ARCFM8.vtest'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">update</SPAN> arcfm8<SPAN class="punctuation token">.</SPAN>table_vw <SPAN class="keyword token">set</SPAN> REFERENCEDRAWING <SPAN class="operator token">=</SPAN> <SPAN class="string token">'my multiversion view update!!'</SPAN>
<SPAN class="keyword token">where</SPAN> OBJECTID <SPAN class="operator token">=</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">commit</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">call</SPAN> sde<SPAN class="punctuation token">.</SPAN>version_user_ddl<SPAN class="punctuation token">.</SPAN>edit_version <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'ARCFM8.vtest'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Failing on line 3...

with cx_Oracle.connect("arcfm8/xyz@xyz") 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

TamiOnstad

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 xyz 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.