Skip to content

Commit 3c4d83f

Browse files
anthony-tuiningaanthony-tuininga
committed
Adjusted tutorial to use new AQ syntax.
1 parent e6a825d commit 3c4d83f

File tree

5 files changed

+131
-84
lines changed

5 files changed

+131
-84
lines changed

‎samples/tutorial/Python-and-Oracle-Database-Scripting-for-the-Future.html‎

Lines changed: 65 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -104,7 +104,7 @@ <h2><a name="preface">Preface</a></h2>
104104

105105
<ol>
106106
<li><a target="_blank" href="https://www.python.org/">Python</a> (3.6 preferred but 2.7 should work)</li>
107-
<li>cx_Oracle (version 7 preferred but 6.3 or later should work) and Oracle Instant Client Package - Basic (version 18.3 preferred but 12.2 should work)
107+
<li>cx_Oracle (version 7.2 preferred but 6.3 or later should work, except for the section on Advanced Queuing which requires version 7.2 or later) and Oracle Instant Client Package - Basic (version 19.3 preferred but 18.3 or 12.2 should also work)
108108
<ul>
109109
<li><a target="_blank" href="http://cx-oracle.readthedocs.io/en/latest/installation.html#installing-cx-oracle-on-linux">Linux</a></li>
110110
<li><a target="_blank" href="http://cx-oracle.readthedocs.io/en/latest/installation.html#installing-cx-oracle-on-macos">macOS</a> - please note the special instructions for macOS in the link.</li>
@@ -1976,7 +1976,7 @@ <h4>7.2 Fetching a CLOB as a string</h4>
19761976
end if;
19771977
end;""")
19781978

1979-
# Create type
1979+
# Create a type
19801980
print("Creating books type UDT_BOOK...")
19811981
cur.execute("""
19821982
create type %s as object (
@@ -1988,38 +1988,47 @@ <h4>7.2 Fetching a CLOB as a string</h4>
19881988
# Create queue table and queue and start the queue
19891989
print("Creating queue table...")
19901990
cur.callproc("dbms_aqadm.create_queue_table",
1991-
(QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
1991+
(QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
19921992
cur.callproc("dbms_aqadm.create_queue", (QUEUE_NAME, QUEUE_TABLE_NAME))
19931993
cur.callproc("dbms_aqadm.start_queue", (QUEUE_NAME,))
19941994

1995-
# Enqueue a few messages
19961995
booksType = con.gettype(BOOK_TYPE_NAME)
1997-
book1 = booksType.newobject()
1998-
book1.TITLE = "The Fellowship of the Ring"
1999-
book1.AUTHORS = "Tolkien, J.R.R."
2000-
book1.PRICE = decimal.Decimal("10.99")
2001-
book2 = booksType.newobject()
2002-
book2.TITLE = "Harry Potter and the Philosopher's Stone"
2003-
book2.AUTHORS = "Rowling, J.K."
2004-
book2.PRICE = decimal.Decimal("7.99")
2005-
options = con.enqoptions()
2006-
messageProperties = con.msgproperties()
2007-
for book in (book1, book2):
2008-
print("Enqueuing book", book.TITLE)
2009-
con.enq(QUEUE_NAME, options, messageProperties, book)
2010-
con.commit()
1996+
queue = con.queue(QUEUE_NAME, booksType)
1997+
1998+
# Enqueue a few messages
1999+
print("Enqueuing messages...")
2000+
2001+
BOOK_DATA = [
2002+
("The Fellowship of the Ring", "Tolkien, J.R.R.", decimal.Decimal("10.99")),
2003+
("Harry Potter and the Philosopher's Stone", "Rowling, J.K.",
2004+
decimal.Decimal("7.99"))
2005+
]
2006+
2007+
for title, authors, price in BOOK_DATA:
2008+
book = booksType.newobject()
2009+
book.TITLE = title
2010+
book.AUTHORS = authors
2011+
book.PRICE = price
2012+
print(title)
2013+
queue.enqOne(con.msgproperties(payload=book))
2014+
con.commit()
20112015

20122016
# Dequeue the messages
2013-
options = con.deqoptions()
2014-
options.navigation = cx_Oracle.DEQ_FIRST_MSG
2015-
options.wait = cx_Oracle.DEQ_NO_WAIT
2016-
while con.deq(QUEUE_NAME, options, messageProperties, book):
2017-
print("Dequeued book", book.TITLE)
2018-
con.commit()
2017+
print("\nDequeuing messages...")
2018+
queue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT
2019+
while True:
2020+
props = queue.deqOne()
2021+
if not props:
2022+
break
2023+
print(props.payload.TITLE)
2024+
con.commit()
2025+
2026+
print("\nDone.")
20192027
</pre>
20202028

20212029
<p>This file sets up Advanced Queuing using Oracle's DBMS_AQADM
2022-
package. The queue is used for passing Oracle UDT_BOOK objects.</p>
2030+
package. The queue is used for passing Oracle UDT_BOOK objects. The
2031+
file uses AQ interface features enhanced in cx_Oracle 7.2.</p>
20232032

20242033
<p>Run the file:</p>
20252034

@@ -2030,14 +2039,37 @@ <h4>7.2 Fetching a CLOB as a string</h4>
20302039
<p>To experiment, split the code into three files: one to create and
20312040
start the queue, and two other files to queue and dequeue messages.
20322041
Experiment running the queue and dequeue files concurrently in
2033-
separate terminal windows. If you are stuck, look in the
2034-
<code>solutions</code> directory at the <code>aq-dequeue.py</code>,
2035-
<code>aq-enqueue.py</code> and <code>aq-queuestart.py</code>
2036-
files.</p>
2037-
2038-
<p>Try changing the dequeue options and mode. For example change the
2039-
dequeue <code>options.wait</code> value to
2040-
<code>cx_Oracle.DEQ_WAIT_FOREVER</code>.</p>
2042+
separate terminal windows.</p>
2043+
2044+
<p>Try removing the <code>commit()</code> call in
2045+
<code>aq-dequeue.py</code>. Now run <code>aq-enqueue.py</code> once
2046+
and then <code>aq-dequeue.py</code> several times. The same messages
2047+
will be available each time you try to dequeue them.</p>
2048+
2049+
<p>Change <code>aq-dequeue.py</code> to commit in a separate
2050+
transaction by changing the "visibility" setting:</p>
2051+
2052+
<pre>
2053+
queue.deqOptions.visibility = cx_Oracle.DEQ_IMMEDIATE
2054+
</pre>
2055+
2056+
<p>This gives the same behavior as the original code.</p>
2057+
2058+
<p>Now change the options of enqueued messages so that they expire from the
2059+
queue if they have not been dequeued after four seconds:</p>
2060+
2061+
<pre>
2062+
queue.enqOne(con.msgproperties(payload=book, expiration=4))
2063+
</pre>
2064+
2065+
<p>Now run <code>aq-enqueue.py</code> and wait four seconds before you
2066+
run <code>aq-dequeue.py</code>. There should be no messages to
2067+
dequeue. </p>
2068+
2069+
<p>If you are stuck, look in the <code>solutions</code> directory at
2070+
the <code>aq-dequeue.py</code>, <code>aq-enqueue.py</code> and
2071+
<code>aq-queuestart.py</code> files.</p>
2072+
20412073

20422074
</ul>
20432075
</li>

‎samples/tutorial/aq.py‎

Lines changed: 32 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
#------------------------------------------------------------------------------
44

55
#------------------------------------------------------------------------------
6-
# Copyright (c) 2017, 2018, Oracle and/or its affiliates. All rights reserved.
6+
# Copyright (c) 2017, 2019, Oracle and/or its affiliates. All rights reserved.
77
#------------------------------------------------------------------------------
88

99
from __future__ import print_function
@@ -32,7 +32,7 @@
3232
end if;
3333
end;""")
3434

35-
# Create type
35+
# Create a type
3636
print("Creating books type UDT_BOOK...")
3737
cur.execute("""
3838
create type %s as object (
@@ -44,31 +44,39 @@
4444
# Create queue table and queue and start the queue
4545
print("Creating queue table...")
4646
cur.callproc("dbms_aqadm.create_queue_table",
47-
(QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
47+
(QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
4848
cur.callproc("dbms_aqadm.create_queue", (QUEUE_NAME, QUEUE_TABLE_NAME))
4949
cur.callproc("dbms_aqadm.start_queue", (QUEUE_NAME,))
5050

51-
# Enqueue a few messages
5251
booksType = con.gettype(BOOK_TYPE_NAME)
53-
book1 = booksType.newobject()
54-
book1.TITLE = "The Fellowship of the Ring"
55-
book1.AUTHORS = "Tolkien, J.R.R."
56-
book1.PRICE = decimal.Decimal("10.99")
57-
book2 = booksType.newobject()
58-
book2.TITLE = "Harry Potter and the Philosopher's Stone"
59-
book2.AUTHORS = "Rowling, J.K."
60-
book2.PRICE = decimal.Decimal("7.99")
61-
options = con.enqoptions()
62-
messageProperties = con.msgproperties()
63-
for book in (book1, book2):
64-
print("Enqueuing book", book.TITLE)
65-
con.enq(QUEUE_NAME, options, messageProperties, book)
66-
con.commit()
52+
queue = con.queue(QUEUE_NAME, booksType)
53+
54+
# Enqueue a few messages
55+
print("Enqueuing messages...")
56+
57+
BOOK_DATA = [
58+
("The Fellowship of the Ring", "Tolkien, J.R.R.", decimal.Decimal("10.99")),
59+
("Harry Potter and the Philosopher's Stone", "Rowling, J.K.",
60+
decimal.Decimal("7.99"))
61+
]
62+
63+
for title, authors, price in BOOK_DATA:
64+
book = booksType.newobject()
65+
book.TITLE = title
66+
book.AUTHORS = authors
67+
book.PRICE = price
68+
print(title)
69+
queue.enqOne(con.msgproperties(payload=book))
70+
con.commit()
6771

6872
# Dequeue the messages
69-
options = con.deqoptions()
70-
options.navigation = cx_Oracle.DEQ_FIRST_MSG
71-
options.wait = cx_Oracle.DEQ_NO_WAIT
72-
while con.deq(QUEUE_NAME, options, messageProperties, book):
73-
print("Dequeued book", book.TITLE)
74-
con.commit()
73+
print("\nDequeuing messages...")
74+
queue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT
75+
while True:
76+
props = queue.deqOne()
77+
if not props:
78+
break
79+
print(props.payload.TITLE)
80+
con.commit()
81+
82+
print("\nDone.")

‎samples/tutorial/solutions/aq-dequeue.py‎

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
#------------------------------------------------------------------------------
44

55
#------------------------------------------------------------------------------
6-
# Copyright (c) 2017, 2018, Oracle and/or its affiliates. All rights reserved.
6+
# Copyright (c) 2017, 2019, Oracle and/or its affiliates. All rights reserved.
77
#------------------------------------------------------------------------------
88

99
from __future__ import print_function
@@ -20,12 +20,16 @@
2020
QUEUE_TABLE_NAME = "BOOK_QUEUE_TABLE"
2121

2222
# Dequeue the messages
23-
options = con.deqoptions()
24-
options.navigation = cx_Oracle.DEQ_FIRST_MSG
25-
options.wait = cx_Oracle.DEQ_NO_WAIT
26-
messageProperties = con.msgproperties()
2723
booksType = con.gettype(BOOK_TYPE_NAME)
28-
book = booksType.newobject()
29-
while con.deq(QUEUE_NAME, options, messageProperties, book):
30-
print("Dequeued book", book.TITLE)
31-
con.commit()
24+
queue = con.queue(QUEUE_NAME, booksType)
25+
queue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT
26+
queue.deqOptions.visibility = cx_Oracle.DEQ_IMMEDIATE
27+
28+
print("\nDequeuing messages...")
29+
while True:
30+
props = queue.deqOne()
31+
if not props:
32+
break
33+
print(props.payload.TITLE)
34+
35+
print("\nDone.")

‎samples/tutorial/solutions/aq-enqueue.py‎

Lines changed: 18 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
#------------------------------------------------------------------------------
44

55
#------------------------------------------------------------------------------
6-
# Copyright (c) 2017, 2018, Oracle and/or its affiliates. All rights reserved.
6+
# Copyright (c) 2017, 2019, Oracle and/or its affiliates. All rights reserved.
77
#------------------------------------------------------------------------------
88

99
from __future__ import print_function
@@ -20,18 +20,21 @@
2020
QUEUE_TABLE_NAME = "BOOK_QUEUE_TABLE"
2121

2222
# Enqueue a few messages
23+
print("Enqueuing messages...")
24+
25+
BOOK_DATA = [
26+
("The Fellowship of the Ring", "Tolkien, J.R.R.", decimal.Decimal("10.99")),
27+
("Harry Potter and the Philosopher's Stone", "Rowling, J.K.", decimal.Decimal("7.99"))
28+
]
29+
2330
booksType = con.gettype(BOOK_TYPE_NAME)
24-
book1 = booksType.newobject()
25-
book1.TITLE = "The Fellowship of the Ring"
26-
book1.AUTHORS = "Tolkien, J.R.R."
27-
book1.PRICE = decimal.Decimal("10.99")
28-
book2 = booksType.newobject()
29-
book2.TITLE = "Harry Potter and the Philosopher's Stone"
30-
book2.AUTHORS = "Rowling, J.K."
31-
book2.PRICE = decimal.Decimal("7.99")
32-
options = con.enqoptions()
33-
messageProperties = con.msgproperties()
34-
for book in (book1, book2):
35-
print("Enqueuing book", book.TITLE)
36-
con.enq(QUEUE_NAME, options, messageProperties, book)
37-
con.commit()
31+
queue = con.queue(QUEUE_NAME, booksType)
32+
33+
for title, authors, price in BOOK_DATA:
34+
book = booksType.newobject()
35+
book.TITLE = title
36+
book.AUTHORS = authors
37+
book.PRICE = price
38+
print(title)
39+
queue.enqOne(con.msgproperties(payload=book, expiration=4))
40+
con.commit()

‎samples/tutorial/solutions/aq-queuestart.py‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
#------------------------------------------------------------------------------
44

55
#------------------------------------------------------------------------------
6-
# Copyright (c) 2017, 2018, Oracle and/or its affiliates. All rights reserved.
6+
# Copyright (c) 2017, 2019, Oracle and/or its affiliates. All rights reserved.
77
#------------------------------------------------------------------------------
88

99
from __future__ import print_function
@@ -32,7 +32,7 @@
3232
end if;
3333
end;""")
3434

35-
# Create type
35+
# Create a type
3636
print("Creating books type UDT_BOOK...")
3737
cur.execute("""
3838
create type %s as object (
@@ -44,6 +44,6 @@
4444
# Create queue table and queue and start the queue
4545
print("Creating queue table...")
4646
cur.callproc("dbms_aqadm.create_queue_table",
47-
(QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
47+
(QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
4848
cur.callproc("dbms_aqadm.create_queue", (QUEUE_NAME, QUEUE_TABLE_NAME))
4949
cur.callproc("dbms_aqadm.start_queue", (QUEUE_NAME,))

0 commit comments

Comments
 (0)