How to Delimit the Large comma separated string - Oracle Procedure

Question

I am working in Oracle 12C and passing comma separated list from my C# program to Oracle procedure and wanted to delimit the string and wanted to pass use this in dynamic sql. i am using CLOB in procedure to receive this from C# and Created a below function

CREATE OR REPLACE TYPE ARRAY AS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE FUNCTION PARSE_CSV (p_clob CLOB, p_separator VARCHAR2)
    RETURN ARRAY
    PIPELINED
AS
    v_size           NUMBER;
    v_start_pos      NUMBER := 1;
    v_new_position   NUMBER := 0;
    v_line           VARCHAR2 (4000);
    x_clob           CLOB := p_clob || TO_CLOB (p_separator);
BEGIN
    v_size := DBMS_LOB.getlength (x_clob);

    WHILE v_start_pos <= v_size
    LOOP
        v_new_position :=
            NVL (INSTR (x_clob, p_separator, v_start_pos), 4000);
        v_line := SUBSTR (x_clob, v_start_pos, v_new_position - v_start_pos);
        v_start_pos := v_new_position + LENGTH (p_separator);

        PIPE ROW (v_line);
    END LOOP;

    RETURN;
END;
/

but i am getting ORA-01704: string literal too long when i use this in dynamic sql. below the sample CSV data

