X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Improved Oracle Advanced Queuing in cx_Oracle 7.2

Christopher Jones
Senior Principal Product Manager

cx_Oracle logo

cx_Oracle 7.2, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features.

 

It's time for a tidy batch of enhancements and fixes to cx_Oracle. The top changes:

  • Improved Oracle Advanced Queuing support:

    • Added support for enqueue and dequeue of "RAW" payloads (adding to the previous support for object AQ messages). This lets you used strings and buffers for messages.

    • Added bulk enqueue and dequeue of messages. This can significantly improve performance when dealing with multiple messages.

    • To align the new and existing AQ support, a new method Connection.queue() was introduced, This returns a queue, which has methods and properties for AQ functionality. The previous AQ methods Connection.deq(), Connection.deqOptions(), Connection.enq(), and Connection.enqOptions() are deprecated and will be removed in cx_Oracle 8.

     

  • Added support for SODA bulk insert, available when cx_Oracle uses Oracle client libraries 18.5 and higher. This is currently in 'preview' status.

  • Added support for setting the LOB prefetch length indicator in order to reduce the number of round trips when processing LOB values.

Thanks are deserved, as always, to everyone who has contributed directly and indirectly.

Installing or Upgrading cx_Oracle

You can install or upgrade cx_Oracle by running:

python -m pip install cx_Oracle --upgrade

See cx_Oracle 7 Installation for details.  If the upgrade seems to have no effect, add the pip install option --force-reinstall.

Linux packages will be available soon from Oracle Linux for Python Developers.

cx_Oracle's new AQ interface

Here's an example of the new interface to AQ. I use a RAW queue to send simple strings.

First we need a RAW queue itself, which I'll call DEMO_RAW_QUEUE. This has to be configured in the database, for example in SQL*Plus:

dbms_aqadm.create_queue_table('MY_QUEUE_TABLE', 'RAW');
dbms_aqadm.create_queue('DEMO_RAW_QUEUE', 'MY_QUEUE_TABLE');
dbms_aqadm.start_queue('DEMO_RAW_QUEUE');

To send messages in Python you connect and get a 'queue' object. The queue can be used for enqueuing, dequeuing, or both. Refer to the cx_Oracle and Oracle Advanced Queuing documentation for details on enqueue and dequeue options. Here I use ENQ_IMMEDIATE so that a commit on the connection is not needed to commit messages:

import cx_Oracle
connection = cx_Oracle.connect(. . .)

queue = connection.queue("DEMO_RAW_QUEUE")
queue.enqOptions.visibility = cx_Oracle.ENQ_IMMEDIATE

Now messages can be queued. Here I'm sending three messages, setting an expiration of 60 seconds on them. This means that if no dequeue operation occurs in 60 seconds then the messages will be dropped from the queue:

PAYLOAD_DATA = [
    "The first message",
    "The second message",
    "The third message"
]
for data in PAYLOAD_DATA:
    queue.enqOne(connection.msgproperties(payload=data, expiration=60))

Dequeuing is similar. Strings are encoded as buffers, so a decode() is used to get the text:

queue = connection.queue("DEMO_RAW_QUEUE")
queue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT
queue.deqOptions.navigation = cx_Oracle.DEQ_FIRST_MSG

while True:
    props = queue.deqOne()
    if not props:
        break
    print(props.payload.decode())
connection.commit()

There are examples in the samples directory.

Readers with a good memory will recall I mentioned bulk AQ operations but the example above used a loop for each string. You can convert the examples easily. The bulk enqueue operation simply takes an array of messages. The bulk dequeue method returns an array of messages; the array length is up to a specified size.

cx_Oracle References

Home page: oracle.github.io/python-cx_Oracle/index.html

Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: cx-oracle.readthedocs.io/en/latest/index.html

Release Notes: cx-oracle.readthedocs.io/en/latest/releasenotes.html

Source Code Repository: github.com/oracle/python-cx_Oracle

Facebook group: www.facebook.com/groups/oraclescripting

Questions: github.com/oracle/python-cx_Oracle/issues

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.