The value of prefetchrows for REF CURSOR variables is now honored

anthony-tuininga · anthony-tuininga · commit 7e7133b09a1d · 2020-11-12T14:24:42.000-07:00
(oracle#482).
diff --git a/doc/src/release_notes.rst b/doc/src/release_notes.rst
@@ -23,6 +23,7 @@ Version 8.1 (TBD)
 #)  The ability to pickle/unpickle Database and API types has been restored.
 #)  Tests can now be run with tox in order to automate testing of the different
     environments that are supported.
+#)  The value of prefetchrows for REF CURSOR variables is now honored.
 #)  Improved documentation and test suite.
 
 
diff --git a/samples/RefCursor.py b/samples/RefCursor.py
@@ -13,17 +13,43 @@
 connection = cx_Oracle.connect(SampleEnv.GetMainConnectString())
 cursor = connection.cursor()
 
-refCursor = connection.cursor()
-cursor.callproc("myrefcursorproc", (2, 6, refCursor))
+ref_cursor = connection.cursor()
+cursor.callproc("myrefcursorproc", (2, 6, ref_cursor))
 print("Rows between 2 and 6:")
-for row in refCursor:
+for row in ref_cursor:
     print(row)
 print()
 
-refCursor = connection.cursor()
-cursor.callproc("myrefcursorproc", (8, 9, refCursor))
+ref_cursor = connection.cursor()
+cursor.callproc("myrefcursorproc", (8, 9, ref_cursor))
 print("Rows between 8 and 9:")
-for row in refCursor:
+for row in ref_cursor:
     print(row)
 print()
 
+#------------------------------------------------------------------------------
+# Setting prefetchrows and arraysize of a REF cursor can improve performance
+# when fetching a large number of rows (Tuned Fetch)
+#------------------------------------------------------------------------------
+
+# Truncate the table used for this demo
+cursor.execute("truncate table TestTempTable")
+
+# Populate the table with a large number of rows
+num_rows = 50000
+sql = "insert into TestTempTable (IntCol) values (:1)"
+data = [(n + 1,) for n in range(num_rows)]
+cursor.executemany(sql, data)
+
+# Set the arraysize and prefetch rows of the REF cursor
+ref_cursor = connection.cursor()
+ref_cursor.prefetchrows = 1000
+ref_cursor.arraysize = 1000
+
+# Perform the tuned fetch
+sum_rows = 0
+cursor.callproc("myrefcursorproc2", [ref_cursor])
+print("Sum of IntCol for", num_rows, "rows:")
+for row in ref_cursor:
+    sum_rows += row[0]
+print(sum_rows)
diff --git a/samples/sql/SetupSamplesExec.sql b/samples/sql/SetupSamplesExec.sql
@@ -382,6 +382,15 @@ begin
 end;
 /
 
+create procedure &main_user..myrefcursorproc2 (
+    a_RefCursor                         out sys_refcursor
+) as
+begin
+    open a_RefCursor for
+        select *
+        from TestTempTable;
+end;
+/
 
 --
 -- Create package for demoing PL/SQL collections and records.
diff --git a/src/cxoVar.c b/src/cxoVar.c
@@ -462,6 +462,10 @@ static int cxoVar_setValueCursor(cxoVar *var, uint32_t pos, dpiData *data,
         cursor->handle = data->value.asStmt;
         dpiStmt_addRef(cursor->handle);
     }
+
+    if (dpiStmt_setPrefetchRows(cursor->handle, cursor->prefetchRows) < 0)
+        return cxoError_raiseAndReturnInt();
+
     cursor->fixupRefCursor = 1;
     return 0;
 }
diff --git a/test/sql/SetupTestExec.sql b/test/sql/SetupTestExec.sql
@@ -443,6 +443,17 @@ begin
 end;
 /
 
+-- create procedure for testing refcursor
+create procedure &main_user..myrefcursorproc (
+    a_RefCursor                         out sys_refcursor
+) as
+begin
+    open a_RefCursor for
+        select *
+        from TestTempTable;
+end;
+/
+
 -- create functions for testing callfunc()
 create function &main_user..func_Test (
     a_String                            varchar2,
diff --git a/test/test_1200_cursor.py b/test/test_1200_cursor.py
@@ -791,5 +791,62 @@ def test_1265_prefetchrows(self):
             cursor.execute("select IntCol from TestTempTable").fetchall()
             self.assertRoundTrips(2)
 
+    def test_1266_refcursor_prefetchrows(self):
+        "1266 - test prefetch rows and arraysize using a refcursor"
+        self.setup_round_trip_checker()
+
+        # simple DDL only requires a single round trip
+        with self.connection.cursor() as cursor:
+            cursor.execute("truncate table TestTempTable")
+            self.assertRoundTrips(1)
+
+        # array execution only requires a single round trip
+        num_rows = 590
+        with self.connection.cursor() as cursor:
+            sql = "insert into TestTempTable (IntCol) values (:1)"
+            data = [(n + 1,) for n in range(num_rows)]
+            cursor.executemany(sql, data)
+            self.assertRoundTrips(1)
+
+        # create refcursor and execute stored procedure
+        with self.connection.cursor() as cursor:
+            refcursor = self.connection.cursor()
+            refcursor.prefetchrows = 300
+            refcursor.arraysize = 300
+            cursor.callproc("myrefcursorproc", [refcursor])
+            refcursor.fetchall()
+            self.assertRoundTrips(2)
+
+    def test_1267_existing_cursor_prefetchrows(self):
+        "1267 - test prefetch rows using existing cursor"
+        self.setup_round_trip_checker()
+
+        # Set prefetch rows on an existing cursor
+        num_rows = 590
+        with self.connection.cursor() as cursor:
+            cursor.execute("truncate table TestTempTable")
+            sql = "insert into TestTempTable (IntCol) values (:1)"
+            data = [(n + 1,) for n in range(num_rows)]
+            cursor.executemany(sql, data)
+            cursor.prefetchrows = 300
+            cursor.arraysize = 300
+            cursor.execute("select IntCol from TestTempTable").fetchall()
+            self.assertRoundTrips(4)
+
+    def test_1268_bind_names_with_single_line_comments(self):
+        "1268 - test bindnames() with single line comments"
+        self.cursor.prepare("""--begin :value2 := :a + :b + :c +:a +3; end;
+                            begin :value2 := :a + :c +3; end;
+                            """)
+        self.assertEqual(self.cursor.bindnames(), ["VALUE2", "A", "C"])
+
+    def test_1269_bind_names_with_multi_line_comments(self):
+        "1269 - test bindnames() with multi line comments"
+        self.cursor.prepare("""/*--select * from :a where :a = 1
+                            select * from table_names where :a = 1*/
+                            select * from :table_name where :value = 1
+                            """)
+        self.assertEqual(self.cursor.bindnames(), ["TABLE_NAME", "VALUE"])
+
 if __name__ == "__main__":
     base.run_test_cases()

Original file line number	Diff line number	Diff line change
`@@ -462,6 +462,10 @@ static int cxoVar_setValueCursor(cxoVar var, uint32_t pos, dpiData data,`
`462`	`462`	`cursor->handle = data->value.asStmt;`
`463`	`463`	`dpiStmt_addRef(cursor->handle);`
`464`	`464`	`}`
	`465`	`+`
	`466`	`+ if (dpiStmt_setPrefetchRows(cursor->handle, cursor->prefetchRows) < 0)`
	`467`	`+ return cxoError_raiseAndReturnInt();`
	`468`	`+`
`465`	`469`	`cursor->fixupRefCursor = 1;`
`466`	`470`	`return 0;`
`467`	`471`	`}`