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

File tree

3 files changed

+363
-0
lines changed

3 files changed

+363
-0
lines changed

‎samples/SessionCallback.py‎

Lines changed: 139 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,139 @@
1+
#------------------------------------------------------------------------------
2+
# Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved.
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# SessionCallback.py
7+
#
8+
# Demonstrate how to use a session callback written in Python. The callback is
9+
# invoked whenever a newly created session is acquired from the pool, or when
10+
# the requested tag does not match the tag that is associated with the
11+
# session. It is generally used to set session state, so that the application
12+
# can count on known session state, which allows the application to reduce the
13+
# number of round trips made to the database.
14+
#
15+
# This script requires cx_Oracle 7.1 and higher.
16+
#------------------------------------------------------------------------------
17+
18+
from __future__ import print_function
19+
20+
import cx_Oracle
21+
import SampleEnv
22+
23+
# define a dictionary of NLS_DATE_FORMAT formats supported by this sample
24+
SUPPORTED_FORMATS = {
25+
"SIMPLE" : "'YYYY-MM-DD HH24:MI'",
26+
"FULL" : "'YYYY-MM-DD HH24:MI:SS'"
27+
}
28+
29+
# define a dictionary of TIME_ZONE values supported by this sample
30+
SUPPORTED_TIME_ZONES = {
31+
"UTC" : "'UTC'",
32+
"MST" : "'-07:00'"
33+
}
34+
35+
# define a dictionary of keys that are supported by this sample
36+
SUPPORTED_KEYS = {
37+
"NLS_DATE_FORMAT" : SUPPORTED_FORMATS,
38+
"TIME_ZONE" : SUPPORTED_TIME_ZONES
39+
}
40+
41+
# define session callback
42+
def init_session(conn, requestedTag):
43+
44+
# display the requested and actual tags
45+
print("init_session(): requested tag=%r, actual tag=%r" % \
46+
(requestedTag, conn.tag))
47+
48+
# tags are expected to be in the form "key1=value1;key2=value2"
49+
# in this example, they are used to set NLS parameters and the tag is
50+
# parsed to validate it
51+
if requestedTag is not None:
52+
stateParts = []
53+
for directive in requestedTag.split(";"):
54+
parts = directive.split("=")
55+
if len(parts) != 2:
56+
raise ValueError("Tag must contain key=value pairs")
57+
key, value = parts
58+
valueDict = SUPPORTED_KEYS.get(key)
59+
if valueDict is None:
60+
raise ValueError("Tag only supports keys: %s" % \
61+
(", ".join(SUPPORTED_KEYS)))
62+
actualValue = valueDict.get(value)
63+
if actualValue is None:
64+
raise ValueError("Key %s only supports values: %s" % \
65+
(key, ", ".join(valueDict)))
66+
stateParts.append("%s = %s" % (key, actualValue))
67+
sql = "alter session set %s" % " ".join(stateParts)
68+
cursor = conn.cursor()
69+
cursor.execute(sql)
70+
71+
# assign the requested tag to the connection so that when the connection
72+
# is closed, it will automatically be retagged; note that if the requested
73+
# tag is None (no tag was requested) this has no effect
74+
conn.tag = requestedTag
75+
76+
77+
# create pool with session callback defined
78+
pool = cx_Oracle.SessionPool(SampleEnv.MAIN_USER, SampleEnv.MAIN_PASSWORD,
79+
SampleEnv.CONNECT_STRING, min=2, max=5, increment=1, threaded=True,
80+
sessionCallback=init_session)
81+
82+
# acquire session without specifying a tag; since the session returned is
83+
# newly created, the callback will be invoked but since there is no tag
84+
# specified, no session state will be changed
85+
print("(1) acquire session without tag")
86+
conn = pool.acquire()
87+
cursor = conn.cursor()
88+
cursor.execute("select to_char(current_date) from dual")
89+
result, = cursor.fetchone()
90+
print("main(): result is", repr(result))
91+
conn.close()
92+
93+
# acquire session, specifying a tag; since the session returned has no tag,
94+
# the callback will be invoked; session state will be changed and the tag will
95+
# be saved when the connection is closed
96+
print("(2) acquire session with tag")
97+
conn = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")
98+
cursor = conn.cursor()
99+
cursor.execute("select to_char(current_date) from dual")
100+
result, = cursor.fetchone()
101+
print("main(): result is", repr(result))
102+
conn.close()
103+
104+
# acquire session, specifying the same tag; since a session exists in the pool
105+
# with this tag, it will be returned and the callback will not be invoked but
106+
# the connection will still have the session state defined previously
107+
print("(3) acquire session with same tag")
108+
conn = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")
109+
cursor = conn.cursor()
110+
cursor.execute("select to_char(current_date) from dual")
111+
result, = cursor.fetchone()
112+
print("main(): result is", repr(result))
113+
conn.close()
114+
115+
# acquire session, specifying a different tag; since no session exists in the
116+
# pool with this tag, a new session will be returned and the callback will be
117+
# invoked; session state will be changed and the tag will be saved when the
118+
# connection is closed
119+
print("(4) acquire session with different tag")
120+
conn = pool.acquire(tag="NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC")
121+
cursor = conn.cursor()
122+
cursor.execute("select to_char(current_date) from dual")
123+
result, = cursor.fetchone()
124+
print("main(): result is", repr(result))
125+
conn.close()
126+
127+
# acquire session, specifying a different tag but also specifying that a
128+
# session with any tag can be acquired from the pool; a session with one of the
129+
# previously set tags will be returned and the callback will be invoked;
130+
# session state will be changed and the tag will be saved when the connection
131+
# is closed
132+
print("(4) acquire session with different tag but match any also specified")
133+
conn = pool.acquire(tag="NLS_DATE_FORMAT=FULL;TIME_ZONE=MST", matchanytag=True)
134+
cursor = conn.cursor()
135+
cursor.execute("select to_char(current_date) from dual")
136+
result, = cursor.fetchone()
137+
print("main(): result is", repr(result))
138+
conn.close()
139+

