java - Using Like with In to select query -

i have query of following search column request xml matching string.

select * customer access '%usr'  , status = 'success'  , request '%' ||  (select regexp_substr(request, '<paes:idname>purchaseid</paes:idname><paes:idvalue>\d+</paes:idvalue>')   customer request '%add%sub%prime%') || '%'; 

however getting error message of ora-01427: single-row subquery returns more 1 row

what want search requests has same id add not add request. example....canceling order request id number used canceling order.

how change above use request take like in '%' || ... || '%'

or alternative above query?

select regexp_substr(request, '<paes:idname>purchaseid</paes:idname><paes:idvalue>\d+</paes:idvalue>')       customer     request '%add%sub%prime%' 

would return multiple rows of ids

what want search request column matching ids. if replace like in ora-00932: inconsistent datatypes: expected - got clob error. how work around this?

edit: example request column

request (hugeclob) (hugeclob) (hugeclob) ... 

if double click hugeclob full request below....

<?xml version='1.0' encoding='utf-8'?> <soap-env:envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">     <soap-env:header>         <wsse:security             xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"             soap-env:mustunderstand="1">             <wsse:usernametoken                 xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"                 xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"                 wsu:id="uuid_86c8ee4f-b6e6-4e07-9fa6-e2f9f2695c01">                 <wsse:username>admin</wsse:username>                 <wsse:password                     type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#passwordtext">mx9483</wsse:password>             </wsse:usernametoken>         </wsse:security>     </soap-env:header>     <soap-env:body>      ....     <paes:idname>purchaseid</paes:idname>     <paes:idvalue>2786872</paes:idvalue>      ....     <paes:idname>mailaddressid</paes:idname>     <paes:idvalue>2786622</paes:idvalue>      ....     </soap-env:body> 

here example of cancel request

<?xml version='1.0' encoding='utf-8'?> <soap-env:envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">     <soap-env:header>         <wsse:security             xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"             soap-env:mustunderstand="1">             <wsse:usernametoken                 xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"                 xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"                 wsu:id="uuid_86c8ee4f-b6e6-4e07-9fa6-e2f9f2695c01">                 <wsse:username>admin</wsse:username>                 <wsse:password                     type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#passwordtext">mx9483</wsse:password>             </wsse:usernametoken>         </wsse:security>     </soap-env:header>     <soap-env:body>     <paes:idname>cancelid</paes:idname>     <paes:idvalue>2786872</paes:idvalue>     </soap-env:body> </soap-env:envelope> 

because sopa message normal xml document. it's can processed in more stuctural way.

prepare test db;

create table rr (id number, requext clob) ; 

short version of messages.

insert rr values(1, '<soap-env:envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">     <soap-env:body xmlns:paes="emvin.atl/schema/v1">       <paes:idname>purchaseid</paes:idname>       <paes:idvalue>2786872</paes:idvalue>       <paes:idname>mailaddressid</paes:idname>       <paes:idvalue>2786622</paes:idvalue>     </soap-env:body>     </soap-env:envelope>'); insert rr values(2, '<soap-env:envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/">     <soap-env:body  xmlns:paes="emvin.atl/schema/v1">     <paes:idname>cancelid</paes:idname>     <paes:idvalue>2786872</paes:idvalue>     </soap-env:body> </soap-env:envelope>'); 

with xmltable function can extract paes:idvalue.

select * rr,xmltable( xmlnamespaces('emvin.atl/schema/v1' "paes"), '$doc//paes:idvalue' passing xmltype(rr.requext) "doc"  columns  idvalue  number path './text()' ) ; 

now can create view previous query or use in statement.

with test ( select * rr,xmltable( xmlnamespaces('emvin.atl/schema/v1' "paes"), '$doc//paes:idvalue' passing xmltype(rr.requext) "doc"  columns  idvalue  number path './text()' ))  select * test idvalue in (select idvalue test id =1)   , id != 1; 

xquery

xmltable