Hi I am trying to use a string (colon seperated value) In my IN Query.
Find the below code and the bold underlined statement to find the query i am using to change the colon separated values.
select distinct A.PRIMARY_FEEDER_ID, B.UF_TYPE Type, A.FEEDER_ID, A.PLANING_NO, y.FEEDER, y.ELEC_METERNUMBER, p.CS57EGNAME Customer_name, Z.MM57TNUM, q.WPPH1 MOBILE_NO, A.Substation_name
from
DCCAPXD01.DCC_SUBSTATION_EXPLORER_KV@dccapexuat A, DCCAPXD01.DCC_FEEDERS_DTLS@dccapexuat B,
F5705004@erpprod_lnk x, MV_EL_WITH_SUBSTATION y, F5704001@erpprod_lnk z, F570101N@erpprod_lnk p, f0115@erpprod_lnk q
where Z.MM57MNUM = Y.elec_meternumber
and P.CSAN8 = Z.MM57TNUM
and Z.MM57MTYPE = 'EL'
and Z.MM57TNUM= Q.WPAN8
and q.WPPHTP='SMS'
and trim(q.WPPH1) is not null
and length(trim(q.WPPH1)) = 8
and substr(trim(q.WPPH1),1,1) <> '4'
and Z.MM57PMNUM= x.QD57PMNUM
AND A.PRIMARY_FEEDER_ID = B.FEEDER_NAME
--AND A.FEEDER_ID = B.FEEDER_ID
AND B.UF_TYPE = 'Manual'
AND B.UF_STAGE = :P20_UF_MANUAL
and A.PLANING_NO in (SELECT DISTINCT trim(regexp_substr(:P20_TYPE_SUB_M, '[^:]+', 1, LEVEL)) emp_id FROM dual CONNECT BY LEVEL <= regexp_count(:P20_TYPE_SUB_M, ':')+1)
and A.PLANING_NO = Y.SUBSTATION
For small values in P20_TYPE_SUB_M is working perfectly fine, but for large data sets its throwing the below error
ORA-01460: unimplemented or unreasonable conversion requested
01460. 00000 - "unimplemented or unreasonable conversion requested"
*Cause:
*Action:
Help to find an alternative solution.
Find the failed test case
P20_TYPE_SUB_M =
0217:0219:12307:0112:0113:0117:0520:0543:0546:0548:0552:0644:0645:0649:0651:0652:0653:0655:24423:0695:0696:0697:0329:0447:0450:0453:0458:0466:0467:0469:0482:0485:0486:0487:0496:0499:0501:0502:0504:0505:0509:0510:0512:0513:0514:0517:0952:0980:0986:0992:1013:1019:0883:0890:0891:0894:0909:0910:0920:0923:0708:0709:0710:0711:0712:0714:0806:0812:1271:1276:1283:1288:1289:1291:1293:1294:1295:1296:1332:1670:1730:1872:1874:1886:1897:1935:1938:1947:1949:1765:1772:1802:1806:1832:1551:1562:2156:2165:2166:2179:2217:2234:2236:2270:2278:2285:2296:2324:2334:2346:1965:2017:2085:2090:2103:2110:2472:2474:2475:2476:2477:2478:2488:2614:2621:2634:2651:2661:2669:2491:2510:2536:2541:2547:2566:2383:2424:2443:2954:2992:2996:3011:3014:3015:3056:3058:3078:3079:2695:2696:2757:2758:2759:2760:2771:2773:2787:2795:2829:2839:2845:2856:2858:3188:3195:3196:3219:3227:3229:3350:3356:3362:3364:3370:3379:3380:3387:3399:3401:3402:3410:3415:3418:3239:3251:3269:3298:3299:3307:3319:3321:3330:3331:3334:3341:3087:3092:3093:3097:3098:3111:3150:3151:3665:3669:3693:3714:3723:3740:3773:3785:3816:3821:3839:3843:3861:3874:3443:3459:3471:3494:3506:3512:3517:3519:3524:3533:3537:3544:3545:3550:3561:3564:3570:3576:3584:3606:4013:4146:4155:4160:4164:4167:4170:4171:4174:4177:4180:4187:4191:4199:4243:4259:4079:4082:4085:4095:4102:4105:4106:4138:4139:3900:3912:3937:3944:4500:4280:4284:4292:4296:4304:4308:4312:4316:4320:4324:4325:4334:4801:4835:4993:4996:5004:5028:5031:5034:5066:4859:4911:4918:4920:4692:15158:4776:5322:5331:5332:5346:5384:5401:5407:5444:5468:5096:5112:5119:5120:5124:5125:5132:5133:5140:5184:5202:5272:5277:5284:5620:5632:5640:5798:5804:5831:5842:5856:5670:5699:5712:5753:14688:5542:5546:5550:5561:5566:5576:5579:5583:5587:5591:5904:5913:5923:5924:5953:6024:10558:10572:11452:11477:12432:12492:11483:11484:11559:9783:9786:9792:9801:9808:9815:10600:10633:10647:11605:11623:11631:11649:9857:9862:9873:10675:10679:11664:11681:11685:11725:10762:9942:9977:10788:10814:10815:23307:11848:9987:10020:11864:11875:10083:10104:11952:17605:10124:10126:10128:10132:10922:10925:10949:10952:10984:10985:11008:11025:11050:11051:11081:11101:11103:11106:12125:12126:12127:12146:10288:10310:11111:11112:11113:12173:12177:12178:12210:9502:9518:9524:9537:9541:11199:12229:9546:9550:9576:10376:10404:10412:11232:12274:12284:9594:9597:9629:9634:10429:11294:11321:11336:12338:9648:9653:9662:9664:9693:9694:9697:10489:10497:10501:11353:11418:21570:12403:17222:19278:12683:13888:13923:13939:13940:13944:15735:15752:15754:15767:15768:15769:15771:16423:14759:17276:17278:17279:12550:12551:12553:13973:14131:15877:15902:15908:12691:17286:17288:12576:12577:12600:12647:12648:12654:12655:14135:14141:14182:14207:16008:16180:17566:18202:14629:17572:17571:14530:14760:14728:12656:12657:12682:14232:14253:14266:16324:16343:16363:14778:15373:15364:13008:12723:12805:12813:14344:14346:14352:16456:18100:16751:19615:17494:14873:19390:12833:12859:12894:14399:14413:14414:14415:14416:14417:14418:14441:12942:12943:12968:12986:14477:14542:14547:14550:14563:9939:14750:14702:17406:14631:14690:14691:17495:15407:17573:17575:17576:15883:14777:17215:13099:14753:14757:14761:14769:14771:14772:14773:14775:14776:14793:17796:13136:13167:13181:13208:13213:13220:14818:14819:14860:14861:14876:14877:14896:18104:14768:15434:15435:15436:15405:18855:17267:17266:13311:15091:15136:15146:15152:17264:17336:17258:17259:17260:17261:17262:14770:13487:15222:15225:15232:15441:15437:11697:12690:12687:13500:13582:13594:13605:15375:15439:15440:15442:15603:16924:15445:15451:15452:15453:15454:15455:12131:16711:16212:16464:12684:13643:13651:13719:15499:15530:15534:15465:14743:14752:15430:15431:15432:15703:15450:14687:14780:13765:12692:15443:14755:15449:17574:17567:17570:13752:13800:15582:15644:15444:15448:15433:15446:15447:14766:15514:10484:17290:17291:17294:17273:12651:19237:20630:22387:23785:23786:23391:20650:26559:23447:17569:17579:17580:17568:15415:17151:21504:18098:23563:22581:22385:23021:21291:14742:14804:22274:15370:14808:18101:18102:21226:19446:19879:19950:14746:22231:21978:21977:21976:21981:21979:21982:16124:17328:23603:21961:21962:21986:21985:21984:21983:21379:21963:21964:21965:21966:21967:15384:17319:21154:13617:18493:22012:22005:22006:12689:20649:19404:14756:21980:24614:22067:22018:22017:22068:21305:23192:23195:23194:23193:15234:19411:18122:15498:14754:13308:5376:21994:22000:21993:21999:21996:17828:22001:22002:23541:22003:22004:22007:21997:21998:21995:22010:22011:22013:19607:22431:16904:23186:23185:23187:23188:25971:23189:23190:17269:15502:20971:19602:20760:16104:11715:22210:20897:17293:18289:18435:15765:14779:21109:21713:21136:20144:14774:16457:15927:21436:4193:21779:20890:23903:21851:19728:14767:14764:21124:12688:20133:17297:14744:19814:16292:19947:17715:15399:16844:19873:22117:3529:23587:16980:19435:21786:16055:13160:21970:21971:21972:21973:21974:21975:24512:22009:22008:21989:21990:22015:22016:23585:21110:21968:20509:22526:15933:15934:0451:19247:23259:19056:18900:18107:18106:16126:17287:17284:17283:21969:21987:21988:21992:22014:21710:23043:23338:21201:20965:20634:16267:17285:21158:14747:20486:15032:16319:25080:20015:15612:23480:27242:19241:15996:15935:24525:22702:3427:25101:23318:21471:17919:21927:9799:24041:21610:16565:24425:24426:24427:21641:22223:15867:15705:23886:21105:21817:20167:21405:5105:29446:30632:23911:20607:28907:28908:28909:24620:17921:11647:24435:17323:22206:10000:25639:27422:22341:16835:23430:23501:24605:24424:28853:24565:15788:16521:23296:27768:24021:23326:14745:14758:15056:12417:14546:2660:15460:25839:4272:3323:3487:26956:22383:26567:22354:15901:12363:14763:22548:28904:24511:5417:29169:24531:24869:25992:27335:15276:26897:17263:21804:27424:20163:24245:26415:3205:14471:3526:32066:27575:18053:3563:17274:26585:22024:25219:22580:27423:21116:21659:17166:1964:3305:23656:17578:17195:18286:16332:27499:10970:17257:17268:17265:21874:20893:23138:27590:27841:21358:2493:21346:19510:18961:20137:32067:19319:5187:3263:26656:17292:25276:32037:32036:25277:31917:22721:24353:31941:2153:20480:28472:22521:30104:28943:15075:21103:29031:26212:30032:30033:22125:4707:23996:21925:32328:33110:5122:30333:0135:23856:19568:29774:25805:27110:20886:2093:25375:25376:23674:28905:28917:28918:3760:4714:26235:22314:28216:24599:16294:27548:29856:25542:17299:17402:21179:28107:28485:20632:22524:27840:26668:14721:28088:23191:23874:24444:27113:23897:23577:10538:29381:30158:22862:26040:17577:21406:27124:31579:27516:23372:27167:24874:32408:3310:21417:23594:18518:26877:17920:26154:28854:20001:21416:17282:24746:3247:33010:23604:21112:21274