Added samples for session callbacks in Python and PL/SQL. · licolin/python-cx_Oracle@b51ed5b · GitHub
Skip to content

Commit b51ed5b

Browse files
anthony-tuiningaanthony-tuininga
committed
Added samples for session callbacks in Python and PL/SQL.
1 parent 1824dd1 commit b51ed5b

3 files changed

Lines changed: 363 additions & 0 deletions

File tree

‎samples/SessionCallback.py‎

Lines changed: 139 additions & 0 deletions

‎samples/SessionCallbackPLSQL.py‎

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,104 @@
1+
#------------------------------------------------------------------------------
2+
# Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved.
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# SessionCallbackPLSQL.py
7+
#
8+
# Demonstrate how to use a session callback written in PL/SQL. The callback is
9+
# invoked whenever the tag requested by the application does not match the tag
10+
# associated with the session in the pool. It should be used to set session
11+
# state, so that the application can count on known session state, which allows
12+
# the application to reduce the number of round trips to the database.
13+
#
14+
# The primary advantage to this approach over the equivalent approach shown in
15+
# SessionCallback.py is when DRCP is used, as the callback is invoked on the
16+
# server and no round trip is required to set state.
17+
#
18+
# This script requires cx_Oracle 7.1 and higher.
19+
#------------------------------------------------------------------------------
20+
21+
from __future__ import print_function
22+
23+
import cx_Oracle
24+
import SampleEnv
25+
26+
# create pool with session callback defined
27+
pool = cx_Oracle.SessionPool(SampleEnv.MAIN_USER, SampleEnv.MAIN_PASSWORD,
28+
SampleEnv.CONNECT_STRING, min=2, max=5, increment=1, threaded=True,
29+
sessionCallback="pkg_SessionCallback.TheCallback")
30+
31+
# truncate table logging calls to PL/SQL session callback
32+
conn = pool.acquire()
33+
cursor = conn.cursor()
34+
cursor.execute("truncate table PLSQLSessionCallbacks")
35+
conn.close()
36+
37+
# acquire session without specifying a tag; the callback will not be invoked as
38+
# a result and no session state will be changed
39+
print("(1) acquire session without tag")
40+
conn = pool.acquire()
41+
cursor = conn.cursor()
42+
cursor.execute("select to_char(current_date) from dual")
43+
result, = cursor.fetchone()
44+
print("main(): result is", repr(result))
45+
conn.close()
46+
47+
# acquire session, specifying a tag; since the session returned has no tag,
48+
# the callback will be invoked; session state will be changed and the tag will
49+
# be saved when the connection is closed
50+
print("(2) acquire session with tag")
51+
conn = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")
52+
cursor = conn.cursor()
53+
cursor.execute("select to_char(current_date) from dual")
54+
result, = cursor.fetchone()
55+
print("main(): result is", repr(result))
56+
conn.close()
57+
58+
# acquire session, specifying the same tag; since a session exists in the pool
59+
# with this tag, it will be returned and the callback will not be invoked but
60+
# the connection will still have the session state defined previously
61+
print("(3) acquire session with same tag")
62+
conn = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")
63+
cursor = conn.cursor()
64+
cursor.execute("select to_char(current_date) from dual")
65+
result, = cursor.fetchone()
66+
print("main(): result is", repr(result))
67+
conn.close()
68+
69+
# acquire session, specifying a different tag; since no session exists in the
70+
# pool with this tag, a new session will be returned and the callback will be
71+
# invoked; session state will be changed and the tag will be saved when the
72+
# connection is closed
73+
print("(4) acquire session with different tag")
74+
conn = pool.acquire(tag="NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC")
75+
cursor = conn.cursor()
76+
cursor.execute("select to_char(current_date) from dual")
77+
result, = cursor.fetchone()
78+
print("main(): result is", repr(result))
79+
conn.close()
80+
81+
# acquire session, specifying a different tag but also specifying that a
82+
# session with any tag can be acquired from the pool; a session with one of the
83+
# previously set tags will be returned and the callback will be invoked;
84+
# session state will be changed and the tag will be saved when the connection
85+
# is closed
86+
print("(4) acquire session with different tag but match any also specified")
87+
conn = pool.acquire(tag="NLS_DATE_FORMAT=FULL;TIME_ZONE=MST", matchanytag=True)
88+
cursor = conn.cursor()
89+
cursor.execute("select to_char(current_date) from dual")
90+
result, = cursor.fetchone()
91+
print("main(): result is", repr(result))
92+
conn.close()
93+
94+
# acquire session and display results from PL/SQL session logs
95+
conn = pool.acquire()
96+
cursor = conn.cursor()
97+
cursor.execute("""
98+
select RequestedTag, ActualTag
99+
from PLSQLSessionCallbacks
100+
order by FixupTimestamp""")
101+
print("(5) PL/SQL session callbacks")
102+
for requestedTag, actualTag in cursor:
103+
print("Requested:", requestedTag, "Actual:", actualTag)
104+

‎samples/sql/SetupSamples.sql‎

Lines changed: 120 additions & 0 deletions

0 commit comments

Comments
 (0)