Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Unanswered: 9i, Loop over delimited list

    I am trying to figure out how to send a variable into a stored procedure and then loop over that variable (a comma delimited list) to perform an insert for each item in the list.

    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use a FOR loop
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2006
    Posts
    2
    I was planning on it, i'm just trying to figure out tell the for loop to loop over the items in the list, do i need to convert to an array, is there some function to count the items in a list so i can specify the number of times to loop, and then what is the notation to specify an item in that array or collection when i'm looping?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'd loop looking for a comma.
    After finding no more commas, the loop terminates.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Well, to count the number of items you want to iterate, you would use something like .. length( 'csv,string' ) - length( replace( 'csv,string' , ',' ) ) .. that is, the length of the string minus the length of the string with the delimited char stripped would give you the number of items to loop.

    Anyways, I would suggest you look at this website and use a similar method, avoiding row-by-row processing and inserting.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    check out DBMS_UTILITY.COMMA_TO_TABLE in your manual.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    There is also a very nice str2tbl pipelined function on asktom. The advantage of using this is that you can use it in normal sql select statements.

    Alan

  8. #8
    Join Date
    Mar 2004
    Posts
    370
    1. Parse the CSV into discreet object into a Pl/SQL table.
    2. do a FORALL insert into that is ultimately quick and clean on those objects.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •