Oracle select query from table with custom datatypes [duplicate]

Question

I have inherited a project with an old Oracle database with tables that contain custom datatype columns:

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

I can run a SELECT on the table and select just the JOBID column and get results, however as soon as I try to select any of the custom DataType columns I receive the error ORA-00904: : invalid identifier

I have searched around and everything suggests it is due to an invalid column name. I have tried the following queries, all return the same error message:

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

Is there anything else I can do to help diagnose the cause of the error?

Thanks.

Edit: An example of one of the custom types:

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

Edit2: I looked at the PAYLOADLISTTYPE and it is slightly different:

CREATE type payloadlisttype as table of transactiontype

Then transactiontype:

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

headertype and datatype then also nest custom data types, which nest custom types, there are custom datatypes down 7 layers if that makes a difference

Edit3: Querying ALL_OBJECTS for the top level data types returns:

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

I only ran this against the top level data types, does it need to be done at all levels?

Answer 1

Use a table alias:

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

or, to get the attributes within the types:

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