SQL WHERE IN CLAUSE ORACLE SERVICE BUS EXECUTE PL-SQL

Hi,

I would like to post the code for using the Where in clause through PL - SQL statement

let $sqlStat := fn:concat('SELECT EMAIL_ID,EMAIL_ADDRESS,SUBJECT,BODY,TEMPLATE_ID FROM ESB_EMAIL_TB WHERE EMAIL_ID IN (',let $eml_ids := concat(for $eml_id in $body/ema:reSendEmailRequest/ema:email_ID return concat($eml_id,',')) return substring($eml_ids,1,string-length($eml_ids)-1),')')
return
<email_data>{
fn-bea:execute-sql('jdbc/ccsit_ds', xs:QName('emailRecords'), $sqlStat)
}
</email_data>

The select statement with the where in clause is simple. What tricky here is the use of the XQuery to populate the value of the email id which came as unbounded from the request.

Hence the select statement would eventually result like following

SELECT EMAIL_ID,EMAIL_ADDRESS,SUBJECT,BODY,TEMPLATE_ID FROM ESB_EMAIL_TB WHERE EMAIL_ID IN ('<email_id1>','<email_id2>','<email_id3>'....)

The PL-SQL is preferred for select usecases where DBADAPTER configuration is complicated when coding for multiple select ids.

Hope this post helps somewhere.


<script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
<!-- MyAdUnit -->
<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-2141952672790293"
     data-ad-slot="6953290368"
     data-ad-format="auto"></ins>
<script>
(adsbygoogle = window.adsbygoogle || []).push({});
</script>