Is it possible to "Reuse Cursors" to Avoid Soft Parse in oracledb?

[mkmoisen]

The Oracle documentation makes reference to the notion that while soft parses with bind variables are better than hard parses, reusing cursors are even better than soft parses.

However, soft parses are not ideal, because they still require syntax and security checking, which consume system resources. Because parsing should be minimized as much as possible, application developers should design their applications to parse SQL statements once and execute them many times. This is done through cursors. Experienced SQL programmers should be familiar with the concept of opening and re-executing cursors.

And here:

Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times.

My interpretation is that there is a way to avoid even a soft parse through reusing a cursor.

Is there any way to "reuse a cursor" in oracledb?

To be clear, I'm not exactly clear what cursor reuse means.

The only thing I could come up with was a single-row cursor in PL/SQL which can be "reused" without calling CLOSE and OPEN again:

set serveroutput on

declare
    CURSOR l_cursor IS
        SELECT 1 foo FROM dual
    ;
    l_value PLS_INTEGER;
BEGIN
    OPEN l_cursor;
    FETCH l_cursor INTO l_value;
    
    dbms_output.put_line(l_value);
    
    -- reusing the cursor
    FETCH l_cursor INTO l_value;
    
    dbms_output.put_line(l_value);
    
    CLOSE l_cursor;
END;
/

I was surprised to see that the above will print the value twice, even though I did not CLOSE and OPEN the cursor again.

I do not see a way to do it for multi-row cursors, which seem to require a CLOSE and OPEN. Unless this also counts as reusing a cursor.

[cjbj]

Have you looked at https://python-oracledb.readthedocs.io/en/latest/api_manual/cursor.html#Cursor.prepare ? Check the tests for examples.