Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL paste and insert problems
Yusuf,
Try this routine:
declare
start_pos number;
end_pos number;
cursor c is select * from temp;
begin
for cursor_data in c loop
start_pos :=1; end_pos := instr(cursor_data.description, ' ', start_pos); while end_pos !=0 loop insert into temp2 values(cursor_data.item_no, substr(cursor_data.description,start_pos, end_pos - start_pos)); start_pos := end_pos +1; end_pos := instr(cursor_data.description, ' ', start_pos); end loop;
Hope this helps.
Regards,
Don
Yusuf wrote in message <3494B54A.47090017_at_followme.com>...
>hi everybody,
>
>here is my PL/SQL problems...
>
>I have two tables
>
>the first table
>table temp has 2 columns (item_no number, description varchar2 (100))
> item_no description
> 102 ABC AD ASDD EDS AD
> 104 AB DCS CD XSDF
> ... ...............................
> ... ... .............................
>the second table temp1 has 2 columns ( item_no number, descriptio
>varchar (8) ).
>I want to insert temp value in such a way by reading item_no and each
>characters
>and insert into temp1 i.e for every item_no cut and insert multiple
>values as descriped.
>
>temp1 table should be
>
>item_no description
>102 ABC
>102 AD
>102 EDS
>etc. etc.
>
>hints ---I have been trying to deal with this problems
>use cursor loop, substr, instr
>or
>any suggestion or alternatives
>
>Thanks
>
>Mohamed Yusuf
>
>
Received on Tue Dec 16 1997 - 00:00:00 CST