PAR000015,PAR000016,PAR000017,PAR000018,PAR000019,PAR000020,PAR000021,PAR000022,PAR000023,PAR000024,PAR000025,PAR000026,PAR000027,PAR000028,PAR000029,PAR000030,PAR000031,PAR000032,PAR000033,PAR000034,PAR000035,PAR000036,PAR000037,PAR000038,PAR000039,PAR000040,PAR000041,PAR000042,PAR000043,PAR000044,PAR000045,PAR000046,PAR000047,PAR000048,PAR000049,PAR000050,PAR000051,PAR000052,PAR000053,PAR000054,PAR000055,PAR000056,PAR000057,PAR000058,PAR000059,PAR000060,PAR000061,PAR000062,PAR000063,PAR000064,PAR000065,PAR000066,PAR000067,PAR000068,PAR000069,PAR000070,PAR000071,PAR000072,PAR000073,PAR000074,PAR000075,PAR000076,PAR000077,PAR000078,PAR000079,PAR000080,PAR000081,PAR000082,PAR000083,PAR000084,PAR000085,PAR000086,PAR000087,PAR000088,PAR000089,PAR000090,PAR000091,PAR000092,PAR000093,PAR000094,PAR000095,PAR000096,PAR000097,PAR000098,PAR000099,PAR000100,PAR000101,PAR000102,PAR000103,PAR000104,PAR000105,PAR000106,PAR000107,PAR000108,PAR000109,PAR000110,PAR000111,PAR000112,PAR000113,PAR000114,PAR000115,PAR000116,PAR000117,PAR000118,PAR000119,PAR000120,PAR000121,PAR000122,PAR000123,PAR000124,PAR000125,PAR000126,PAR000127,PAR000128,PAR000129,PAR000130,PAR000131,PAR000132,PAR000133,PAR000134,PAR000135,PAR000136,PAR000137,PAR000138,PAR000139,PAR000140,PAR000141,PAR000142,PAR000143,PAR000144,PAR000145,PAR000146,PAR000147,PAR000148,PAR000149,PAR000150,PAR000151,PAR000152,PAR000153,PAR000154,PAR000155,PAR000156,PAR000157,PAR000158,PAR000159,PAR000160,PAR000161,PAR000162,PAR000163,PAR000164,PAR000165,PAR000166,PAR000167,PAR000168,PAR000169,PAR000170,PAR000171,PAR000172,PAR000173,PAR000174,PAR000175,PAR000176,PAR000177,PAR000178,PAR000179,PAR000180,PAR000181,PAR000182,PAR000183,PAR000184,PAR000185,PAR000186,PAR000187,PAR000188,PAR000189,PAR000190,PAR000191,PAR000192,PAR000193,PAR000194,PAR000195,PAR000196,PAR000197,PAR000198,PAR000199,PAR000200,PAR000201,PAR000202,PAR000203,PAR000204,PAR000205,PAR000206,PAR000207,PAR000208,PAR000209,PAR000210,PAR000211,PAR000212,PAR000213,PAR000214,PAR000215,PAR000216,PAR000217,PAR000218,PAR000219,PAR000220,PAR000221,PAR000222,PAR000223,PAR000224,PAR000225,PAR000226,PAR000227,PAR000228,PAR000229,PAR000230,PAR000231,PAR000232,PAR000233,PAR000234,PAR000235,PAR000236,PAR000237,PAR000238,PAR000239,PAR000240,PAR000241,PAR000242,PAR000243,PAR000244,PAR000245,PAR000246,PAR000247,PAR000248,PAR000249,PAR000250,PAR000251,PAR000252,PAR000253,PAR000254,PAR000255,PAR000256,PAR000257,PAR000258,PAR000259,PAR000260,PAR000261,PAR000262,PAR000263,PAR000264,PAR000265,PAR000266,PAR000267,PAR000268,PAR000269,PAR000270,PAR000271,PAR000272,PAR000273,PAR000274,PAR000275,PAR000276,PAR000277,PAR000278,PAR000279,PAR000280,PAR000281,PAR000282,PAR000283,PAR000284,PAR000285,PAR000286,PAR000287,PAR000288,PAR000289,PAR000290,PAR000291,PAR000292,PAR000293,PAR000294,PAR000295,PAR000296,PAR000297,PAR000298,PAR000299,PAR000300,PAR000301,PAR000302,PAR000303,PAR000304,PAR000305,PAR000306,PAR000307,PAR000308,PAR000309,PAR000310,PAR000311,PAR000312,PAR000313,PAR000314,PAR000315,PAR000316,PAR000317,PAR000318,PAR000319,PAR000320,PAR000321,PAR000322,PAR000323,PAR000324,PAR000325,PAR000326,PAR000327,PAR000328,PAR000329,PAR000330,PAR000331,PAR000332,PAR000333,PAR000334,PAR000335,PAR000336,PAR000337,PAR000338,PAR000339,PAR000340,PAR000341,PAR000342,PAR000343,PAR000344,PAR000345,PAR000346,PAR000347,PAR000348,PAR000349,PAR000350,PAR000351,PAR000352,PAR000353,PAR000354,PAR000355,PAR000356,PAR000357,PAR000358,PAR000359,PAR000360,PAR000361,PAR000362,PAR000363,PAR000364,PAR000365,PAR000366,PAR000367,PAR000368,PAR000369,PAR000370,PAR000371,PAR000372,PAR000373,PAR000374,PAR000375,PAR000376,PAR000377,PAR000378,PAR000379,PAR000380,PAR000381,PAR000382,PAR000383,PAR000384,PAR000385,PAR000386,PAR000387,PAR000388,PAR000389,PAR000390,PAR000391,PAR000392,PAR000393,PAR000394,PAR000395,PAR000396,PAR000397,PAR000398,PAR000399,PAR000400,PAR000401,PAR000402,PAR000403,PAR000404,PAR000405,PAR000406,PAR000407,PAR000408,PAR000409,PAR000410,PAR000411,PAR000412,PAR000413,PAR000414,PAR000415,PAR000416,PAR000417,PAR000418,PAR000419,PAR000420,PAR000421,PAR000422,PAR000423,PAR000424,PAR000425,PAR000426,PAR000427,PAR000428,PAR000429,PAR000430,PAR000431,PAR000432,PAR000433,PAR000434,PAR000435,PAR000436,PAR000437,PAR000438,PAR000439,PAR000440,PAR000441,PAR000442,PAR000443,PAR000444,PAR000445,PAR000446,PAR000447,PAR000448,PAR000449,PAR000450,PAR000451,PAR000452,PAR000453,PAR000454,PAR000455,PAR000456,PAR000457,PAR000458,PAR000459,PAR000460,PAR000461,PAR000462,PAR000463,PAR000464,PAR000465,PAR000466,PAR000467,PAR000468,PAR000469,PAR000470,PAR000471,PAR000472,PAR000473,PAR000474,PAR000475,PAR000476,PAR000477,PAR000478,PAR000479,PAR000480,PAR000481,PAR000482,PAR000483,PAR000484,PAR000485,PAR000486,PAR000487,PAR000488,PAR000489,PAR000490,PAR000491,PAR000492,PAR000493,PAR000494,PAR000495,PAR000496,PAR000497,PAR000498,PAR000499,PAR000500,PAR000501,PAR000502,PAR000503,PAR000504,PAR000505,PAR000506,PAR000507,PAR000508,PAR000509,PAR000510,PAR000511,PAR000512,PAR000513,PAR000514,PAR000515,PAR000516,PAR000517,PAR000518,PAR000519,PAR000520,PAR000521,PAR000522,PAR000523,PAR000524,PAR000525,PAR000526,PAR000527,PAR000528,PAR000529,PAR000530,PAR000531,PAR000532,PAR000533,PAR000534,PAR000535,PAR000536,PAR000537,PAR000538,PAR000539,PAR000540,PAR000541,PAR000542,PAR000543,PAR000544,PAR000545,PAR000546,PAR000547,PAR000548,PAR000549,PAR000550,PAR000551,PAR000552,PAR000553,PAR000554,PAR000555,PAR000556,PAR000557,PAR000558,PAR000559,PAR000560,PAR000561,PAR000562,PAR000563,PAR000564,PAR000565,PAR000566,PAR000567,PAR000568,PAR000569,PAR000570,PAR000571,PAR000572,PAR000573,PAR000574,PAR000575,PAR000576,PAR000577,PAR000578,PAR000579,PAR000580,PAR000581,PAR000582,PAR000583,PAR000584,PAR000585,PAR000586,PAR000587,PAR000588,PAR000589,PAR000590,PAR000591,PAR000592,PAR000593,PAR000594,PAR000595,PAR000596,PAR000597,PAR000598,PAR000599,PAR000600,PAR000601,PAR000602,PAR000603,PAR000604,PAR000605,PAR000606,PAR000607,PAR000608,PAR000609,PAR000610,PAR000611,PAR000612,PAR000613,PAR000614,PAR000615,PAR000616,PAR000617,PAR000618,PAR000619,PAR000620,PAR000621,PAR000622,PAR000623,PAR000624,PAR000625,PAR000626,PAR000627,PAR000628,PAR000629,PAR000630,PAR000631,PAR000632,PAR000633,PAR000634,PAR000635,PAR000636,PAR000637,PAR000638,PAR000639,PAR000640,PAR000641,PAR000642,PAR000643,PAR000644,PAR000645,PAR000646,PAR000647,PAR000648,PAR000649,PAR000650,PAR000651,PAR000652,PAR000653,PAR000654,PAR000655,PAR000656,PAR000657,PAR000658,PAR000659,PAR000660,PAR000661,PAR000662,PAR000663,PAR000664,PAR000665,PAR000666,PAR000667,PAR000668,PAR000669,PAR000670,PAR000671,PAR000672,PAR000673,PAR000674,PAR000675,PAR000676,PAR000677,PAR000678,PAR000679,PAR000680,PAR000681,PAR000682,PAR000683,PAR000684,PAR000685,PAR000686,PAR000687,PAR000688,PAR000689,PAR000690,PAR000691,PAR000692,PAR000693,PAR000694,PAR000695,PAR000696,PAR000697,PAR000698,PAR000699,PAR000700,PAR000701,PAR000702,PAR000703,PAR000704,PAR000705,PAR000706,PAR000707,PAR000708,PAR000709,PAR000710,PAR000711,PAR000712,PAR000713,PAR000714,PAR000715,PAR000716,PAR000717,PAR000718,PAR000719,PAR000720,PAR000721,PAR000722,PAR000723,PAR000724,PAR000725,PAR000726,PAR000727,PAR000728,PAR000729,PAR000730,PAR000731,PAR000732,PAR000733,PAR000734,PAR000735,PAR000736,PAR000737,PAR000738,PAR000739,PAR000740,PAR000741,PAR000742,PAR000743,PAR000744,PAR000745,PAR000746,PAR000747,PAR000748,PAR000749,PAR000750,PAR000751,PAR000752,PAR000753,PAR000754,PAR000755,PAR000756,PAR000757,PAR000758,PAR000759,PAR000760,PAR000761,PAR000762,PAR000763,PAR000764,PAR000765,PAR000766,PAR000767,PAR000768,PAR000769,PAR000770,PAR000771,PAR000772,PAR000773,PAR000774,PAR000775,PAR000776,PAR000777,PAR000778,PAR000779,PAR000780,PAR000781,PAR000782,PAR000783,PAR000784,PAR000785,PAR000786,PAR000787,PAR000788,PAR000789,PAR000790,PAR000791,PAR000792,PAR000793,PAR000794,PAR000795,PAR000796,PAR000797,PAR000798,PAR000799,PAR000800,PAR000801,PAR000802,PAR000803,PAR000804,PAR000805,PAR000806,PAR000807,PAR000808,PAR000809,PAR000810,PAR000811,PAR000812,PAR000813,PAR000814,PAR000815,PAR000816,PAR000817,PAR000818,PAR000819,PAR000820,PAR000821,PAR000822,PAR000823,PAR000824,PAR000825,PAR000826,PAR000827,PAR000828,PAR000829,PAR000830,PAR000831,PAR000832,PAR000833,PAR000834,PAR000835,PAR000836,PAR000837,PAR000838,PAR000839,PAR000840,PAR000841,PAR000842,PAR000843,PAR000844,PAR000845,PAR000846,PAR000847,PAR000848,PAR000849,PAR000850,PAR000851,PAR000852,PAR000853,PAR000854,PAR000855,PAR000856,PAR000857,PAR000858,PAR000859,PAR000860,PAR000861,PAR000862,PAR000863,PAR000864,PAR000865,PAR000866,PAR000867,PAR000868,PAR000869,PAR000870,PAR000871,PAR000872,PAR000873,PAR000874,PAR000875,PAR000876,PAR000877,PAR000878,PAR000879,PAR000880,PAR000881,PAR000882,PAR000883,PAR000884,PAR000885,PAR000886,PAR000887,PAR000888,PAR000889,PAR000890,PAR000891,PAR000892,PAR000893,PAR000894,PAR000895,PAR000896,PAR000897,PAR000898,PAR000899,PAR000900,PAR000901,PAR000902,PAR000903,PAR000904,PAR000905,PAR000906,PAR000907,PAR000908,PAR000909,PAR000910,PAR000911,PAR000912,PAR000913,PAR000914,PAR000915,PAR000916,PAR000917,PAR000918,PAR000919,PAR000920,PAR000921,PAR000922,PAR000923,PAR000924,PAR000925,PAR000926,PAR000927,PAR000928,PAR000929,PAR000930,PAR000931,PAR000932,PAR000933,PAR000934,PAR000935,PAR000936,PAR000937,PAR000938,PAR000939,PAR000940,PAR000941,PAR000942,PAR000943,PAR000944,PAR000945,PAR000946,PAR000947,PAR000948,PAR000949,PAR000950,PAR000951,PAR000952,PAR000953,PAR000954,PAR000955,PAR000956,PAR000957,PAR000958,PAR000959,PAR000960,PAR000961,PAR000962,PAR000963,PAR000964,PAR000965,PAR000966,PAR000967,PAR000968,PAR000969,PAR000970,PAR000971,PAR000972,PAR000973,PAR000974,PAR000975,PAR000976,PAR000977,PAR000978,PAR000979,PAR000980,PAR000981,PAR000982,PAR000983,PAR000984,PAR000985,PAR000986,PAR000987,PAR000988,PAR000989,PAR000990,PAR000991,PAR000992,PAR000993,PAR000994,PAR000995,PAR000996,PAR000997,PAR000998,PAR000999,PAR001000,PAR001001,PAR001002,PAR001003,PAR001004,PAR001005,PAR001006,PAR001007,PAR001008,PAR001009,PAR001010,PAR001011,PAR001012,PAR001013,PAR001014,PAR001015,PAR001016,PAR001017,PAR001018,PAR001019,PAR001020,PAR001021,PAR001022,PAR001023,PAR001024,PAR001025,PAR001026,PAR001027,PAR001028,PAR001029,PAR001030,PAR001031,PAR001032,PAR001033,PAR001034,PAR001035,PAR001036,PAR001037,PAR001038,PAR001039,PAR001040,PAR001041,PAR001042,PAR001043,PAR001044,PAR001045,PAR001046,PAR001047,PAR001048,PAR001049,PAR001050,PAR001051,PAR001052,PAR001053,PAR001054,PAR001055,PAR001056,PAR001057,PAR001058,PAR001059,PAR001060,PAR001061,PAR001062,PAR001063,PAR001064,PAR001065,PAR001066,PAR001067,PAR001068,PAR001069,PAR001070,PAR001071,PAR001072,PAR001073,PAR001074,PAR001075,PAR001076,PAR001077,PAR001078,PAR001079,PAR001080,PAR001081,PAR001082,PAR001083,PAR001084,PAR001085,PAR001086,PAR001087,PAR001088,PAR001089,PAR001090,PAR001091,PAR001092,PAR001093,PAR001094,PAR001095,PAR001096,PAR001097,PAR001098,PAR001099,PAR001100,PAR001101,PAR001102,PAR001103,PAR001104,PAR001105,PAR001106,PAR001107,PAR001108,PAR001109,PAR001110,PAR001111,PAR001112,PAR001113,PAR001114,PAR001115,PAR001116,PAR001117,PAR001118,PAR001119,PAR001120,PAR001121,PAR001122,PAR001123,PAR001124,PAR001125,PAR001126,PAR001127,PAR001128,PAR001129,PAR001130,PAR001131,PAR001132,PAR001133,PAR001134,PAR001135,PAR001136,PAR001137,PAR001138,PAR001139,PAR001140,PAR001141,PAR001142,PAR001143,PAR001144,PAR001145,PAR001146,PAR001147,PAR001148,PAR001149,PAR001150,PAR001151,PAR001152,PAR001153,PAR001154,PAR001155,PAR001156,PAR001157,PAR001158,PAR001159,PAR001160,PAR001161,PAR001162,PAR001163,PAR001164,PAR001165,PAR001166,PAR001167,PAR001168,PAR001169,PAR001170,PAR001171,PAR001172,PAR001173,PAR001174,PAR001175,PAR001176,PAR001177,PAR001178,PAR001179,PAR001180,PAR001181,PAR001182,PAR001183,PAR001184,PAR001185,PAR001186,PAR001187,PAR001188,PAR001189,PAR001190,PAR001191,PAR001192,PAR001193,PAR001194,PAR001195,PAR001196,PAR001197,PAR001198,PAR001199,PAR001200,PAR001201,PAR001202,PAR001203,PAR001204,PAR001205,PAR001206,PAR001207,PAR001208,PAR001209,PAR001210,PAR001211,PAR001212,PAR001213,PAR001214,PAR001215,PAR001216,PAR001217,PAR001218,PAR001219,PAR001220,PAR001221,PAR001222,PAR001223,PAR001224,PAR001225,PAR001226,PAR001227,PAR001228,PAR001229,PAR001230,PAR001231,PAR001232,PAR001233,PAR001234,PAR001235,PAR001236,PAR001237,PAR001238,PAR001239,PAR001240,PAR001241,PAR001242,PAR001243,PAR001244,PAR001245,PAR001246,PAR001247,PAR001248,PAR001249,PAR001250,PAR001251,PAR001252,PAR001253,PAR001254,PAR001255,PAR001256,PAR001257,PAR001258,PAR001259,PAR001260,PAR001261,PAR001262,PAR001263,PAR001264,PAR001265,PAR001266,PAR001267,PAR001268,PAR001269,PAR001270,PAR001271,PAR001272,PAR001273,PAR001274,PAR001275,PAR001276,PAR001277,PAR001278,PAR001279,PAR001280,PAR001281,PAR001282,PAR001283,PAR001284,PAR001285,PAR001286,PAR001287,PAR001288,PAR001289,PAR001290,PAR001291,PAR001292,PAR001293,PAR001294,PAR001295,PAR001296,PAR001297,PAR001298,PAR001299,PAR001300,PAR001301,PAR001302,PAR001303,PAR001304,PAR001305,PAR001306,PAR001307,PAR001308,PAR001309,PAR001310,PAR001311,PAR001312,PAR001313,PAR001314,PAR001315,PAR001316,PAR001317,PAR001318,PAR001319,PAR001320,PAR001321,PAR001322,PAR001323,PAR001324,PAR001325,PAR001326,PAR001327,PAR001328,PAR001329,PAR001330,PAR001331,PAR001332,PAR001333,PAR001334,PAR001335,PAR001336,PAR001337,PAR001338,PAR001339,PAR001340,PAR001341,PAR001342,PAR001343,PAR001344,PAR001345,PAR001346,PAR001347,PAR001348,PAR001349,PAR001350,PAR001351,PAR001352,PAR001353,PAR001354,PAR001355,PAR001356,PAR001357,PAR001358,PAR001359,PAR001360,PAR001361,PAR001362,PAR001363,PAR001364,PAR001365,PAR001366,PAR001367,PAR001368,PAR001369,PAR001370,PAR001371,PAR001372,PAR001373,PAR001374,PAR001375,PAR001376,PAR001377,PAR001378,PAR001379,PAR001380,PAR001381,PAR001382,PAR001383,PAR001384,PAR001385,PAR001386,PAR001387,PAR001388,PAR001389,PAR001390,PAR001391,PAR001392,PAR001393,PAR001394,PAR001395,PAR001396,PAR001397,PAR001398,PAR001399,PAR001400,PAR001401,PAR001402,PAR001403,PAR001404,PAR001405,PAR001406,PAR001407,PAR001408,PAR001409,PAR001410,PAR001411,PAR001412,PAR001413,PAR001414,PAR001415,PAR001416,PAR001417,PAR001418,PAR001419,PAR001420,PAR001421,PAR001422,PAR001423,PAR001424,PAR001425,PAR001426,PAR001427,PAR001428,PAR001429,PAR001430,PAR001431,PAR001432,PAR001433,PAR001434,PAR001435,PAR001436,PAR001437,PAR001438,PAR001439,PAR001440,PAR001441,PAR001442,PAR001443,PAR001444,PAR001445,PAR001446,PAR001447,PAR001448,PAR001449,PAR001450,PAR001451,PAR001452,PAR001453,PAR001454,PAR001455,PAR001456,PAR001457,PAR001458,PAR001459,PAR001460,PAR001461,PAR001462,PAR001463,PAR001464,PAR001465,PAR001466,PAR001467,PAR001468,PAR001469,PAR001470,PAR001471,PAR001472,PAR001473,PAR001474,PAR001475,PAR001476,PAR001477,PAR001478,PAR001479,PAR001480,PAR001481,PAR001482,PAR001483,PAR001484,PAR001485,PAR001486,PAR001487,PAR001488,PAR001489,PAR001490,PAR001491,PAR001492,PAR001493,PAR001494,PAR001495,PAR001496,PAR001497,PAR001498,PAR001499,PAR001500,PAR001501,PAR001502,PAR001503,PAR001504,PAR001505,PAR001506,PAR001507,PAR001508,PAR001509,PAR001510,PAR001511,PAR001512,PAR001513,PAR001514,PAR001515,PAR001516,PAR001517,PAR001518,PAR001519,PAR001520,PAR001521,PAR001522,PAR001523,PAR001524,PAR001525,PAR001526,PAR001527,PAR001528,PAR001529,PAR001530,PAR001531,PAR001532,PAR001533,PAR001534,PAR001535,PAR001536,PAR001537,PAR001538,PAR001539,PAR001540,PAR001541,PAR001542,PAR001543,PAR001544,PAR001545,PAR001546,PAR001547,PAR001548,PAR001549,PAR001550,PAR001551,PAR001552,PAR001553,PAR001554,PAR001555,PAR001556,PAR001557,PAR001558,PAR001559,PAR001560,PAR001561,PAR001562,PAR001563,PAR001564,PAR001565,PAR001566,PAR001567,PAR001568,PAR001569,PAR001570,PAR001571,PAR001572,PAR001573,PAR001574,PAR001575,PAR001576,PAR001577,PAR001578,PAR001579,PAR001580,PAR001581,PAR001582,PAR001583,PAR001584,PAR001585,PAR001586,PAR001587,PAR001588,PAR001589,PAR001590,PAR001591,PAR001592,PAR001593,PAR001594,PAR001595,PAR001596,PAR001597,PAR001598,PAR001599,PAR001600,PAR001601,PAR001602,PAR001603,PAR001604,PAR001605,PAR001606,PAR001607,PAR001608,PAR001609,PAR001610,PAR001611,PAR001612,PAR001613,PAR001614,PAR001615,PAR001616,PAR001617,PAR001618,PAR001619,PAR001620,PAR001621,PAR001622,PAR001623,PAR001624,PAR001625,PAR001626,PAR001627,PAR001628,PAR001629,PAR001630,PAR001631,PAR001632,PAR001633,PAR001634,PAR001635,PAR001636,PAR001637,PAR001638,PAR001639,PAR001640,PAR001641,PAR001642,PAR001643,PAR001644,PAR001645,PAR001646,PAR001647,PAR001648,PAR001649,PAR001650,PAR001651,PAR001652,PAR001653,PAR001654,PAR001655,PAR001656,PAR001657,PAR001658,PAR001659,PAR001660,PAR001661,PAR001662,PAR001663,PAR001664,PAR001665,PAR001666,PAR001667,PAR001668,PAR001669,PAR001670,PAR001671,PAR001672,PAR001673,PAR001674,PAR001675,PAR001676,PAR001677,PAR001678,PAR001679,PAR001680,PAR001681,PAR001682,PAR001683,PAR001684,PAR001685,PAR001686,PAR001687,PAR001688,PAR001689,PAR001690,PAR001691,PAR001692,PAR001693,PAR001694,PAR001695,PAR001696,PAR001697,PAR001698,PAR001699,PAR001700,PAR001701,PAR001702,PAR001703,PAR001704,PAR001705,PAR001706,PAR001707,PAR001708,PAR001709,PAR001710,PAR001711,PAR001712,PAR001713,PAR001714,PAR001715,PAR001716,PAR001717,PAR001718,PAR001719,PAR001720,PAR001721,PAR001722,PAR001723,PAR001724,PAR001725,PAR001726,PAR001727,PAR001728,PAR001729,PAR001730,PAR001731,PAR001732,PAR001733,PAR001734,PAR001735,PAR001736,PAR001737,PAR001738,PAR001739,PAR001740,PAR001741,PAR001742,PAR001743,PAR001744,PAR001745,PAR001746,PAR001747,PAR001748,PAR001749,PAR001750,PAR001751,PAR001752,PAR001753,PAR001754,PAR001755,PAR001756,PAR001757,PAR001758,PAR001759,PAR001760,PAR001761,PAR001762,PAR001763,PAR001764,PAR001765,PAR001766,PAR001767,PAR001768,PAR001769,PAR001770,PAR001771,PAR001772,PAR001773,PAR001774,PAR001775,PAR001776,PAR001777,PAR001778,PAR001779,PAR001780,PAR001781,PAR001782,PAR001783,PAR001784,PAR001785,PAR001786,PAR001787,PAR001788,PAR001789,PAR001790,PAR001791,PAR001792,PAR001793,PAR001794,PAR001795,PAR001796,PAR001797,PAR001798,PAR001799,PAR001800,PAR001801,PAR001802,PAR001803,PAR001804,PAR001805,PAR001806,PAR001807,PAR001808,PAR001809,PAR001810,PAR001811,PAR001812,PAR001813,PAR001814,PAR001815,PAR001816,PAR001817,PAR001818,PAR001819,PAR001820,PAR001821,PAR001822,PAR001823,PAR001824,PAR001825,PAR001826,PAR001827,PAR001828,PAR001829,PAR001830,PAR001831,PAR001832,PAR001833,PAR001834,PAR001835,PAR001836,PAR001837,PAR001838,PAR001839,PAR001840,PAR001841,PAR001842,PAR001843,PAR001844,PAR001845,PAR001846,PAR001847,PAR001848,PAR001849,PAR001850,PAR001851,PAR001852,PAR001853,PAR001854,PAR001855,PAR001856,PAR001857,PAR001858,PAR001859,PAR001860,PAR001861,PAR001862,PAR001863,PAR001864,PAR001865,PAR001866,PAR001867,PAR001868,PAR001869,PAR001870,PAR001871,PAR001872,PAR001873,PAR001874,PAR001875,PAR001876,PAR001877,PAR001878,PAR001879,PAR001880,PAR001881,PAR001882,PAR001883,PAR001884,PAR001885,PAR001886,PAR001887,PAR001888,PAR001889,PAR001890,PAR001891,PAR001892,PAR001893,PAR001894,PAR001895,PAR001896,PAR001897,PAR001898,PAR001899,PAR001900,PAR001901,PAR001902,PAR001903,PAR001904,PAR001905,PAR001906,PAR001907,PAR001908,PAR001909,PAR001910,PAR001911,PAR001912,PAR001913,PAR001914,PAR001915,PAR001916,PAR001917,PAR001918,PAR001919,PAR001920,PAR001921,PAR001922,PAR001923,PAR001924,PAR001925,PAR001926,PAR001927,PAR001928,PAR001929,PAR001930,PAR001931,PAR001932,PAR001933,PAR001934,PAR001935,PAR001936,PAR001937,PAR001938,PAR001939,PAR001940,PAR001941,PAR001942,PAR001943,PAR001944,PAR001945,PAR001946,PAR001947,PAR001948,PAR001949,PAR001950,PAR001951,PAR001952,PAR001953,PAR001954,PAR001955,PAR001956,PAR001957,PAR001958,PAR001959,PAR001960,PAR001961,PAR001962,PAR001963,PAR001964,PAR001965,PAR001966,PAR001967,PAR001968,PAR001969,PAR001970,PAR001971,PAR001972,PAR001973,PAR001974,PAR001975,PAR001976,PAR001977,PAR001978,PAR001979,PAR001980,PAR001981,PAR001982,PAR001983,PAR001984,PAR001985,PAR001986,PAR001987,PAR001988,PAR001989,PAR001990,PAR001991,PAR001992,PAR001993,PAR001994,PAR001995,PAR001996,PAR001997,PAR001998,PAR001999,PAR002000,PAR002001,PAR002002,PAR002003,PAR002004,PAR002005,PAR002006,PAR002007,PAR002008,PAR002009,PAR002010,PAR002011,PAR002012,PAR002013,PAR002014,PAR002015,PAR002016,PAR002017,PAR002018,PAR002019,PAR002020,PAR002021,PAR002022,PAR002023,PAR002024,PAR002025,PAR002026,PAR002027,PAR002028,PAR002029,PAR002030,PAR002031,PAR002032,PAR002033,PAR002034,PAR002035,PAR002036,PAR002037,PAR002038,PAR002039,PAR002040,PAR002041,PAR002042,PAR002043,PAR002044,PAR002045,PAR002046,PAR002047,PAR002048,PAR002049,PAR002050,PAR002051,PAR002052,PAR002053,PAR002054,PAR002055,PAR002056,PAR002057,PAR002058,PAR002059,PAR002060,PAR002061,PAR002062,PAR002063,PAR002064,PAR002065,PAR002066,PAR002067,PAR002068,PAR002069,PAR002070,PAR002071,PAR002072,PAR002073,PAR002074,PAR002075,PAR002076,PAR002077,PAR002078,PAR002079,PAR002080,PAR002081,PAR002082,PAR002083,PAR002084,PAR002085,PAR002086,PAR002087,PAR002088,PAR002089,PAR002090,PAR002091,PAR002092,PAR002093,PAR002094,PAR002095,PAR002096,PAR002097,PAR002098,PAR002099,PAR002100,PAR002101,PAR002102,PAR002103,PAR002104,PAR002105,PAR002106,PAR002107,PAR002108,PAR002109,PAR002110,PAR002111,PAR002112,PAR002113,PAR002114,PAR002115,PAR002116,PAR002117,PAR002118,PAR002119,PAR002120,PAR002121,PAR002122,PAR002123,PAR002124,PAR002125,PAR002126,PAR002127,PAR002128,PAR002129,PAR002130,PAR002131,PAR002132,PAR002133,PAR002134,PAR002135,PAR002136,PAR002137,PAR002138,PAR002139,PAR002140,PAR002141,PAR002142,PAR002143,PAR002144,PAR002145,PAR002146,PAR002147,PAR002148,PAR002149,PAR002150,PAR002151,PAR002152,PAR002153,PAR002154,PAR002155,PAR002156,PAR002157,PAR002158,PAR002159,PAR002160,PAR002161,PAR002162,PAR002163,PAR002164,PAR002165,PAR002166,PAR002167,PAR002168,PAR002169,PAR002170,PAR002171,PAR002172,PAR002173,PAR002174,PAR002175,PAR002176,PAR002177,PAR002178,PAR002179,PAR002180,PAR002181,PAR002182,PAR002183,PAR002184,PAR002185,PAR002186,PAR002187,PAR002188,PAR002189,PAR002190,PAR002191,PAR002192,PAR002193,PAR002194,PAR002195,PAR002196,PAR002197,PAR002198,PAR002199,PAR002200,PAR002201,PAR002202,PAR002203,PAR002204,PAR002205,PAR002206,PAR002207,PAR002208,PAR002209,PAR002210,PAR002211,PAR002212,PAR002213,PAR002214,PAR002215,PAR002216,PAR002217,PAR002218,PAR002219,PAR002220,PAR002221,PAR002222,PAR002223,PAR002224,PAR002225,PAR002226,PAR002227,PAR002228,PAR002229,PAR002230,PAR002231,PAR002232,PAR002233,PAR002234,PAR002235,PAR002236,PAR002237,PAR002238,PAR002239,PAR002240,PAR002241,PAR002242,PAR002243,PAR002244,PAR002245,PAR002246,PAR002247,PAR002248,PAR002249,PAR002250,PAR002251,PAR002252,PAR002253,PAR002254,PAR002255,PAR002256,PAR002257,PAR002258,PAR002259,PAR002260,PAR002261

