Home » SQL & PL/SQL » SQL & PL/SQL » Extracting values from comma separated string and loop through them (Oracle 10g)
Extracting values from comma separated string and loop through them [message #398449] Thu, 16 April 2009 05:31 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
There is a function that will be returning a varchar2 value with error_ids concatenated with comma like (32,45,12,4);
Now I have to write a insert statement in a loop for all these values.
INSERT INTO ams_upload_error_log
                           (flag, line_no, file_id, application_no, error_id
                           )
                    VALUES ('P', v_tot_rec, p_file_id, TRIM (cur_rec_ams.appl_no), v_error_id
                           );

Here the value in v_error_id is seperated by comma.
So I want to put this insert script inside a loop. This loop should go 4 times for each value in v_error_id to be inserted in error_id.

Please tell me a way to do this.
I have looked into instr function but thet would be too lengthy to find position everytime.

Please guide me on this as how to do this.

Reards,
Mahi

[Updated on: Thu, 16 April 2009 05:36] by Moderator

Report message to a moderator

Re: Extracting values from comma separated string and loop through them [message #398453 is a reply to message #398449] Thu, 16 April 2009 05:35 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use substr and instr in a loop. (I don't think it is too lengthy)

But why can't you return an array instead of the comma separated string?

By
Vamsi

[Updated on: Thu, 16 April 2009 05:48]

Report message to a moderator

Re: Extracting values from comma separated string and loop through them [message #398516 is a reply to message #398449] Thu, 16 April 2009 08:59 Go to previous messageGo to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member
here is how to do what you want
DECLARE

lv_binary BINARY_INTEGER;
lv_array dbms_utility.uncl_array;

BEGIN

dbms_utility.comma_to_table(v_error_id, lv_binary, lv_array);

FORALL i IN 1 .. lv_array.COUNT
INSERT INTO ams_upload_error_log
(flag,
line_no,
file_id,
application_no,
error_id)
VALUES
('P',
v_tot_rec,
p_file_id,
TRIM(cur_rec_ams.appl_no),
lv_array(i));

END;

and here cur_rec_ams.appl_no, i have no idea what this is you are trying to trim.
Re: Extracting values from comma separated string and loop through them [message #398517 is a reply to message #398449] Thu, 16 April 2009 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
DECLARE 
  lv_binary  BINARY_INTEGER; 
   lv_array   dbms_utility.uncl_array; 
BEGIN 
  dbms_utility.Comma_to_table(v_error_id,lv_binary,lv_array); 
   
  FORALL i IN 1..lv_array.COUNT 
    INSERT INTO ams_upload_error_log 
               (flag, 
                line_no, 
                file_id, 
                application_no, 
                error_id) 
    VALUES     ('P', 
                v_tot_rec, 
                p_file_id, 
                Trim(cur_rec_ams.appl_no), 
                Lv_array(i)); 
END;


In a more readable format.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Extracting values from comma separated string and loop through them [message #398545 is a reply to message #398516] Thu, 16 April 2009 10:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That won't work.
Comma_to_table sounds like just what you're looking for, but it only works on varchar2 strings that all meet the requirements for valid Oracle names.

One of these requirements is no leading numbers.

Try this example, based on the OPs requirements:
DECLARE 
   v_string   varchar2(1000);
   v_len      number;
   v_array   dbms_utility.uncl_array; 
BEGIN 
  v_string := '32,45,12,4';
  dbms_utility.Comma_to_table(v_string,v_len,v_array); 
   
  for i in v_array.first .. v_array.last loop
    dbms_output.put_line(v_array(i));
  end loop;
END;
/
Re: Extracting values from comma separated string and loop through them [message #398575 is a reply to message #398545] Thu, 16 April 2009 15:02 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member

http://tkyte.blogspot.com/2006/06/varying-in-lists.html


Re: Extracting values from comma separated string and loop through them [message #398591 is a reply to message #398449] Thu, 16 April 2009 20:13 Go to previous messageGo to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member
DECLARE 
   v_string   varchar2(1000);
   v_len      number;
   v_array   dbms_utility.uncl_array; 
BEGIN 
  v_string := '32,45,12,4';
  v_string := '"'||replace(v_string, ',', '","')||'"';
  
  dbms_utility.Comma_to_table(v_string,v_len,v_array); 
   
  for i in 1 .. v_array.COUNT
  loop
    dbms_output.put_line(REPLACE(v_array(i), '"', ''));
  end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
Re: Extracting values from comma separated string and loop through them [message #398613 is a reply to message #398591] Thu, 16 April 2009 22:59 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks to all of you for looking into my question. I am able to do the insert by the utility dbms_utility.Comma_to_table(v_string,v_len,v_array) .

This is so simple too.

Thanks again.

Regards,
Mahi
Previous Topic: Horizontal to Vertical Transpose.
Next Topic: ORA-06508: problem
Goto Forum:
  


Current Time: Fri Dec 09 17:40:51 CST 2016

Total time taken to generate the page: 0.35760 seconds