‎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
Original file line numberDiff line numberDiff line change
@@ -167,6 +167,12 @@ create table &main_user..Ptab (
167167
mydata varchar(20)
168168
);
169169

170+
create table &main_user..PlsqlSessionCallbacks (
171+
RequestedTag varchar2(250),
172+
ActualTag varchar2(250),
173+
FixupTimestamp timestamp
174+
);
175+
170176
-- create queue table and queues for demonstrating advanced queuing
171177
begin
172178
dbms_aqadm.create_queue_table('&main_user..BOOK_QUEUE',
@@ -351,3 +357,117 @@ create or replace package body &main_user..pkg_Demo as
351357
end;
352358
/
353359

360+
--
361+
-- Create package for demoing PL/SQL session callback
362+
--
363+
364+
create or replace package &main_user..pkg_SessionCallback as
365+
366+
procedure TheCallback (
367+
a_RequestedTag varchar2,
368+
a_ActualTag varchar2
369+
);
370+
371+
end;
372+
/
373+
374+
create or replace package body &main_user..pkg_SessionCallback as
375+
376+
type udt_Properties is table of varchar2(64) index by varchar2(64);
377+
378+
procedure LogCall (
379+
a_RequestedTag varchar2,
380+
a_ActualTag varchar2
381+
) is
382+
pragma autonomous_transaction;
383+
begin
384+
insert into PlsqlSessionCallbacks
385+
values (a_RequestedTag, a_ActualTag, systimestamp);
386+
commit;
387+
end;
388+
389+
procedure ParseProperty (
390+
a_Property varchar2,
391+
a_Name out nocopy varchar2,
392+
a_Value out nocopy varchar2
393+
) is
394+
t_Pos number;
395+
begin
396+
t_Pos := instr(a_Property, '=');
397+
if t_Pos = 0 then
398+
raise_application_error(-20000, 'Tag must contain key=value pairs');
399+
end if;
400+
a_Name := substr(a_Property, 1, t_Pos - 1);
401+
a_Value := substr(a_Property, t_Pos + 1);
402+
end;
403+
404+
procedure SetProperty (
405+
a_Name varchar2,
406+
a_Value varchar2
407+
) is
408+
t_ValidValues udt_Properties;
409+
begin
410+
if a_Name = 'TIME_ZONE' then
411+
t_ValidValues('UTC') := 'UTC';
412+
t_ValidValues('MST') := '-07:00';
413+
elsif a_Name = 'NLS_DATE_FORMAT' then
414+
t_ValidValues('SIMPLE') := 'YYYY-MM-DD HH24:MI';
415+
t_ValidValues('FULL') := 'YYYY-MM-DD HH24:MI:SS';
416+
else
417+
raise_application_error(-20000, 'Unsupported session setting');
418+
end if;
419+
if not t_ValidValues.exists(a_Value) then
420+
raise_application_error(-20000, 'Unsupported session setting');
421+
end if;
422+
execute immediate
423+
'ALTER SESSION SET ' || a_Name || '=''' ||
424+
t_ValidValues(a_Value) || '''';
425+
end;
426+
427+
procedure ParseTag (
428+
a_Tag varchar2,
429+
a_Properties out nocopy udt_Properties
430+
) is
431+
t_PropertyName varchar2(64);
432+
t_PropertyValue varchar2(64);
433+
t_StartPos number;
434+
t_EndPos number;
435+
begin
436+
t_StartPos := 1;
437+
while t_StartPos < length(a_Tag) loop
438+
t_EndPos := instr(a_Tag, ';', t_StartPos);
439+
if t_EndPos = 0 then
440+
t_EndPos := length(a_Tag) + 1;
441+
end if;
442+
ParseProperty(substr(a_Tag, t_StartPos, t_EndPos - t_StartPos),
443+
t_PropertyName, t_PropertyValue);
444+
a_Properties(t_PropertyName) := t_PropertyValue;
445+
t_StartPos := t_EndPos + 1;
446+
end loop;
447+
end;
448+
449+
procedure TheCallback (
450+
a_RequestedTag varchar2,
451+
a_ActualTag varchar2
452+
) is
453+
t_RequestedProps udt_Properties;
454+
t_ActualProps udt_Properties;
455+
t_PropertyName varchar2(64);
456+
begin
457+
LogCall(a_RequestedTag, a_ActualTag);
458+
ParseTag(a_RequestedTag, t_RequestedProps);
459+
ParseTag(a_ActualTag, t_ActualProps);
460+
t_PropertyName := t_RequestedProps.first;
461+
while t_PropertyName is not null loop
462+
if not t_ActualProps.exists(t_PropertyName) or
463+
t_ActualProps(t_PropertyName) !=
464+
t_RequestedProps(t_PropertyName) then
465+
SetProperty(t_PropertyName, t_RequestedProps(t_PropertyName));
466+
end if;
467+
t_PropertyName := t_RequestedProps.next(t_PropertyName);
468+
end loop;
469+
end;
470+
471+
end;
472+
/
473+

0 commit comments

Comments
 (0)