sqloracle-databaseora-00936oracle-type

Oracle: query against value of user defined type

I have a user-defined type:

create or replace type my_message_type 
  as object (relatedid varchar2(50), payload clob);

I inserted a message programatically. SQL Developer renders the inserted object as:

SYNESSO.MY_MESSAGE_TYPE('abcdefgh','oracle.sql.CLOB@1dae16a')

How can query against this data using SQL? For example the following seems intuitive:

select count(1) from table_of_my_messages where user_data.relatedid = 'abcdefgh';

But this results in ORA-00904: "USER_DATA"."RELATEDID": invalid identifier.

I then discovered the correct syntax is to construct the message type and use equality checking. But how to construct an instance of the type with some of the fields matched to any?:

select * from table_of_my_messages where user_data = my_message_type('abcdefgh', *);
-- ORA-00936: missing expression

select * from table_of_my_messages where user_data = my_message_type('abcdefgh');
-- ORA-02315: incorrect number of arguments for default constructor

select * from table_of_my_messages where user_data = my_message_type('abcdefgh', ?);
-- Missing IN or OUT parameter at index:: 1
Solution

  • The intuitive version is almost correct. I needed only to alias the table for it to work...

    select count(1) from table_of_my_messages m 
    where m.user_data.relatedid = 'abcdefgh';