如何用cx_Oracle将Java的Oracle SQL/XML查询转换为Python实现？

Absolutely you can replicate your Java logic using cx_Oracle—no need to stick with Java! Your initial attempts ran into issues because of incorrect parameter binding syntax (you tried assigning to a literal 2 instead of a bound output variable), so let's walk through two correct approaches to get that task ID.

Approach 1: Execute a PL/SQL Block with Bound Parameters #

This mirrors your Java code closely, using a PL/SQL block and explicitly binding the output parameter.

import cx_Oracle

# Replace with your actual database connection details
db_connection = cx_Oracle.connect("username/password@host:port/service_name")

# Your XML input (note: no need for HTML entities like < in Python—use the raw XML)
xml_input = """<ttc.export.public.data.search>
    <query>
        <popid>1</popid>
        <moduleid>3</moduleid>
        <only_changes>0</only_changes>
    </query>
</ttc.export.public.data.search>"""

# Define the PL/SQL block with placeholders for parameters
plsql_block = """
BEGIN
    ? := pkgioexportora.request(?);
END;
"""

with db_connection.cursor() as cursor:
    # Register the output parameter as a NUMBER type
    task_id = cursor.var(cx_Oracle.NUMBER)
    # Execute the block, passing the output variable first, then the XML input
    cursor.execute(plsql_block, [task_id, xml_input])
    
    # Retrieve the task ID from the output variable
    print(f"Generated Task ID: {task_id.getvalue()}")

db_connection.close()

Approach 2: Use callproc for Direct Procedure Calls #

cx_Oracle's callproc method is designed specifically for calling stored procedures. You just need to properly define the output parameter before passing it in.

import cx_Oracle

db_connection = cx_Oracle.connect("username/password@host:port/service_name")
xml_input = """<ttc.export.public.data.search>
    <query>
        <popid>1</popid>
        <moduleid>3</moduleid>
        <only_changes>0</only_changes>
    </query>
</ttc.export.public.data.search>"""

with db_connection.cursor() as cursor:
    # Create an output variable of type NUMBER
    task_id = cursor.var(cx_Oracle.NUMBER)
    # Call the procedure: pass the package.procedure name, then the parameter list (output first)
    cursor.callproc("pkgioexportora.request", [task_id, xml_input])
    
    # Get the result
    print(f"Generated Task ID: {task_id.getvalue()}")

db_connection.close()

Key Notes to Avoid Issues #

• Parameter Order: The output parameter comes first in the argument list (matching how you registered the OUT parameter in Java).
• XML Handling: In Python, you don't need to escape angle brackets to </>—use the raw XML string directly.
• Resource Management: Using with statements ensures cursors and connections are closed automatically, preventing leaks.
• Why Your Initial Attempt Failed: Writing begin 2 := pkgioexportora.request(...); is invalid PL/SQL—you can't assign to a literal number. You must use a bound variable (like the task_id var in the examples) to capture the output.

Once you have the task ID, you can use it in subsequent SELECT queries just like you would in Java.

内容的提问来源于stack exchange，提问作者imbtfab