Dear Experts,
I am facing a problem in using the CLOB in where caluse and my CLOB contains comma separated numbers.
I have used XMLTABLE is working fine in test but when i use it in main query and use XMLTABLE as subquery then its giving error.
SELECT TO_NUMBER(COLUMN_VALUE) FROM XMLTABLE('5206, 1913, 1914, 1915, 2235, 1912, 2236');
SELECT DISTINCT cas.cust_acct_site_id address_id,
hps.party_site_id,
hps.party_id,
cas.cust_account_id,
cas.org_id org_id
FROM hz_cust_acct_sites_all cas, hz_party_sites hps,hz_customer_profiles hcp
WHERE cas.cust_account_id IN (SELECT rel.related_cust_account_id
FROM hz_cust_acct_relate_all rel
WHERE rel.cust_account_id = pl_cust_account_id)
AND cas.party_site_id = hps.party_site_id
AND hps.party_id = NVL(p_party_id, hps.party_id)
AND cas.cust_account_id = hcp.cust_account_id
AND hcp.site_use_id IS NULL
AND (hcp.collector_id) = NVL(p_acc_handler_id,hcp.collector_id)
AND cas.org_id = NVL(p_company,cas.org_id)
AND cas.status = 'A'
AND cas.cust_account_id NOT IN (SELECT TO_NUMBER(COLUMN_VALUE)
FROM XMLTABLE (lc_cust_accounts));
where lc_cust_accounts is CLOB variable. I am getting following error message.
Found 'lc_cust_accounts', Expecting: XMLNAMESPACES or string
Please suggest any solution or may i doing something wrong.
Thanks in anticipation.