Convert Java to Python using cx_Oracle SQL/XML query

Question

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?

Anthony Tuininga · Accepted Answer · 2018-02-02 15:43:23Z

1

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()

answered Feb 2, 2018 at 15:43

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

imbtfab Over a year ago

After much fiddling I arrived at a similator solution. This looks cleaner.

Collectives™ on Stack Overflow

Convert Java to Python using cx_Oracle SQL/XML query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related