This discussion is archived
3 Replies Latest reply: Apr 1, 2013 1:12 AM by 690004 RSS

Query /Function Help

690004 Newbie
Currently Being Moderated
Hi All,

I have a requriement in which i have to pass the values as comma seprated in a single row

'0110001,0110101,0110201,01105,01117,01129,01729321,01729331,01729341,01735,01931,01934,01936,01938,01939,01942,01942R,01950,01985,02019880001,02019880901,02019880902,02022701001,02022701901,02029182001,02029182901,02031790001,02031790901,02035440001,02035602001,02038572001,02038572901,02040240001,02040250001,02040261001,02042351001,02043490001,02053381001,02054181901,02054550001,02054632001,02054633001,02054634001,02075894001,02076234001,02076234901,02079173001,02083670001,02083670002,02084442001,02088101001,02089080001,02089131001,02092660001,02093842001,02093842901,02093843001,02093843801,02093843901,02094670001,02094680001,02094870001,02095440001,02099310001,02099510001,02099510002,02099510004,02501700001,02507002001,02507308001,02512543005,02513260001,02515773001,02516097001,02516471001,02516471002,02518443001,02519590001,02519591001,02519620001,02519841002,02523020001,02523030001,02523910001,02524101001,02524101801,02524240001,02528220001,02531370001,02531380001,02531390001,02531400001,02532480001,02532481001,02535164001,02535165001,02535165901,02535221001,02535860001,02535860002,02535861001,02535861002,02535870001,02537100001,02538111001,02538211001,02538211901,02546810001,030004,030013,030014,030025,03009290001,03017703001,03017703901,03022621001,03022692001,03022900001,03022910001,03038700001,03040283001,03040301001,03042571001,03042571002,03042571003,03054211001,03054721001,03083081001,03083081801,03083880001,03085540001,03085592001,03088460001,03088470001,03091181001,03091191001,03091202001,03504091001,03505300001,03511571001,03519850001,03520020001,03527740001,03527750001,03527760001,03527770001,03530461001,03533750001,03533910001,03533920001,03533960001,03537990001,03537990801,04003200001,04003200002,04003210001,04003220001,0400322001,04003230001,0400323001,04004250002,0400432001,0400456001,04004570001,04006320001,0400698001,04009165001,04009166001,0400930001,0400952001,0400968001,0400970001,04009710001,0400974001,0400975001,04009942001,04010690001,0401095001,0401096001,0401098001,04011000001,04014120001,04017041001,04017071001,04017081001,04017130001,04017140001,04017191001,04017240001,04017662001,04018000001,04018010001,04019181001,04019330001,04019451001,04019510001,04021130001,04021431001,04021950001,04021950002,04021950003,04021950004,04021950006,04022390001,04022460001,04022460002,04022480001,04022630001,04023240001,04023313001,04023720001,04023740001,04023750001,04023760001,04023981001,04024120001,04027140001,04027440001,04027440002,04027960901,04029003601,04029003901,04029031901,04029041001,04029051901,04030140001,04030770001,04032361001,04032370001,04032500001,04032530001,04032530002,04032573001,04032590001,04032620001,04032630001,04032641001,04033750001,04035632001,04036180001,04036180002,04036191001,04036200001,04037600001,04037770001,04042561001,04043371001,04044210001,04044221001,04044250001,04045281001,04045540001,04046012001,04046321001,04046522001,04046580001,04046760001,04047630001,04047780001,04053120001,04053840001,04053957601,04054470001,04054871001,04054871002,04054940001,04054960001,04055140001,04055150001,04055244901,04055245001,04055245002,04055245003,04055245004,04055245005,04055245006,04055245007,04055245008,04055245009,04055245010,04055245011,04055245012,04055245013,04055245014,04055245015,04055245016,04055245017,04055245601,04055245602,04055245603,04055245604,04055245605,04055245606,04055245607,04055245608,04055245609,04055245610,04055245611,04055245612,04055245613,04055245614,04055245615,04055245616,04055245617,04055267901,04055268001,04055268601,04055520001,04064580001,04064680001,04064820001,04065071001,04065082001,04065232001,04065331001,04067460001,04068050001,04068180001,04068402001,04068440001,04068780001,04068900001,04075710001,04075940001,04076007601,04076008601,04077980001,04077983001,04079120001,04079420001,04083070001,04083730001,04084580001,04085043001,04085043002,04085071001,04085080001,04085080002,04085090002,04085121001,04085131001,04085212001,04085212002,04085222002'

and divided it to diferent rows


0110001
0110101
0110201
01105
01117


I have written a function for it.

but this is restricted only for 4000 charcters , i have to pass more than 4000 charcters for this funcions.





below is the function

create or replace TYPE VARCHAR_ARRAY AS VARRAY(1000000) OF VARCHAR2(1000);

create or replace FUNCTION in_list(p_list IN VARCHAR2)                              
RETURN VARCHAR_ARRAY                                                                           
PIPELINED AS l_text VARCHAR2(32767) := p_list||',';                                                       
l_idx NUMBER;                                                                                
BEGIN LOOP                                                                                
l_idx := INSTR(l_text,','); EXIT WHEN NVL(l_idx,0) = 0;                                                       
PIPE ROW(TRIM(SUBSTR(l_text,1, l_idx - 1))); l_text := SUBSTR(l_text, l_idx + 1); END LOOP;                              
RETURN;                                                                                     
END in_list;



can any one please help me in resolving this issue.

Thanks in Advance

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points