Oracle从表中选择自定义数据类型的查询

Question

我继承了一个带有旧的Oracle数据库的项目，该数据库包含自定义数据类型列的表：

CREATE TABLE JOB_DOWNLOAD (
    JOBID      NUMBER(8) NOT NULL,
    MSGHEADER  MSGHEADERTYPE,  --Custom DataType
    PAYLOAD    PAYLOADLISTTYPE --Custom DataType
);

我可以在表上运行SELECT，然后仅选择JOBID列并获得结果，但是，一旦我尝试选择任何自定义数据类型列，我会收到错误ORA-00904: : invalid identifier

我已经搜索了，一切都表明这是由于无效的列名称。我已经尝试了以下查询，所有这些都返回相同的错误消息：

SELECT * FROM JOB_DOWNLOAD
SELECT JOBID, MSGHEADER, PAYLOAD, FROM JOB_DOWNLOAD
SELECT MSGHEADER  FROM JOB_DOWNLOAD
SELECT PAYLOAD FROM JOB_DOWNLOAD
SELECT MSGHEADER AS "MessageHead" FROM JOB_DOWNLOAD

我还有其他事情可以帮助诊断错误的原因吗？

谢谢。

编辑：自定义类型之一的一个示例：

CREATE type msgheadertype as object
( destination       destinationtype
, org           varchar2(20)
)

edit2：我看了PAYLOADLISTTYPE，略有不同：

CREATE type payloadlisttype as table of transactiontype

然后transactiontype：

CREATE TYPE transactiontype as object
( header            headertype
, data          datatype
)

headertype和 datatype，然后还嵌套自定义数据类型，哪些嵌套自定义类型，如果有差异，则有自定义数据类型下降7层

edit3：查询最高数据类型的ALL_OBJECTS返回：

OBJECT_NAME, OBJECT_TYPE, STATUS
----------------------------------
MSGHEADERTYPE,   TYPE,    VALID
MSGHEADERTYPE,   SYNONYM, VALID
PAYLOADLISTTYPE, TYPE,    VALID

我仅针对顶级数据类型进行运行，是否需要在所有级别上完成？

Answer 1

使用表别名：

SELECT JOBID,
       j.MSGHEADER,
       j.PAYLOAD
FROM   JOB_DOWNLOAD j

或在类型中获取属性：

SELECT JOBID,
       j.MSGHEADER.destination,
       j.MSGHEADER.org,
       j.PAYLOAD.column1,
       j.PAYLOAD.column2
FROM   JOB_DOWNLOAD j

Answer 2

最简单的方法是将PL/SQL类型列转换为XML：

SELECT XMLTYPE( MSGHEADER ),
XMLTYPE(PAYLOAD)
FROM JOB_DOWNLOAD;

如果您只想显示特定值，请使用ExtractValue：

SELECT extractvalue( XMLTYPE( MSGHEADER ), 'PAYLOADLISTTYPE/DESTINATION')
FROM JOB_DOWNLOAD;

您可能需要检查结果的XML，以找出您要在ExtractValue函数中作为参数提供的实际路径。