我希望任何人都能帮助我。我正在设计一个模型与一些RTMV(实时物化视图)在Oracle 12.2。我创建了所有MV日志，并定义了刷新组以及要执行的任务。到目前为止一切顺利。
然而，我需要运行它们的查询不起作用，因为显然使用DUAL,REGEXP_SUBSTR或CONNECT BY LEVEL与提示FRESH_MV不兼容。我需要使用提示，因为查询是在rtmv上完成的，这是获得实时数据的唯一方法(据我所知)。
如果提示不能使用，我想知道如何重写下面显示的条件，以避免使用DUAL, CONNECT BY LEVEL和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))
让我给你看
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
我已经遭受了很多工作的RTMV(我有这么多的问题与启用ON QUERY计算)。是否有一种方法可以使用提示FRESH_MV与任何这些?
有没有办法重写这个条件而不使用任何明显不兼容的函数?
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))
CONNECT BY机制是一种将逗号分隔的字符串转换成行的方法，例如
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
，因此当在周围的查询
中使用时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.
您可以通过查找要包含在输入字符串中的列(并添加前导和尾随逗号)来模拟。
SQL> select *
2 from t
3 where instr(','||:var16||',',','||x||',') > 0;
X
----------
SMITH
BLAKE
或类似的LIKE