You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have used cx_oracle to push and pull user data type structures to an AQ successfully. However, I have not had complete success with the ANYDATA data type. I can push a user data type message onto an ANYDATA queue with success. When I query the record natively (i.e. from the data column in the queue table) I can see that the content (within the ANYDATA column ) is of the enqueued user data type and using the Oracle function CAST I can read out the data. However, dequeueing ANYDATA messages seems to be a non-starter as it seems impossible to use newobject() against the datatype:
msgTypeAnydata = cn.gettype("SYS.ANYDATA") <<== this works
msg = msgTypeAnydata.newobject() <<== this fails
I have only been able to dequeue ANYDATA messages via stored procedures which then locate the object. The essential elements of the stored procedure is:
DBMS_AQ.DEQUEUE( queue_name => 'aqmgr.event_queue', payload => deq_address);
IF (deq_address.GetTypeName() = 'AQMGR.EVENT_MSG_TYPE') THEN
num_var := deq_address.GetObject(address)
So the above dequeues an ANYDATA message into the payload variable deq_address before the payload is evaluated and the user type "gotten" from the anydata variable (and put into "address"). The procedure returns the user data type object in it's entirety and extraction of the object contents can ensue successfully.
You are correct that ANYDATA is not currently supported. We don't have any current plans to support it, but will discuss this internally. Thanks for the suggestion!
ANYDATA is extremely powerful and enables us to update object types without having to rebuild queues. This provides us a way of disassociating any changes from the queue availability which provides high levels of system/application availability. Using Python methods is extremely elegant and being able to unpack types based upon their inherent type within the Python code would be ideal.
I have used cx_oracle to push and pull user data type structures to an AQ successfully. However, I have not had complete success with the ANYDATA data type. I can push a user data type message onto an ANYDATA queue with success. When I query the record natively (i.e. from the data column in the queue table) I can see that the content (within the ANYDATA column ) is of the enqueued user data type and using the Oracle function CAST I can read out the data. However, dequeueing ANYDATA messages seems to be a non-starter as it seems impossible to use newobject() against the datatype:
msgTypeAnydata = cn.gettype("SYS.ANYDATA") <<== this works
msg = msgTypeAnydata.newobject() <<== this fails
I have only been able to dequeue ANYDATA messages via stored procedures which then locate the object. The essential elements of the stored procedure is:
address AQMGR.EVENT_MSG_TYPE;
deq_address ANYDATA;
So the above dequeues an ANYDATA message into the payload variable deq_address before the payload is evaluated and the user type "gotten" from the anydata variable (and put into "address"). The procedure returns the user data type object in it's entirety and extraction of the object contents can ensue successfully.
The cx_oracle portion is
msgType = cn.gettype("EVENT_MSG_TYPE")
msg = msgType.newobject()
cur = cn.cursor()
cur.prepare("BEGIN deq(:a); END; ")
cur.execute(None, [msg])
cn.commit()
print(msg.NAME)
This all works.
The procedure "deq" returns into :a the object of the relevant type. NAME is one of the object type variables:
CREATE OR REPLACE TYPE event_msg_type AS OBJECT (
name CHAR(20),
current_status NUMBER(5),
next_status NUMBER(5)
);
/
It's cx 8.1 (Windows 10) on Oracle 19c (Linux)
Is my understanding correct that ANYDATA is not fully supported as an object type? Will it ever be?
The text was updated successfully, but these errors were encountered: