CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
imbtfab
imbtfab

Reputation: 493

Convert Java to Python using cx_Oracle SQL/XML query

I have an existing solution in Java to extract data from an Oracle database. I do it like so:

String tmp = "begin ? := pkgioexportora.request(?); end;";
String xml = "<ttc.export.public.data.search><query><popid>1</popid> <moduleid>3</moduleid><only_changes>0</only_changes></query></ttc.export.public.data.search>";
CallableStatement callableStmt = oracle.prepareCall(tmp); 
// Register the type of the out param - an Oracle specific type
callableStmt.registerOutParameter(1, OracleTypes.NUMBER);
callableStmt.setString(2, xml);

This construct returns a job id which I'm later required to use in the WHERE clause of a SELECT query.

I've tried using just a cursor and input the complete statement, without the CallableStatement stuff, but no luck.

cursor = con.cursor()
cursor.execute("begin 2 := pkgioexportora.request(xml_stuff_here); end;";)

callproc seems to give me similar error.

I've tried searching for solutions, or similar things done, but yet to come up with any examples. Is it possible to do such a thing with cx_Oracle or am I stuck with my Java code for doing this?

Upvotes: 0

Views: 259

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7096

You can use cursor.callfunc() in cx_Oracle. As in the following:

result = cursor.callfunc("pkgioexportora.request", cx_Oracle.NUMBER, [xml_stuff])

You could also do it this way, but its more complex:

var = cursor.var(cx_Oracle.NUMBER) cursor.execute("begin :1 := pkgioexportora.request(:2); end;", [var, xml_stuff]) result = var.getvalue()

Upvotes: 1

Related Questions