My current queue payload type has a component having timestamp datatype.
Eg : my_queue_payload(attr1 varchar2(100),
I have successfully managed to query the component attr1 using the below query on the queue table(my_queue_tab)
select a.user_data.attr1 from my_queue_tab a where a.msgid = 'AAABBBCCC1234';
However, I am unable to query the component attr_timestamp using the above query due to Oravle error 'invalid identifier'.
I have the following queries with respect to the above issue :
1. I am unable to query attr1 if i dont provide an alias for the queue table, please let me know the reason why ?
2. I am unable to use any Oracle function on attr1 , please let me know the reason why ?
3. How I can query attr_timestamp ?
Could you please help.
Thanks in advance.
you cannot use a simple dot notation in order to query USER_DATA.
There is already a thread "AQ: How to query user_data?" Re: AQ: How to query user_data?
You have to use the built-in function TREAT.