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
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';
精彩评论