Home » SQL & PL/SQL » SQL & PL/SQL » splliting the string
splliting the string [message #192255] Mon, 11 September 2006 10:13 Go to next message
anil.rdbms
Messages: 11
Registered: August 2006
Location: NEW YORK
Junior Member

Hi i have problem with parameters to procedure

here i have a string parameter and i need to spit and insert in the

Hi i have problem with parameters to procedure

here i have a string parameter and i need to spit and insert in the table from the parameter

i have in parameter like this

'123,456,789,456' and i need to split the string and pass 123 as the first value to

the inser statement

then 456 like this

i have written like this



L_N:= LENGTH(IN_po); length of the parameter
L_RETURN:= IN_LOANERS;
for i in 1..l_n loop
EXIT WHEN L_RETURN IS NULL;
P_RETURN:= LPAD(L_RETURN,INSTR(L_RETURN, ',' ,1)-1);
INSERT INTO po_head(po_no)
VALUES(TO_CHAR(P_RETURN));
M_RETURN:= TO_CHAR(REPLACE(L_RETURN, LPAD(L_RETURN,INStr(L_RETURN, ',' ,1)-1),NULL));
M_RETURN:= LTRIM(M_RETURN,',');
L_RETURN:= TO_CHAR(M_RETURN);
end loop;
END;

here i am facing two problems when it comes to last values it is returning null and the last value is not getting inserted

like LPAD('456',INSTR('456', ',' ,1)-1);


but if i have LPAD('256,456',INSTR('256,456', ',' ,1)-1); it retruns 256

so when it comes to last values it is giving problem

and also how to repeat the loop the count of commas times in the string .

if any one knows please help me


thanks
anil

[Updated on: Mon, 11 September 2006 10:14]

Report message to a moderator

Re: splliting the string [message #192259 is a reply to message #192255] Mon, 11 September 2006 10:27 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
try dbms_utility.comma_to_table ( starting from 9.2 if I remember correct)
Re: splliting the string [message #192278 is a reply to message #192259] Mon, 11 September 2006 12:12 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
markmal wrote on Mon, 11 September 2006 10:27

try dbms_utility.comma_to_table ( starting from 9.2 if I remember correct)

It was there in 8i surely, BUT for dbms_utility.comma_to_table to work the elements in the comma-separated list must satisfy Oracle table naming conventions. The string in this case ('123,456,789,456') does not conform to this rule, so the comma_to_table method will give an error. Look here for details.

This solution from the forum archives should be of help: Link
Re: splliting the string [message #192413 is a reply to message #192255] Tue, 12 September 2006 04:19 Go to previous message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Try this, might it will work.

declare
L_N number=0;
L_RETURN varchar2(100):=null;
p_RETURN varchar2(100):=null;
begin
L_RETURN:= IN_po;
while l_return is not null
loop
L_N:= LENGTH(l_return);
P_RETURN:= substr(l_return,1,decode(instr(l_return,','),0,L_N+1,instr(l_return,','))-1);
INSERT INTO po_head(po_no) VALUES( TO_CHAR(P_RETURN) );
l_return=ltrim(ltrim(l_return,P_RETURN),',');
end loop;
END;


Sandy
Previous Topic: Forward Declaration of Procedures and Functions in Packages
Next Topic: ALTER TABLE stmt for modified CHECK constraint for Oralce 9.x
Goto Forum:
  


Current Time: Sat Dec 03 20:09:12 CST 2016

Total time taken to generate the page: 0.09471 seconds