SQLDeveloper and Userdefined datatypes in tables

You might have tables that contain columns with a userdefined datatypes. For instance from 11g onwards SOASuite contain Integration B2B, with that datamodel

B2B works with advanced queueing with the queue-table ip_qtab based on the IP_MESSAGE_TYPE Oracle Type wich is defined like:

create or replace type IP_MESSAGE_TYPE as OBJECT (
        MSG_ID                                          VARCHAR2(128),
        INREPLYTO_MSG_ID                                VARCHAR2(128),
 FROM_PARTY                                      VARCHAR2(512),
 TO_PARTY                                        VARCHAR2(512),
        ACTION_NAME                                     VARCHAR2(512),
        DOCTYPE_NAME                                    VARCHAR2(512),
        DOCTYPE_REVISION                                VARCHAR2(512),
        MSG_TYPE                                        INT,
        PAYLOAD                                         CLOB,
        ATTACHMENT                                      BLOB
);

In the queuetable you then have a payload column based on this type. When you do a select on such a table the payload column has actually several attributes. Tools like Pl/Sql Developer from Allroundautomations or TOAD apparently encounter that the column is based on the Oracle Type, so they actually show the seperate attributes in the grid.

SQLDeveloper (currently 4.3) apparently does not so. But it is quite easy to add this information in your select. For a select on the queuetable (actually with AQ you shouldn't query the queuetable, but the accompanying AQ$<queuetable> view) it will look like:

SELECT QTB.QUEUE,
  QTB.MSG_ID,
  QTB.CORR_ID,
  QTB.MSG_PRIORITY,
  QTB.MSG_STATE,
  QTB.RETRY_COUNT,
  QTB.USER_DATA.MSG_ID MSG,
  QTB.USER_DATA.INREPLYTO_MSG_ID INREPLYTO_MSG_ID,
  QTB.USER_DATA.FROM_PARTY FROM_PARTY,
  QTB.USER_DATA.TO_PARTY TO_PARTY,
  QTB.USER_DATA.ACTION_NAME ACTION_NAME,
  QTB.USER_DATA.DOCTYPE_NAME DOCTYPE_NAME,
  QTB.USER_DATA.DOCTYPE_REVISION DOCTYPE_REVISION,
  QTB.USER_DATA.MSG_TYPE MSG_TYPE,
  QTB.USER_DATA.PAYLOAD PAYLOAD,
  QTB.CONSUMER_NAME,
  QTB.PROTOCOL
FROM AQ$IP_QTAB QTB;

You see that the trick is to just add the attribute as a seperate identifier to the user_data-column, using the dot-notation.

If you're certain that the selected rows contain a valid XML document in the Payload attribute you could provide that attribute to the xmltype() constructor:

SELECT QTB.QUEUE,
  QTB.MSG_ID,
  QTB.CORR_ID,
  QTB.MSG_PRIORITY,
  QTB.MSG_STATE,
  QTB.RETRY_COUNT,
  QTB.USER_DATA.MSG_ID MSG,
  QTB.USER_DATA.INREPLYTO_MSG_ID INREPLYTO_MSG_ID,
  QTB.USER_DATA.FROM_PARTY FROM_PARTY,
  QTB.USER_DATA.TO_PARTY TO_PARTY,
  QTB.USER_DATA.ACTION_NAME ACTION_NAME,
  QTB.USER_DATA.DOCTYPE_NAME DOCTYPE_NAME,
  QTB.USER_DATA.DOCTYPE_REVISION DOCTYPE_REVISION,
  QTB.USER_DATA.MSG_TYPE MSG_TYPE,
  xmltype(QTB.USER_DATA.PAYLOAD) PAYLOAD,
  QTB.CONSUMER_NAME,
  QTB.PROTOCOL
FROM AQ$IP_QTAB QTB;

And of course this works for other tables as well. This is just a quick example for a table based on an object type. Unfortunately I don't have some example data in the queue at the moment.