Home » SQL & PL/SQL » SQL & PL/SQL » How to Delimit the Large comma separated string - Oracle Procedure (oracle 12c)
How to Delimit the Large comma separated string - Oracle Procedure [message #687957] |
Mon, 31 July 2023 21:10 |
|
born2achieve
Messages: 9 Registered: July 2012
|
Junior Member |
|
|
0
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,PAR 000028,PAR000029,PAR000030,PAR000031,PAR000032,PAR000033,PAR000034,PAR000035,PAR000036,PAR000037,PAR000038,PAR000039,PAR000040,PAR000 041,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,PA R000068,PAR000069,PAR000070,PAR000071,PAR000072,PAR000073,PAR000074,PAR000075,PAR000076,PAR000077,PAR000078,PAR000079,PAR000080,PAR00 0081,PAR000082,PAR000083,PAR000084,PAR000085,PAR000086,PAR000087,PAR000088,PAR000089,PAR000090,PAR000091,PAR000092,PAR000093,PAR00009 4,PAR000095,PAR000096,PAR000097,PAR000098,PAR000099,PAR000100,PAR000101,PAR000102,PAR000103,PAR000104,PAR000105,PAR000106,PAR000107,P AR000108,PAR000109,PAR000110,PAR000111,PAR000112,PAR000113,PAR000114,PAR000115,PAR000116,PAR000117,PAR000118,PAR000119,PAR000120,PAR0 00121,PAR000122,PAR000123,PAR000124,PAR000125,PAR000126,PAR000127,PAR000128,PAR000129,PAR000130,PAR000131,PAR000132,PAR000133,PAR0001 34,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,PAR 000161,PAR000162,PAR000163,PAR000164,PAR000165,PAR000166,PAR000167,PAR000168,PAR000169,PAR000170,PAR000171,PAR000172,PAR000173,PAR000 174,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,PA R000201,PAR000202,PAR000203,PAR000204,PAR000205,PAR000206,PAR000207,PAR000208,PAR000209,PAR000210,PAR000211,PAR000212,PAR000213,PAR00 0214,PAR000215,PAR000216,PAR000217,PAR000218,PAR000219,PAR000220,PAR000221,PAR000222,PAR000223,PAR000224,PAR000225,PAR000226,PAR00022 7,PAR000228,PAR000229,PAR000230,PAR000231,PAR000232,PAR000233,PAR000234,PAR000235,PAR000236,PAR000237,PAR000238,PAR000239,PAR000240,P AR000241,PAR000242,PAR000243,PAR000244,PAR000245,PAR000246,PAR000247,PAR000248,PAR000249,PAR000250,PAR000251,PAR000252,PAR000253,PAR0 00254,PAR000255,PAR000256,PAR000257,PAR000258,PAR000259,PAR000260,PAR000261,PAR000262,PAR000263,PAR000264,PAR000265,PAR000266,PAR0002 67,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,PAR 000294,PAR000295,PAR000296,PAR000297,PAR000298,PAR000299,PAR000300,PAR000301,PAR000302,PAR000303,PAR000304,PAR000305,PAR000306,PAR000 307,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,PA R000334,PAR000335,PAR000336,PAR000337,PAR000338,PAR000339,PAR000340,PAR000341,PAR000342,PAR000343,PAR000344,PAR000345,PAR000346,PAR00 0347,PAR000348,PAR000349,PAR000350,PAR000351,PAR000352,PAR000353,PAR000354,PAR000355,PAR000356,PAR000357,PAR000358,PAR000359,PAR00036 0,PAR000361,PAR000362,PAR000363,PAR000364,PAR000365,PAR000366,PAR000367,PAR000368,PAR000369,PAR000370,PAR000371,PAR000372,PAR000373,P AR000374,PAR000375,PAR000376,PAR000377,PAR000378,PAR000379,PAR000380,PAR000381,PAR000382,PAR000383,PAR000384,PAR000385,PAR000386,PAR0 00387,PAR000388,PAR000389,PAR000390,PAR000391,PAR000392,PAR000393,PAR000394,PAR000395,PAR000396,PAR000397,PAR000398,PAR000399,PAR0004 00,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,PAR 000427,PAR000428,PAR000429,PAR000430,PAR000431,PAR000432,PAR000433,PAR000434,PAR000435,PAR000436,PAR000437,PAR000438,PAR000439,PAR000 440,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,PA R000467,PAR000468,PAR000469,PAR000470,PAR000471,PAR000472,PAR000473,PAR000474,PAR000475,PAR000476,PAR000477,PAR000478,PAR000479,PAR00 0480,PAR000481,PAR000482,PAR000483,PAR000484,PAR000485,PAR000486,PAR000487,PAR000488,PAR000489,PAR000490,PAR000491,PAR000492,PAR00049 3,PAR000494,PAR000495,PAR000496,PAR000497,PAR000498,PAR000499,PAR000500,PAR000501,PAR000502,PAR000503,PAR000504,PAR000505,PAR000506,P AR000507,PAR000508,PAR000509,PAR000510,PAR000511,PAR000512,PAR000513,PAR000514,PAR000515,PAR000516,PAR000517,PAR000518,PAR000519,PAR0 00520,PAR000521,PAR000522,PAR000523,PAR000524,PAR000525,PAR000526,PAR000527,PAR000528,PAR000529,PAR000530,PAR000531,PAR000532,PAR0005 33,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,PAR 000560,PAR000561,PAR000562,PAR000563,PAR000564,PAR000565,PAR000566,PAR000567,PAR000568,PAR000569,PAR000570,PAR000571,PAR000572,PAR000 573,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,PA R000600,PAR000601,PAR000602,PAR000603,PAR000604,PAR000605,PAR000606,PAR000607,PAR000608,PAR000609,PAR000610,PAR000611,PAR000612,PAR00 0613,PAR000614,PAR000615,PAR000616,PAR000617,PAR000618,PAR000619,PAR000620,PAR000621,PAR000622,PAR000623,PAR000624,PAR000625,PAR00062 6,PAR000627,PAR000628,PAR000629,PAR000630,PAR000631,PAR000632,PAR000633,PAR000634,PAR000635,PAR000636,PAR000637,PAR000638,PAR000639,P AR000640,PAR000641,PAR000642,PAR000643,PAR000644,PAR000645,PAR000646,PAR000647,PAR000648,PAR000649,PAR000650,PAR000651,PAR000652,PAR0 00653,PAR000654,PAR000655,PAR000656,PAR000657,PAR000658,PAR000659,PAR000660,PAR000661,PAR000662,PAR000663,PAR000664,PAR000665,PAR0006 66,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,PAR 000693,PAR000694,PAR000695,PAR000696,PAR000697,PAR000698,PAR000699,PAR000700,PAR000701,PAR000702,PAR000703,PAR000704,PAR000705,PAR000 706,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,PA R000733,PAR000734,PAR000735,PAR000736,PAR000737,PAR000738,PAR000739,PAR000740,PAR000741,PAR000742,PAR000743,PAR000744,PAR000745,PAR00 0746,PAR000747,PAR000748,PAR000749,PAR000750,PAR000751,PAR000752,PAR000753,PAR000754,PAR000755,PAR000756,PAR000757,PAR000758,PAR00075 9,PAR000760,PAR000761,PAR000762,PAR000763,PAR000764,PAR000765,PAR000766,PAR000767,PAR000768,PAR000769,PAR000770,PAR000771,PAR000772,P AR000773,PAR000774,PAR000775,PAR000776,PAR000777,PAR000778,PAR000779,PAR000780,PAR000781,PAR000782,PAR000783,PAR000784,PAR000785,PAR0 00786,PAR000787,PAR000788,PAR000789,PAR000790,PAR000791,PAR000792,PAR000793,PAR000794,PAR000795,PAR000796,PAR000797,PAR000798,PAR0007 99,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,PAR 000826,PAR000827,PAR000828,PAR000829,PAR000830,PAR000831,PAR000832,PAR000833,PAR000834,PAR000835,PAR000836,PAR000837,PAR000838,PAR000 839,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,PA R000866,PAR000867,PAR000868,PAR000869,PAR000870,PAR000871,PAR000872,PAR000873,PAR000874,PAR000875,PAR000876,PAR000877,PAR000878,PAR00 0879,PAR000880,PAR000881,PAR000882,PAR000883,PAR000884,PAR000885,PAR000886,PAR000887,PAR000888,PAR000889,PAR000890,PAR000891,PAR00089 2,PAR000893,PAR000894,PAR000895,PAR000896,PAR000897,PAR000898,PAR000899,PAR000900,PAR000901,PAR000902,PAR000903,PAR000904,PAR000905,P AR000906,PAR000907,PAR000908,PAR000909,PAR000910,PAR000911,PAR000912,PAR000913,PAR000914,PAR000915,PAR000916,PAR000917,PAR000918,PAR0 00919,PAR000920,PAR000921,PAR000922,PAR000923,PAR000924,PAR000925,PAR000926,PAR000927,PAR000928,PAR000929,PAR000930,PAR000931,PAR0009 32,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,PAR 000959,PAR000960,PAR000961,PAR000962,PAR000963,PAR000964,PAR000965,PAR000966,PAR000967,PAR000968,PAR000969,PAR000970,PAR000971,PAR000 972,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,PA R000999,PAR001000,PAR001001,PAR001002,PAR001003,PAR001004,PAR001005,PAR001006,PAR001007,PAR001008,PAR001009,PAR001010,PAR001011,PAR00 1012,PAR001013,PAR001014,PAR001015,PAR001016,PAR001017,PAR001018,PAR001019,PAR001020,PAR001021,PAR001022,PAR001023,PAR001024,PAR00102 5,PAR001026,PAR001027,PAR001028,PAR001029,PAR001030,PAR001031,PAR001032,PAR001033,PAR001034,PAR001035,PAR001036,PAR001037,PAR001038,P AR001039,PAR001040,PAR001041,PAR001042,PAR001043,PAR001044,PAR001045,PAR001046,PAR001047,PAR001048,PAR001049,PAR001050,PAR001051,PAR0 01052,PAR001053,PAR001054,PAR001055,PAR001056,PAR001057,PAR001058,PAR001059,PAR001060,PAR001061,PAR001062,PAR001063,PAR001064,PAR0010 65,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,PAR 001092,PAR001093,PAR001094,PAR001095,PAR001096,PAR001097,PAR001098,PAR001099,PAR001100,PAR001101,PAR001102,PAR001103,PAR001104,PAR001 105,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,PA R001132,PAR001133,PAR001134,PAR001135,PAR001136,PAR001137,PAR001138,PAR001139,PAR001140,PAR001141,PAR001142,PAR001143,PAR001144,PAR00 1145,PAR001146,PAR001147,PAR001148,PAR001149,PAR001150,PAR001151,PAR001152,PAR001153,PAR001154,PAR001155,PAR001156,PAR001157,PAR00115 8,PAR001159,PAR001160,PAR001161,PAR001162,PAR001163,PAR001164,PAR001165,PAR001166,PAR001167,PAR001168,PAR001169,PAR001170,PAR001171,P AR001172,PAR001173,PAR001174,PAR001175,PAR001176,PAR001177,PAR001178,PAR001179,PAR001180,PAR001181,PAR001182,PAR001183,PAR001184,PAR0 01185,PAR001186,PAR001187,PAR001188,PAR001189,PAR001190,PAR001191,PAR001192,PAR001193,PAR001194,PAR001195,PAR001196,PAR001197,PAR0011 98,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,PAR 001225,PAR001226,PAR001227,PAR001228,PAR001229,PAR001230,PAR001231,PAR001232,PAR001233,PAR001234,PAR001235,PAR001236,PAR001237,PAR001 238,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,PA R001265,PAR001266,PAR001267,PAR001268,PAR001269,PAR001270,PAR001271,PAR001272,PAR001273,PAR001274,PAR001275,PAR001276,PAR001277,PAR00 1278,PAR001279,PAR001280,PAR001281,PAR001282,PAR001283,PAR001284,PAR001285,PAR001286,PAR001287,PAR001288,PAR001289,PAR001290,PAR00129 1,PAR001292,PAR001293,PAR001294,PAR001295,PAR001296,PAR001297,PAR001298,PAR001299,PAR001300,PAR001301,PAR001302,PAR001303,PAR001304,P AR001305,PAR001306,PAR001307,PAR001308,PAR001309,PAR001310,PAR001311,PAR001312,PAR001313,PAR001314,PAR001315,PAR001316,PAR001317,PAR0 01318,PAR001319,PAR001320,PAR001321,PAR001322,PAR001323,PAR001324,PAR001325,PAR001326,PAR001327,PAR001328,PAR001329,PAR001330,PAR0013 31,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,PAR 001358,PAR001359,PAR001360,PAR001361,PAR001362,PAR001363,PAR001364,PAR001365,PAR001366,PAR001367,PAR001368,PAR001369,PAR001370,PAR001 371,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,PA R001398,PAR001399,PAR001400,PAR001401,PAR001402,PAR001403,PAR001404,PAR001405,PAR001406,PAR001407,PAR001408,PAR001409,PAR001410,PAR00 1411,PAR001412,PAR001413,PAR001414,PAR001415,PAR001416,PAR001417,PAR001418,PAR001419,PAR001420,PAR001421,PAR001422,PAR001423,PAR00142 4,PAR001425,PAR001426,PAR001427,PAR001428,PAR001429,PAR001430,PAR001431,PAR001432,PAR001433,PAR001434,PAR001435,PAR001436,PAR001437,P AR001438,PAR001439,PAR001440,PAR001441,PAR001442,PAR001443,PAR001444,PAR001445,PAR001446,PAR001447,PAR001448,PAR001449,PAR001450,PAR0 01451,PAR001452,PAR001453,PAR001454,PAR001455,PAR001456,PAR001457,PAR001458,PAR001459,PAR001460,PAR001461,PAR001462,PAR001463,PAR0014 64,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,PAR 001491,PAR001492,PAR001493,PAR001494,PAR001495,PAR001496,PAR001497,PAR001498,PAR001499,PAR001500,PAR001501,PAR001502,PAR001503,PAR001 504,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,PA R001531,PAR001532,PAR001533,PAR001534,PAR001535,PAR001536,PAR001537,PAR001538,PAR001539,PAR001540,PAR001541,PAR001542,PAR001543,PAR00 1544,PAR001545,PAR001546,PAR001547,PAR001548,PAR001549,PAR001550,PAR001551,PAR001552,PAR001553,PAR001554,PAR001555,PAR001556,PAR00155 7,PAR001558,PAR001559,PAR001560,PAR001561,PAR001562,PAR001563,PAR001564,PAR001565,PAR001566,PAR001567,PAR001568,PAR001569,PAR001570,P AR001571,PAR001572,PAR001573,PAR001574,PAR001575,PAR001576,PAR001577,PAR001578,PAR001579,PAR001580,PAR001581,PAR001582,PAR001583,PAR0 01584,PAR001585,PAR001586,PAR001587,PAR001588,PAR001589,PAR001590,PAR001591,PAR001592,PAR001593,PAR001594,PAR001595,PAR001596,PAR0015 97,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,PAR 001624,PAR001625,PAR001626,PAR001627,PAR001628,PAR001629,PAR001630,PAR001631,PAR001632,PAR001633,PAR001634,PAR001635,PAR001636,PAR001 637,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,PA R001664,PAR001665,PAR001666,PAR001667,PAR001668,PAR001669,PAR001670,PAR001671,PAR001672,PAR001673,PAR001674,PAR001675,PAR001676,PAR00 1677,PAR001678,PAR001679,PAR001680,PAR001681,PAR001682,PAR001683,PAR001684,PAR001685,PAR001686,PAR001687,PAR001688,PAR001689,PAR00169 0,PAR001691,PAR001692,PAR001693,PAR001694,PAR001695,PAR001696,PAR001697,PAR001698,PAR001699,PAR001700,PAR001701,PAR001702,PAR001703,P AR001704,PAR001705,PAR001706,PAR001707,PAR001708,PAR001709,PAR001710,PAR001711,PAR001712,PAR001713,PAR001714,PAR001715,PAR001716,PAR0 01717,PAR001718,PAR001719,PAR001720,PAR001721,PAR001722,PAR001723,PAR001724,PAR001725,PAR001726,PAR001727,PAR001728,PAR001729,PAR0017 30,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,PAR 001757,PAR001758,PAR001759,PAR001760,PAR001761,PAR001762,PAR001763,PAR001764,PAR001765,PAR001766,PAR001767,PAR001768,PAR001769,PAR001 770,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,PA R001797,PAR001798,PAR001799,PAR001800,PAR001801,PAR001802,PAR001803,PAR001804,PAR001805,PAR001806,PAR001807,PAR001808,PAR001809,PAR00 1810,PAR001811,PAR001812,PAR001813,PAR001814,PAR001815,PAR001816,PAR001817,PAR001818,PAR001819,PAR001820,PAR001821,PAR001822,PAR00182 3,PAR001824,PAR001825,PAR001826,PAR001827,PAR001828,PAR001829,PAR001830,PAR001831,PAR001832,PAR001833,PAR001834,PAR001835,PAR001836,P AR001837,PAR001838,PAR001839,PAR001840,PAR001841,PAR001842,PAR001843,PAR001844,PAR001845,PAR001846,PAR001847,PAR001848,PAR001849,PAR0 01850,PAR001851,PAR001852,PAR001853,PAR001854,PAR001855,PAR001856,PAR001857,PAR001858,PAR001859,PAR001860,PAR001861,PAR001862,PAR0018 63,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,PAR 001890,PAR001891,PAR001892,PAR001893,PAR001894,PAR001895,PAR001896,PAR001897,PAR001898,PAR001899,PAR001900,PAR001901,PAR001902,PAR001 903,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,PA R001930,PAR001931,PAR001932,PAR001933,PAR001934,PAR001935,PAR001936,PAR001937,PAR001938,PAR001939,PAR001940,PAR001941,PAR001942,PAR00 1943,PAR001944,PAR001945,PAR001946,PAR001947,PAR001948,PAR001949,PAR001950,PAR001951,PAR001952,PAR001953,PAR001954,PAR001955,PAR00195 6,PAR001957,PAR001958,PAR001959,PAR001960,PAR001961,PAR001962,PAR001963,PAR001964,PAR001965,PAR001966,PAR001967,PAR001968,PAR001969,P AR001970,PAR001971,PAR001972,PAR001973,PAR001974,PAR001975,PAR001976,PAR001977,PAR001978,PAR001979,PAR001980,PAR001981,PAR001982,PAR0 01983,PAR001984,PAR001985,PAR001986,PAR001987,PAR001988,PAR001989,PAR001990,PAR001991,PAR001992,PAR001993,PAR001994,PAR001995,PAR0019 96,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,PAR 002023,PAR002024,PAR002025,PAR002026,PAR002027,PAR002028,PAR002029,PAR002030,PAR002031,PAR002032,PAR002033,PAR002034,PAR002035,PAR002 036,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,PA R002063,PAR002064,PAR002065,PAR002066,PAR002067,PAR002068,PAR002069,PAR002070,PAR002071,PAR002072,PAR002073,PAR002074,PAR002075,PAR00 2076,PAR002077,PAR002078,PAR002079,PAR002080,PAR002081,PAR002082,PAR002083,PAR002084,PAR002085,PAR002086,PAR002087,PAR002088,PAR00208 9,PAR002090,PAR002091,PAR002092,PAR002093,PAR002094,PAR002095,PAR002096,PAR002097,PAR002098,PAR002099,PAR002100,PAR002101,PAR002102,P AR002103,PAR002104,PAR002105,PAR002106,PAR002107,PAR002108,PAR002109,PAR002110,PAR002111,PAR002112,PAR002113,PAR002114,PAR002115,PAR0 02116,PAR002117,PAR002118,PAR002119,PAR002120,PAR002121,PAR002122,PAR002123,PAR002124,PAR002125,PAR002126,PAR002127,PAR002128,PAR0021 29,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,PAR 002156,PAR002157,PAR002158,PAR002159,PAR002160,PAR002161,PAR002162,PAR002163,PAR002164,PAR002165,PAR002166,PAR002167,PAR002168,PAR002 169,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,PA R002196,PAR002197,PAR002198,PAR002199,PAR002200,PAR002201,PAR002202,PAR002203,PAR002204,PAR002205,PAR002206,PAR002207,PAR002208,PAR00 2209,PAR002210,PAR002211,PAR002212,PAR002213,PAR002214,PAR002215,PAR002216,PAR002217,PAR002218,PAR002219,PAR002220,PAR002221,PAR00222 2,PAR002223,PAR002224,PAR002225,PAR002226,PAR002227,PAR002228,PAR002229,PAR002230,PAR002231,PAR002232,PAR002233,PAR002234,PAR002235,P AR002236,PAR002237,PAR002238,PAR002239,PAR002240,PAR002241,PAR002242,PAR002243,PAR002244,PAR002245,PAR002246,PAR002247,PAR002248,PAR0 02249,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.
[Updated on: Tue, 01 August 2023 00:50] by Moderator Report message to a moderator
|
|
|
Re: How to Delimit the Large comma separated string - Oracle Procedure [message #687958 is a reply to message #687957] |
Mon, 31 July 2023 22:46 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following is a copy of what I posted on the OTN forum in response to the same question.
https://forums.oracle.com/ords/apexds/post/how-to-delimit-the-large-comma-separated-string-oracle-proc-8084
It works for me, as demonstrated below. So, either there is something different in your settings or my simulation of constructing and passing the variable is too different.
– version:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
– type and function:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TYPE ARRAY AS TABLE OF VARCHAR2 (4000);
2 /
Type created.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION PARSE_CSV
2 (p_clob CLOB,
3 p_separator VARCHAR2)
4 RETURN ARRAY
5 PIPELINED
6 AS
7 v_size NUMBER;
8 v_start_pos NUMBER := 1;
9 v_new_position NUMBER := 0;
10 v_line VARCHAR2 (4000);
11 x_clob CLOB := p_clob || TO_CLOB (p_separator);
12 BEGIN
13 v_size := DBMS_LOB.getlength (x_clob);
14 WHILE v_start_pos <= v_size
15 LOOP
16 v_new_position := NVL (INSTR (x_clob, p_separator, v_start_pos), 4000);
17 v_line := SUBSTR (x_clob, v_start_pos, v_new_position - v_start_pos);
18 v_start_pos := v_new_position + LENGTH (p_separator);
19 PIPE ROW (v_line);
20 END LOOP;
21 RETURN;
22 END PARSE_CSV;
23 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
– creation and population of variable:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_clob CLOB
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_clob CLOB := EMPTY_CLOB();
3 BEGIN
4 FOR i IN 1 .. 2247 LOOP
5 v_clob := v_clob || 'PAR' || LPAD (i + 14, 6, '0') || ',';
6 END LOOP;
7 :g_clob := RTRIM (v_clob, ',');
8 END;
9 /
PL/SQL procedure successfully completed.
– select statement passing variable to function:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (parse_csv (:g_clob, ',') )
2 /
COLUMN_VALUE
--------------------------------------------------------------------------------
PAR000015
PAR000016
PAR000017
PAR000018
PAR000019
PAR000020
PAR000021
PAR000022
PAR000023
PAR000024
PAR000025
– truncated to save space on forum
PAR002256
PAR002257
PAR002258
PAR002259
PAR002260
PAR002261
2247 rows selected.
|
|
|
Re: How to Delimit the Large comma separated string - Oracle Procedure [message #687961 is a reply to message #687958] |
Tue, 01 August 2023 09:05 |
|
born2achieve
Messages: 9 Registered: July 2012
|
Junior Member |
|
|
Thanks for the reply and here is the full version what i tried,
CREATE OR REPLACE TYPE PARRAY as table of CLOB;
CREATE OR REPLACE FUNCTION parse_csv(
i_str IN CLOB,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN PARRAY 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;
/
CREATE OR REPLACE PROCEDURE PARSE_CSV_TEST(SearchString IN CLOB, p_cursor OUT SYS_REFCURSOR) as
BEGIN
OPEN p_cursor FOR
select * from TABLE parse_csv(SearchString,','));
END;
/
then when i ran by supplying values wit the below block of code to test the procedure
DECLARE
-- Variable declarations
l_SEARCHSTRING CLOB;
l_T_CURSOR SYS_REFCURSOR;
BEGIN
-- Variable initializations
l_SEARCHSTRING :=
TO_CLOB (
'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');
-- Call
PARSE_CSV_TEST (SEARCHSTRING => l_SEARCHSTRING,
T_CURSOR => l_T_CURSOR);
-- Transaction control
COMMIT;
-- Output bind variables, do not modify
:2 := l_T_CURSOR;
END;
Quote:Note : i could past the full input values on this. i am passing 4000 comma separated values on the above mentioned format and getting the below error when i ran it.
Quote:ORA-06550: line 9, column 13: PLS-00172: string literal too long
please let me know what is the limitation of the comma separated values. this one worked for 2500 records when i tried. Any suggestions to solve this issue?
[Updated on: Tue, 01 August 2023 09:53] by Moderator Report message to a moderator
|
|
|
Re: How to Delimit the Large comma separated string - Oracle Procedure [message #687962 is a reply to message #687961] |
Tue, 01 August 2023 09:54 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
After making a bunch of corrections to your code that I marked with comments,
I was able to pass 4000 values of the length that you have shown with no errors.
So, either you are passing a greater length or passing it i a different way or
there is a different setting. Since it is obvious that you are not running what
you are posting and you are calling it differently, I will give you the
corrected code, so that you can just copy and paste it and test it AS IS, WITHOUT
ANY MODIFICATIONS. You need to post a copy and paste of that run, complete with
line numbers, any results, and any errors. If it does not work for you, then we
need to look at differences in settings. If it does run, then we need to look at
the length of the actual data or at how you are passing it from elsewhere. We
need to have you run this AS IS first, to eliminate problems with this code.
CREATE OR REPLACE TYPE PARRAY as table of CLOB;
/
SHOW ERRORS
CREATE OR REPLACE FUNCTION parse_csv(
i_str IN CLOB,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN PARRAY 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;
/
SHOW ERRORS
CREATE OR REPLACE PROCEDURE PARSE_CSV_TEST(SearchString IN CLOB, p_cursor OUT SYS_REFCURSOR) as
BEGIN
-- added a left parenthesis between table and parse:
OPEN p_cursor FOR
select * from TABLE (parse_csv(SearchString,','));
END;
/
SHOW ERRORS
-- added variable:
VARIABLE g_refcur REFCURSOR
DECLARE
-- Variable declarations
l_SEARCHSTRING CLOB;
l_T_CURSOR SYS_REFCURSOR;
BEGIN
-- Variable initializations
l_SEARCHSTRING :=
TO_CLOB (
'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');
-- replaced line above wit lines below
l_SEARCHSTRING := EMPTY_CLOB();
FOR i IN 1 .. 4000 LOOP
l_SEARCHSTRING := l_SEARCHSTRING || 'PAR' || LPAD (i + 14, 6, '0') || ',';
END LOOP;
-- Call
-- changed t_cursor below to p_cursor, it must match the name in the parse_csv_test procedure:
PARSE_CSV_TEST (SEARCHSTRING => l_SEARCHSTRING,
P_CURSOR => l_T_CURSOR);
-- Transaction control
COMMIT;
/* disabled this section
-- Output bind variables, do not modify
:2 := l_T_CURSOR;
*/
-- added line below
:g_refcur := l_T_CURSOR;
END;
/
-- added line to print results
PRINT g_refcur
|
|
|
|
Re: How to Delimit the Large comma separated string - Oracle Procedure [message #687964 is a reply to message #687963] |
Tue, 01 August 2023 11:11 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
mathguy,
I know that I replaced the single string literal with a constructed value that produced the value he was displaying.
I had already run it with the single string literal and supposedly so had he.
The issue appeared to be one of length, so I felt that I needed something to produce that longer length.
I have no idea nor was he able to post here how he actually gets that data. So, I was doing my best to simulate the
number of values between commas and overall length.
I was trying to start with just getting one legitimate post from him to show that the process after receipt of the
string was working. I just read the latest posts on the OTN thread and it looks like some others were trying to
do the same thing I was, trying to establish that the basic code without the long string and call from C# worked.
It is interesting to note that the code version over there was different and had errors as well.
That is why I was trying to get him to copy and paste, run and post. Otherwise, we are forever looking at bits and pieces
of code with errors and don't know what he actually ran.
This has been very frustrating and, in hindsight, I kind of wish I never got involved with this one.
I do appreciate you trying to clarify things and straighten things out.
Regards,
Barbara
|
|
|
Re: How to Delimit the Large comma separated string - Oracle Procedure [message #687965 is a reply to message #687964] |
Tue, 01 August 2023 11:46 |
|
born2achieve
Messages: 9 Registered: July 2012
|
Junior Member |
|
|
Thank you Barbara for the reply and i can see the only main change on your code is
-- replaced line above wit lines below
l_SEARCHSTRING := EMPTY_CLOB();
FOR i IN 1 .. 4000 LOOP
l_SEARCHSTRING := l_SEARCHSTRING || 'PAR' || LPAD (i + 14, 6, '0') || ',';
END LOOP;
lets go with your flow now, please generate the 4000 comma separated string using your above logic and copy it to notepad. I am using toad for oracle as IDE. so go to the schema browser and find this procedure and right click and hit the "Execute". it will ask you to choose the input. there copy the values you had in notepad and past it and it generares the PL/SQL block. then copy that and run it from another window and it will give the error that i posted.
Eventually, this long comma seperated string would comes from my C# code to procedure.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:44:46 CDT 2024
|