ORA-25263 Error when trying to dequeue a message by msgid

[patecone]

Hi everyone, I'm trying to dequeue a message from a single consumer queue stored in an Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production. Since it's the first time I'm dequeuing using msgid option - I did some tests using condition - I built a simple test-case where I first enqueue a dummy message, check if the msgid is valued and finally try to retrieve the message using the msgid I got in message properties:

import unittest
import binascii
from cx_Oracle import makedsn, DEQ_NO_WAIT, init_oracle_client, version, clientversion
from sqlalchemy import create_engine

# Some code here with user, passwd, makedns, info prints and init_oracle_client

class MyTestCase(unittest.TestCase):
    def test_something(self):
        e = create_engine(f"oracle://{user}:{passwd}@{dsn}", echo=True)
        with e.connect() as econn:
            conn = econn.connection
            msg_type = conn.gettype('MESSAGE_T')
            queue = conn.queue('TASK_Q', msg_type)
            # enqueue a message
            msg = msg_type.newobject()
            msg.SUBJECT = 'prova'
            props = conn.msgproperties(payload=msg)
            queue.enqone(props)
            conn.commit()
            print(f"Accodato {binascii.hexlify(props.msgid)}")
            self.assertIsNotNone(props.msgid)  
            # dequeue the same message
            queue.deqOptions.msgid = props.msgid
            queue.deqOptions.wait = DEQ_NO_WAIT
            msg1 = queue.deqone()
            self.assertEqual(msg1.payload.subject, msg.SUBJECT)

I thought that by passing to deqOptions the same msgid returned by enqone I would have retrieved exactly the message I enqueued, but this is what I get

platform.platform: Windows-10-10.0.19041-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.9.0
cx_Oracle.version: 8.3.0
cx_Oracle.clientversion: (12, 2, 0, 1, 0)
2022-10-13 14:05:20,596 INFO sqlalchemy.engine.Engine select sys_context( 'userenv', 'current_schema' ) from dual
2022-10-13 14:05:20,596 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-10-13 14:05:20,833 INFO sqlalchemy.engine.Engine SELECT value FROM v$parameter WHERE name = 'compatible'
2022-10-13 14:05:20,833 INFO sqlalchemy.engine.Engine [raw sql] {}
2022-10-13 14:05:20,936 INFO sqlalchemy.engine.Engine select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
2022-10-13 14:05:20,936 INFO sqlalchemy.engine.Engine [raw sql] {}
Accodato b'eaea52d64b591f9ee053044418acd6e2'
E
======================================================================
ERROR: test_something (__main__.MyTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "C:\Users\ ... \test_oracleaq.py", line 40, in test_something
    msg1 = queue.deqone()
cx_Oracle.DatabaseError: ORA-25263: no message in queue TASK_Q with message ID 00000000000000000000000000000000

----------------------------------------------------------------------
Ran 1 test in 2.464s

FAILED (errors=1)

Process finished with exit code 1

can someone tell me if I'm doing something wrong?

Best, Cristiano

PS: I'm using SQLAchemy because my production code heavily leverages on the framework and I will have my database connections made through alchemy core rather than cx_Oracle.

[patecone]

Small update, same error also on linux (RHEL8, this time connecting with cx_Oracle.connect)

platform.platform: Linux-4.18.0-372.16.1.el8_6.x86_64-x86_64-with-glibc2.28
sys.maxsize > 2**32: True
platform.python_version: 3.9.7
cx_Oracle.version: 8.3.0
cx_Oracle.clientversion: (12, 2, 0, 1, 0)
E
======================================================================
ERROR: test_something (__main__.MyTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/cesi.lan/candia/go.py", line 37, in test_something
    msg1 = queue.deqMany(1)
cx_Oracle.DatabaseError: ORA-25263: no message in queue TASK_Q with message ID 00000000000000000000000000000000

[anthony-tuininga]

Can you share the SQL used to build the object and the queue? And the complete test case using just cx_Oracle? That would be very helpful.

[patecone]

Hi @anthony-tuininga , this is the test case using cx_Oracle only:

import unittest
import binascii
from cx_Oracle import makedsn, DEQ_NO_WAIT, init_oracle_client, version, clientversion, Binary, connect
user = 'my-user'
passwd = 'my-passwd'
dsn = makedsn(host= 'my-hosy',
              port=1521,
              service_name= 'my-servicename')
import sys
import platform

print("platform.platform:", platform.platform())
print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
print("platform.python_version:", platform.python_version())
print("cx_Oracle.version:", version)
print("cx_Oracle.clientversion:", clientversion())

class MyTestCase(unittest.TestCase):
    def test_something(self):
        with connect(f"{user}/{passwd}@{dsn}") as conn:
            msg_type = conn.gettype('MESSAGE_T')
            queue = conn.queue('TASK_Q', msg_type)
            # enqueue a message
            msg = msg_type.newobject()
            msg.SUBJECT = 'prova'
            props = conn.msgproperties(payload=msg)
            queue.enqone(props)
            conn.commit()
            print(f"Accodato {binascii.hexlify(props.msgid)}")
            self.assertIsNotNone(props.msgid)
            # dequeue the same message
            queue.deqOptions.msgid = props.msgid
            queue.deqOptions.wait = DEQ_NO_WAIT
            msg1 = queue.deqmany(1)
            self.assertEqual(msg1.payload.subject, msg.SUBJECT)

if __name__ == '__main__':
    unittest.main()

This is the SQL queue creation script - it creates two queues, it was for a wider test.

[anthony-tuininga]

Ok. This issue was corrected in ODPI-C 4.4 released in May 2022 as can be seen in the release notes. cx_Oracle does not have this version but I can confirm that updating the version of ODPI-C used by cx_Oracle does correct this issue.

The new driver (python-oracledb) does have this version of ODPI-C so it works today and I would recommend upgrading to it. For your sample, the only changes you have to make are to add this code:

import oracledb
oracledb.init_oracle_client()

That code needs to be added before establishing the connection. You also need to change your import statement from cx_Oracle to oracledb. You can take a look at the documentation for all of the details.

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not been updated for a month.