I hope anyone can help me. I am designing a model with some RTMV (Real Time Materialized Views) in Oracle 12.2. I create all the MV logs and define the refresh group as well as the jobs to do it. So far all good..
However the query that I need to run over them does not work because apparently using either DUAL , REGEXP_SUBSTR or CONNECT BY LEVEL is not compatible with the hint FRESH_MV. I need to use the hint because the query is done over the RTMVs, and that is the only way to get the real time data ( as far as I know ).
If the hint cannot be used, I would like to know how to rewrite the condition shown below in order to avoid using DUAL, CONNECT BY LEVEL and REGEXP_SUBSTR.
AND (:var15 IS NULL OR sel.vinLast6 IN (select regexp_substr(NVL(:var16 , ''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var17 ,''), '[^,]+', 1, level) is not NULL))
Let me show you
SQL> set sqlblanklines on echo on timing on lines 220 pages 600 autotrace traceonly
var var1 varchar2(200);
var var2 varchar2(200);
var var3 varchar2(200);
var var4 varchar2(200);
var var5 varchar2(200);
var var6 varchar2(200);
var var7 varchar2(200);
var var8 varchar2(200);
var var9 varchar2(200);
var var10 varchar2(200);
var var11 varchar2(200);
var var12 varchar2(200);
var var13 varchar2(200);
var var14 varchar2(200);
var var15 varchar2(200);
var var16 varchar2(200);
var var17 varchar2(200);
var var18 varchar2(200);
var var19 varchar2(200);
var var20 varchar2(200);
var var21 varchar2(200);
var var22 varchar2(200);
var var23 varchar2(200);
var var24 varchar2(200);
var var25 varchar2(200);
var var26 varchar2(200);
var var27 varchar2(200);
var var28 varchar2(200);
var var29 varchar2(200);
var var30 varchar2(200);
var var31 varchar2(200);
var var32 varchar2(200);
var var33 varchar2(200);
var var34 varchar2(200);
var var35 varchar2(200);
var var36 varchar2(200);
var var37 varchar2(200);
var var38 varchar2(200);
var var39 varchar2(200);
var var40 varchar2(200);
var var41 varchar2(200);
var var42 varchar2(200);
var var43 varchar2(200);
var var44 varchar2(200);
var var45 varchar2(200);
var var46 varchar2(200);
var var47 varchar2(200);
var var48 varchar2(200);
var var49 varchar2(200);
var var50 varchar2(200);
var var51 varchar2(200);
var var52 varchar2(200);
var var53 varchar2(200);
var var54 varchar2(200);
var var55 varchar2(200);
var var56 varchar2(200);
var var57 varchar2(200);
var var58 varchar2(200);
var var59 varchar2(200);
var var60 varchar2(200);
var var61 varchar2(200);
var var62 varchar2(200);
var var63 varchar2(200);
var var64 varchar2(200);
var var65 varchar2(200);
var var66 varchar2(200);
var var67 varchar2(200);
var var68 varchar2(200);
var var69 varchar2(200);
var var70 varchar2(200);
var var71 varchar2(200);
var var72 varchar2(200);
var var73 varchar2(200);
var var74 varchar2(200);
var var75 varchar2(200);
var var76 varchar2(200);
var var77 varchar2(200);
var var78 varchar2(200);
var var79 varchar2(200);
var var80 varchar2(200);
var var81 varchar2(200);
exec :var1 := null ;
exec :var2 := null ;
exec :var3 := null ;
exec :var4 := null ;
exec :var5 := null ;
exec :var6 := null ;
exec :var7 := null ;
exec :var8 := null ;
exec :var9 := null ;
exec :var10 := null ;
exec :var11 := null ;
exec :var12 := null ;
exec :var13 := null ;
exec :var14 := null ;
exec :var15 := null ;
exec :var16 := null ;
exec :var17 := null ;
exec :var18 := null ;
exec :var19 := null ;
exec :var20 := null ;
exec :var21 := null ;
exec :var22 := null ;
exec :var23 := null ;
exec :var24 := null ;
exec :var25 := null ;
exec :var26 := null ;
exec :var27 := null ;
exec :var28 := null ;
exec :var29 := null ;
exec :var30 := null ;
exec :var31 := null ;
exec :var32 := null ;
exec :var33 := null ;
exec :var34 := null ;
exec :var35 := null ;
exec :var36 := null ;
exec :var37 := null ;
exec :var38 := null ;
exec :var39 := null ;
exec :var40 := null ;
exec :var41 := null ;
exec :var42 := null ;
exec :var43 := null ;
exec :var44 := null ;
exec :var45 := null ;
exec :var46 := null ;
exec :var47 := null ;
exec :var48 := null ;
exec :var49 := null ;
exec :var50 := null ;
exec :var51 := null ;
exec :var52 := null ;
exec :var53 := null ;
exec :var54 := null ;
exec :var55 := null ;
exec :var56 := null ;
exec :var57 := null ;
exec :var58 := null ;
exec :var59 := null ;
exec :var60 := null ;
exec :var61 := null ;
exec :var62 := null ;
exec :var63 := null ;
exec :var64 := null ;
exec :var65 := null ;
exec :var66 := null ;
exec :var67 := null ;
exec :var68 := null ;
exec :var69 := null ;
exec :var70 := null ;
exec :var71 := null ;
exec :var72 := null ;
exec :var73 := null ;
exec :var74 := null ;
exec :var75 := null ;
exec :var76 := null ;
exec :var77 := null ;
exec :var78 := 20 ;
exec :var79 := 20 ;
exec :var80 := null ;
exec :var81 := null ;
-- query
SELECT
sel.maturityDate,
sel.schedule_id ,
sel.scheduleId,
sel.capitalOutstanding,
sel.assetId,
sel.assetModelName ,
sel.loanId,
proposalStatus.DETAIL AS proposalStatusCode,
sel.scheduleStatus,
assetValuation.valuation as schwackeValue ,
typeId.DETAIL as schwackeCodes,
sel.terminationDate,
docRepositary.DETAIL as docRepositaryId,
TO_DATE(CAST(CASE
WHEN (cancellationDate.DATEFIELD= 0)
THEN null
ELSE cancellationDate.DATEFIELD END AS NVARCHAR2(8)), 'yyyymmdd') as cancellationDate,
(CASE
WHEN supplier.thirdPartyNumber = sel.dealerGPNr THEN to_char(supplier.thirdPartyNumber)
ELSE '0'
END) as "supplierGPNr",
supplier.thirdPartyNumber as SupplierThirdNumber ,
(CASE
WHEN proposalStatus.DETAIL = 'RESERVED' THEN '0'
WHEN proposalStatus.DETAIL = 'APPROVED' THEN '1'
WHEN proposalStatus.DETAIL = 'FINANCED' THEN '2'
WHEN proposalStatus.DETAIL = 'CANCELLED' THEN '3'
WHEN proposalStatus.DETAIL = 'PRECANCELLED' THEN '3'
WHEN proposalStatus.DETAIL = 'CREDITNOTECANCELLED' THEN '3'
WHEN proposalStatus.DETAIL = 'WAITING' THEN '4'
ELSE '5'
END) as loanState,
productId1.DETAIL as productId1,
productId2.DETAIL as productId2,
productId3.DETAIL as productId3,
productId4.DETAIL as productId4,
productId.DETAIL as wmProductId,
sel.vin,
sel.modelName,
sel.loanAmount,
(CASE
WHEN sel.capitalOutstanding IS NULL THEN 0
WHEN sel.scheduleStatus = 'Proposal' THEN
(CASE
WHEN proposalStatus.DETAIL = 'WAITING' THEN 0
ELSE sel.capitalOutstanding * (-1)
END)
ELSE sel.capitalOutstanding * (-1)
END) as balance,
sel.dealerGPNr,
sel.dealerNameShort,
TO_DATE(CAST(CASE
WHEN (creationDate.DATEFIELD= 0)
THEN null
ELSE creationDate.DATEFIELD END AS NVARCHAR2(8)), 'yyyymmdd') as creationDate,
sel.activationDate,
sel.KBSTATE,
assetValuationInp.narrative as invoiceId,
(CASE
WHEN assetValuationInp.valuation IS NULL THEN 0
ELSE assetValuationInp.valuation
END) as invoiceAmount,
assetValuationInp.valueDate as invoiceDate,
assetUsageHistory.readingValue as kilometers,
installSchema.DETAIL as installmentSchemaId,
installSchemaVer.DETAIL as installmentSchemaVersion,
interestSchema.DETAIL as interestSchemaId,
interestSchemaVer.DETAIL as interestSchemaVersion,
sel.vinLast6,
CASE
WHEN UPPER(CONCAT(sel.submitterId, '')) IN ('<NONE>', '', 'NULL')
THEN 0
ELSE TO_NUMBER(sel.submitterId)
END as submitterId,
stockId.DETAIL as stockId,
deliveryLink.DETAIL as deliveryLink,
TO_DATE(CAST(CASE
WHEN (deliveryDate.DATEFIELD= 0)
THEN null
ELSE deliveryDate.DATEFIELD END AS NVARCHAR2(8)), 'yyyymmdd') as deliveryDate,
agreementAlert.dunningBlock as dunningBlock,
odsInvoicingCompany.code as "brandId",
odsInvoicingCompany.name as "brand",
agrCompany.code as "mandantId",
modelHSN.DETAIL as hsn,
modelTSN.DETAIL as tsn,
briefNumber.DETAIL as documentNr,
TO_DATE(CAST(CASE WHEN (loanDocumentDate.DATEFIELD = 0) THEN null
ELSE loanDocumentDate.DATEFIELD END AS NVARCHAR2(8)), 'yyyymmdd') as documentStatusDate,
documentStateLoan.DETAIL as documentStateLoan,
loanDocumentLocation.DETAIL as documentLocation,
fn.finalDate
FROM alfaods.mv_ext_dealer_01 sel
inner join ( select max(finalDate) as finalDate,scheduleId from alfaods.mv_max_fn_date fn group by scheduleId ) fn on sel.scheduleId = fn.scheduleId
LEFT JOIN ALFAODS.OdsThirdParty supplier on supplier.id = sel.supplierId
LEFT JOIN ALFAODS.OdsCompany odsInvoicingCompany on odsInvoicingCompany.id = sel.invCompanyId
LEFT JOIN ALFAODS.OdsCompany agrCompany on agrCompany.id = sel.agrCompanyId
LEFT JOIN ALFAODS.OdsAssetValuation assetValuationInp on ( assetValuationInp.assetId = sel.assetId AND assetValuationInp.valueTypeCode = 'INP' AND assetValuationInp.isCurrentForTypeAndSource = 1 )
LEFT JOIN ALFAODS.OdsAssetUsageHistory assetUsageHistory on sel.assetId = assetUsageHistory.assetId
LEFT JOIN ALFAODS.OdsAgreementAlert agreementAlert on agreementAlert.agreementId = sel.agreementid
LEFT JOIN ALFAODS.OdsAssetValuation assetValuation on ( assetValuation.assetId = sel.assetId AND assetValuation.valueTypeCode = 'TTV' )
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos installSchema ON installSchema.alfaScheduleIdentifier = sel.scheduleId AND installSchema.informationType = 'WINSS'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos interestSchema ON interestSchema.alfaScheduleIdentifier = sel.scheduleId AND interestSchema.informationType = 'WINTS'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos application ON application.alfaScheduleIdentifier = sel.scheduleId AND application.informationType = 'WAPID'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos productId1 ON productId1.alfaScheduleIdentifier = sel.scheduleId AND productId1.informationType = 'WPID1'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos productId2 ON productId2.alfaScheduleIdentifier = sel.scheduleId AND productId2.informationType = 'WPID2'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos productId3 ON productId3.alfaScheduleIdentifier = sel.scheduleId AND productId3.informationType = 'WPID3'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos productId4 ON productId4.alfaScheduleIdentifier = sel.scheduleId AND productId4.informationType = 'WPID4'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos stockID ON stockID.alfaScheduleIdentifier = sel.scheduleId AND stockID.informationType = 'WSTID'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos proposalStatus ON proposalStatus.alfaScheduleIdentifier = sel.scheduleId AND proposalStatus.informationType = 'WPRST'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos creationDate ON creationDate.alfaScheduleIdentifier = sel.scheduleId AND creationDate.informationType = 'WCRDT'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos installSchemaVer ON installSchemaVer.alfaScheduleIdentifier = sel.scheduleId AND installSchemaVer.informationType = 'WINSV'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos interestSchemaVer ON interestSchemaVer.alfaScheduleIdentifier = sel.scheduleId AND interestSchemaVer.informationType = 'WINTV'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos cancellationDate ON cancellationDate.alfaScheduleIdentifier = sel.scheduleId AND cancellationDate.informationType = 'WCD'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos deliveryLink ON deliveryLink.alfaScheduleIdentifier = sel.scheduleId AND deliveryLink.informationType = 'WDLNK'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos productId ON productId.alfaScheduleIdentifier = sel.scheduleId AND productId.informationType = 'WPRID'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos deliveryDate ON deliveryDate.alfaScheduleIdentifier = sel.scheduleId AND deliveryDate.informationType = 'WDELD'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos netting ON netting.alfaScheduleIdentifier = sel.scheduleId AND netting.informationType = 'WNETT'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_info_asset modelHSN ON modelHSN.assetIdentifier = sel.assetId AND modelHSN.informationType = u'MANUN'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_info_asset modelTSN ON modelTSN.assetIdentifier = sel.assetId AND modelTSN.informationType = u'MODC'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_info_asset briefNumber ON briefNumber.assetIdentifier = sel.assetId AND briefNumber.informationType = u'BNR'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos documentStateLoan ON documentStateLoan.alfaScheduleIdentifier = sel.scheduleId AND documentStateLoan.informationType = 'WDCLS'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos loanDocumentLocation ON loanDocumentLocation.alfaScheduleIdentifier = sel.scheduleId AND loanDocumentLocation.informationType = 'WLDLC'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos loanDocumentDate ON loanDocumentDate.alfaScheduleIdentifier = sel.scheduleId AND loanDocumentDate.informationType = 'WCRDT'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_miscinfos docRepositary ON docRepositary.alfaScheduleIdentifier = sel.scheduleId AND docRepositary.informationType = 'WDRID'
LEFT JOIN ALFAODS.mv_ods_tmpcurrent_info_asset typeId ON typeId.assetIdentifier = sel.assetId AND typeId.informationType = u'WTYID'
WHERE
(:var2 IS NULL OR productId1.DETAIL = NVL(:var3 ,productId1.DETAIL ))
AND (:var4 IS NULL OR productId2.DETAIL = NVL(:var5 ,productId2.DETAIL ))
AND (:var6 IS NULL OR productId3.DETAIL = NVL(:var7 ,productId3.DETAIL ))
AND (:var8 IS NULL OR productId4.DETAIL = NVL(:var9 ,productId4.DETAIL ))
AND modelHSN.DETAIL = NVL(:var10 , modelHSN.DETAIL)
AND modelTSN.DETAIL = NVL(:var11 , modelTSN.DETAIL )
AND briefNumber.DETAIL = NVL(:var12 ,briefNumber.DETAIL)
AND documentStateLoan.DETAIL= NVL(:var13 , documentStateLoan.DETAIL)
AND loanDocumentLocation.DETAIL = NVL(:var14 , loanDocumentLocation.DETAIL)
AND (:var15 IS NULL OR sel.vinLast6 IN (select regexp_substr(NVL(:var16 , ''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var17 ,''), '[^,]+', 1, level) is not NULL))
AND (:var18 IS NULL OR sel.vin IN (select regexp_substr(NVL(:var19 ,''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var20 ,''), '[^,]+', 1, level) is not NULL))
AND (:var21 IS NULL OR sel.dealerGPNr IN (select regexp_substr(NVL(:var22 ,''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var23 ,''), '[^,]+', 1, level) is not NULL))
AND (:var24 IS NULL OR supplier.thirdPartyNumber IN (select regexp_substr(NVL(:var25 ,''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var26 ,''), '[^,]+', 1, level) is not NULL))
AND (:var27 IS NULL OR sel.dealerGPNr IN ( SELECT thirdpartynumber FROM alfaods.mv_th_and_ref where reference = :var1 ))
AND (:var28 IS NULL OR UPPER(proposalStatus.DETAIL) IN (select regexp_substr(NVL(:var29 ,''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var30 ,''), '[^,]+', 1, level) is not NULL))
AND (:var31 IS NULL OR sel.loanId IN (select regexp_substr(NVL(:var32 ,''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var33 ,''), '[^,]+', 1, level) is not NULL))
AND (:var34 IS NULL OR sel.assetModelName like '%' || :var35 || '%')
AND (:var36 IS NULL OR assetValuationInp.narrative = :var37 OR stockId.DETAIL = :var38 )
AND (:var39 IS NULL OR productId1.DETAIL = NVL(:var40 ,productId1.DETAIL))
AND (:var41 IS NULL OR productId2.DETAIL = NVL(:var42 ,productId2.DETAIL))
AND (:var43 IS NULL OR productId3.DETAIL = NVL(:var44 ,productId3.DETAIL))
AND (:var45 IS NULL OR productId4.DETAIL = NVL(:var46 ,productId4.DETAIL))
AND ((:var47 IS NULL) OR assetValuationInp.valueDate >= TO_DATE(:var48 , 'yyyy-MM-dd'))
AND ((:var49 IS NULL) OR assetValuationInp.valueDate <= TO_DATE(:var50 , 'yyyy-MM-dd'))
AND ((:var51 IS NULL) OR sel.activationDate >= TO_DATE(:var52 , 'yyyy-MM-dd'))
AND ((:var53 IS NULL) OR sel.activationDate <= TO_DATE(:var54 , 'yyyy-MM-dd'))
AND (:var55 IS NULL OR modelHSN.DETAIL = NVL(:var56 ,modelHSN.DETAIL))
AND (:var57 IS NULL OR modelTSN.DETAIL = NVL(:var58 ,modelTSN.DETAIL))
AND (:var59 IS NULL OR briefNumber.DETAIL = NVL(:var60 ,briefNumber.DETAIL))
AND (:var61 IS NULL OR (TO_DATE(:var62 , 'yyyy-MM-dd') <= sel.Maturitydate))
AND (:var63 IS NULL OR
(sel.scheduleStatus IN ('Live (Primary)', 'Proposal') AND TO_DATE(:var64 , 'yyyy-MM-dd') >= sel.maturityDate OR
sel.scheduleStatus IN ('Terminated', 'Matured' ) AND ADD_MONTHS(Trunc(TO_DATE(:var65 ,'yyyy-MM-dd'),'MONTH'),2) >= sel.maturityDate)
)
AND (:var66 IS NULL OR documentStateLoan.DETAIL = NVL(:var67 ,documentStateLoan.DETAIL))
AND (:var68 IS NULL OR loanDocumentLocation.DETAIL = NVL(:var69 ,loanDocumentLocation.DETAIL))
AND ( sel.scheduleStatus IN ('Live (Primary)', 'Proposal')
OR
(
sel.scheduleStatus in ('Terminated', 'Matured')
and
TO_DATE(CAST(CASE WHEN (cancellationDate.DATEFIELD= 0) THEN null ELSE cancellationDate.DATEFIELD END AS NVARCHAR2(8)), 'yyyymmdd')
BETWEEN TO_DATE(NVL(:var80 , '1900-01-01'), 'yyyy-MM-dd') AND TO_DATE(NVL(:var81 ,'9999-12-31'), 'yyyy-MM-dd') OR cancellationDate.DATEFIELD is null
)
)
ORDER BY
CASE WHEN UPPER(:var70 ) = 'LOANID' AND UPPER(:var71 ) = 'ASC' THEN sel.loanId END ASC ,
CASE WHEN UPPER(:var72 ) = 'LOANID' AND UPPER(:var73 ) <> 'ASC' THEN sel.loanId END DESC,
CASE WHEN UPPER(:var74 ) <> 'LOANID' AND UPPER(:var75 ) = 'ASC' THEN sel.dealerGPNr END ASC,
CASE WHEN UPPER(:var76 ) <> 'LOANID' AND UPPER(:var77 ) <> 'ASC' THEN sel.dealerGPNr END DESC
OFFSET :var78 ROWS FETCH NEXT :var79 ROWS ONLY
;
AND (:var15 IS NULL OR sel.vinLast6 IN (select regexp_substr(NVL(:var16 , ''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var17 ,''), '[^,]+', 1, level) is not NULL))
*
ERROR at line 138:
ORA-32359: cannot specify the FRESH_MV hint for this query
I have been suffered a lot to make work the RTMV ( I had so many problems with enabling ON QUERY COMPUTATION ). Is there a way to use the hint FRESH_MV with any of those ?
Is there a way to rewrite this condition without using any of the apparent incompatible functions ?
AND (:var15 IS NULL OR sel.vinLast6 IN (select regexp_substr(NVL(:var16 , ''),'[^,]+', 1, level) from dual connect by regexp_substr(NVL(:var17 ,''), '[^,]+', 1, level) is not NULL))
That CONNECT BY mechanism is a means to convert a comma delimited string into rows, eg
SQL> variable var16 varchar2(100)
SQL> exec :var16 := 'SMITH,BLAKE'
PL/SQL procedure successfully completed.
SQL> select regexp_substr(NVL(:var16 , ''),'[^,]+', 1, level) from dual
2 connect by regexp_substr(NVL(:var16 ,''), '[^,]+', 1, level) is not NULL;
REGEXP_SUBSTR(NVL(:VAR16,''),'[^,]+',1,LEVEL)
-------------------------------------------------------------------------------
SMITH
BLAKE
and thus when used in a surrounding query
SQL> create table t ( x varchar2(10));
Table created.
SQL> set feedback off
SQL> insert into t values ('SMITH');
SQL> insert into t values ('ALLEN');
SQL> insert into t values ('WARD');
SQL> insert into t values ('JONES');
SQL> insert into t values ('MARTIN');
SQL> insert into t values ('BLAKE');
SQL> insert into t values ('CLARK');
SQL> insert into t values ('SCOTT');
SQL> insert into t values ('KING');
SQL> insert into t values ('TURNER');
SQL> insert into t values ('ADAMS');
SQL> insert into t values ('JAMES');
SQL> insert into t values ('FORD');
SQL> insert into t values ('MILLER');
SQL> set feedback on
SQL>
SQL> variable var16 varchar2(100)
SQL> exec :var16 := 'SMITH,BLAKE'
PL/SQL procedure successfully completed.
SQL> select *
2 from t
3 where x in ( select regexp_substr(NVL(:var16 , ''),'[^,]+', 1, level) from dual
4 connect by regexp_substr(NVL(:var16 ,''), '[^,]+', 1, level) is not NULL );
X
----------
SMITH
BLAKE
2 rows selected.
You can emulate that by just looking for the column to be contained within the input string (and adding leading and trailing commas).
SQL> select *
2 from t
3 where instr(','||:var16||',',','||x||',') > 0;
X
----------
SMITH
BLAKE
or similarly with LIKE