Add samples for implicit results and scrollable cursors.

anthony-tuininga · anthony-tuininga · commit 113c31edd845 · 2017-03-09T13:00:24.000-07:00
diff --git a/samples/ImplicitResults.py b/samples/ImplicitResults.py
@@ -0,0 +1,49 @@
+#------------------------------------------------------------------------------
+# Copyright 2016, 2017, Oracle and/or its affiliates. All rights reserved.
+#
+# Portions Copyright 2007-2015, Anthony Tuininga. All rights reserved.
+#
+# Portions Copyright 2001-2007, Computronix (Canada) Ltd., Edmonton, Alberta,
+# Canada. All rights reserved.
+#------------------------------------------------------------------------------
+
+#------------------------------------------------------------------------------
+# ImplicitResults.py
+#   This script demonstrates the use of the 12.1 feature that allows PL/SQL
+# procedures to return result sets implicitly, without having to explicitly
+# define them.
+#------------------------------------------------------------------------------
+
+from __future__ import print_function
+
+import cx_Oracle
+
+con = cx_Oracle.connect("cx_Oracle/dev@localhost/orcl")
+cur = con.cursor()
+
+# use PL/SQL block to return two cursors
+cur.execute("""
+        declare
+            c1 sys_refcursor;
+            c2 sys_refcursor;
+        begin
+
+            open c1 for
+            select * from TestNumbers;
+
+            dbms_sql.return_result(c1);
+
+            open c2 for
+            select * from TestStrings;
+
+            dbms_sql.return_result(c2);
+
+        end;""")
+
+# display results
+for ix, resultSet in enumerate(cur.getimplicitresults()):
+    print("Result Set #" + str(ix + 1))
+    for row in resultSet:
+        print(row)
+    print()
+
diff --git a/samples/ScrollableCursors.py b/samples/ScrollableCursors.py
@@ -0,0 +1,70 @@
+#------------------------------------------------------------------------------
+# Copyright 2016, 2017, Oracle and/or its affiliates. All rights reserved.
+#
+# Portions Copyright 2007-2015, Anthony Tuininga. All rights reserved.
+#
+# Portions Copyright 2001-2007, Computronix (Canada) Ltd., Edmonton, Alberta,
+# Canada. All rights reserved.
+#------------------------------------------------------------------------------
+
+#------------------------------------------------------------------------------
+# ScrollableCursors.py
+#   This script demonstrates how to use scrollable cursors. These allow moving
+# forward and backward in the result set but incur additional overhead on the
+# server to retain this information.
+#------------------------------------------------------------------------------
+
+from __future__ import print_function
+
+import cx_Oracle
+
+con = cx_Oracle.connect("cx_Oracle/dev@localhost/orcl")
+
+# show all of the rows available in the table
+cur = con.cursor()
+cur.execute("select * from TestStrings order by IntCol")
+print("ALL ROWS")
+for row in cur:
+    print(row)
+print()
+
+# create a scrollable cursor
+cur = con.cursor(scrollable = True)
+
+# set array size smaller than the default (100) to force scrolling by the
+# database; otherwise, scrolling occurs directly within the buffers
+cur.arraysize = 3
+cur.execute("select * from TestStrings order by IntCol")
+
+# scroll to last row in the result set; the first parameter is not needed and
+# is ignored)
+cur.scroll(mode = "last")
+print("LAST ROW")
+print(cur.fetchone())
+print()
+
+# scroll to the first row in the result set; the first parameter not needed and
+# is ignored
+cur.scroll(mode = "first")
+print("FIRST ROW")
+print(cur.fetchone())
+print()
+
+# scroll to an absolute row number
+cur.scroll(5, mode = "absolute")
+print("ROW 5")
+print(cur.fetchone())
+print()
+
+# scroll forward six rows (the mode parameter defaults to relative)
+cur.scroll(3)
+print("SKIP 3 ROWS")
+print(cur.fetchone())
+print()
+
+# scroll backward four rows (the mode parameter defaults to relative)
+cur.scroll(-4)
+print("SKIP BACK 4 ROWS")
+print(cur.fetchone())
+print()
+