please help on this issue or any sample post that shows the sql to solve this issue.

Works fine in 19c. I can't recall if the || and SUBSTR were fully supported for >4KB LOBs back in 12c. In older versions some of these returned varchars and would blow up if they exceeded 4KB. By the way, there is no dynamic SQL here. — Paul W, Aug 01 '23 at 03:14
Please clarify what do you mean by *when i use this in dynamic sql*. There's no any dynamic in your code — astentx, Aug 01 '23 at 07:26

score 0 · Answer 1 · answered Aug 01 '23 at 08:39

You do not need to concatenate the delimiter to the end of the string. Instead, you can directly use the passed CLOB and handle the final list element as a special case (and it would allow you to differentiate between a NULL CLOB and an EMPTY_CLOB()).

Adapting my code from this answer into a PIPELINED function:

CREATE OR REPLACE FUNCTION parse_csv(
  i_str    IN  CLOB,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN array PIPELINED DETERMINISTIC
AS
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len IS NULL THEN
    RETURN;
  END IF;

  p_end := INSTR( i_str, i_delim, p_start );
  WHILE p_end > 0 LOOP
    PIPE ROW (SUBSTR( i_str, p_start, p_end - p_start ));
    p_start := p_end + c_ld;
    p_end := INSTR( i_str, i_delim, p_start );
  END LOOP;
  IF p_start <= c_len + 1 THEN
      PIPE ROW (SUBSTR( i_str, p_start, c_len - p_start + 1 ));
  END IF;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    NULL;
END;
/

Note: Handling the NO_DATA_NEEDED exception is not necessary in this particular function and could be omitted. However, when you are dealing with PIPELINED functions it is good practice to recognize that it may be necessary to clean up after the function if it is terminated early and get into the habit of including the exception handling so that when you do need to tidy up (i.e. close cursors, etc.) then it is not forgotten.

Then, for the sample data:

CREATE TABLE table_name (id, data) AS
SELECT 1, EMPTY_CLOB() || (SELECT LISTAGG('PAR' || LPAD(LEVEL + 14, 6, '0'), ',') WITHIN GROUP (ORDER BY LEVEL)
                           FROM   DUAL
                           CONNECT BY LEVEL <= 40)
                       || ','
                       || (SELECT LISTAGG('PAR' || LPAD(LEVEL + 54, 6, '0'), ',') WITHIN GROUP (ORDER BY LEVEL)
                           FROM   DUAL
                           CONNECT BY LEVEL <= 40)
FROM   DUAL UNION ALL
SELECT 2, EMPTY_CLOB() || ',' FROM DUAL UNION ALL
SELECT 3, EMPTY_CLOB() FROM DUAL UNION ALL
SELECT 4, NULL FROM DUAL;

The query:

SELECT t.id, d.column_value
FROM   table_name t
       CROSS APPLY TABLE(parse_csv(t.data, ',')) d

Outputs:

ID	COLUMN_VALUE
1	PAR000015
1	PAR000016
1	PAR000017
...	...
1	PAR000092
1	PAR000093
1	PAR000094
2	null
2	null
3	null

fiddle

i don't know how to reply to you with the code. so added my response as another answer. still this is unresolved. any suggestions please — user2631832, Aug 01 '23 at 14:02
@user2631832 A string literal is limited to 4000 characters. If you want to generate a `CLOB` of more than 4000 characters from string literals then you will need to concatenate multiple literals together. `l_SEARCHSTRING := EMPTY_CLOB() || 'string with first 4000 characters' || 'string with next 4000 characters' || 'etc.'` — MT0, Aug 04 '23 at 14:05
Also, don't use answers to reply to people. Firstly, answers are for answering the question (and replies generally are not answers so are likely to get flagged and removed) and, secondly, the people you are trying to talk to are not likely to see your replies. — MT0, Aug 04 '23 at 14:06
tried the below to get the format you said. declare str clob := to_clob('PAR000015,PAR000016,PAR000017,PAR000018,PAR000019'); v_len number; currChar varchar2(1000) := ''; SEARCHSTRING clob := EMPTY_CLOB(); begin v_len := length(str); for i in 1..v_len Loop currChar := substr(str,i,1000); dbms_output.put_line('val' || currChar); SEARCHSTRING := SEARCHSTRING || currChar; currChar := ''; End loop; end; — user2631832, Aug 07 '23 at 02:03
was trying to cut each 1000 characters to form the string like you suggested and i am doing something wrong in thee. any suggestions or corrections please — user2631832, Aug 07 '23 at 02:04

How to Delimit the Large comma separated string - Oracle Procedure

1 Answers1