CODEWITHSUNDEEP
oracle-databasecustom-data-type
Jake
Jake

Reputation: 1217

Oracle select query from table with custom datatypes

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?

Upvotes: 3

Views: 5400

Answers (2)

Non Plus Ultra
Non Plus Ultra

Reputation: 906

Easiest way is to convert the PL/SQL type column to XML:

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

If you want to display only specific values, use extractvalue:

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

You might want to inspect te resulting XML to find out what the actual path is you want to provide as a parameter in the extractvalue function.

Upvotes: 1

MT0
MT0

Reputation: 167932

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

Upvotes: 1

Related Questions