1

I have an Oracle table that has a column of type VARRAY Oracle collection type.

create or replace
TYPE            FUSION_USER.FUSION_KEY_LIST AS VARRAY(20) OF VARCHAR2(80);

CREATE TABLE FUSION_QUEUE_AUDIT
  (
    "XML_ID"            NUMBER NOT NULL ENABLE,
    "XML_MESSAGE_TYPE"  NUMBER(4,0) NOT NULL ENABLE,
    "XML_MESSAGE"       SYS.XMLTYPE,
    "KEY_ID"            NUMBER,
    "KEY_LIST"          FUSION_USER.FUSION_KEY_LIST,
    "STATUS_IND"        VARCHAR2(1 BYTE) NOT NULL ENABLE
);

The data in KEY_LIST column is stored as:

FUSION_USER.FUSION_KEY_LIST(1600458446,N)
FUSION_USER.FUSION_KEY_LIST(1600488742,N)
FUSION_USER.FUSION_KEY_LIST(1600481496,N)
FUSION_USER.FUSION_KEY_LIST(1600473344,N)

How do I search for a value in this column?. The below queries throws an error:

select * from fusion_queue_audit where xml_message_type = 560 and create_date > (sysdate - 8/24) and INSTR(key_list,'1600103836') > 0;

OR

select * from fusion_queue_audit where xml_message_type = 560 and create_date > (sysdate - 8/24) and key_list like '%1600103836%';

Thanks in advance R

Redwood Thomas
  • 77
  • 9

1 Answers1

1

You can apply table to the collection to access and filter its content like

select * from fusion_queue_audit
where xml_message_type = 560
  and create_date > (sysdate - 8/24)
  and exists (
    select 1 from table(key_list) t1
    where t1.column_value like '%1600103836%'
  );

Check the test example on fiddle

Dornaut
  • 553
  • 3
  • 7