Oracle PL/SQL to Loop through a rowset

Posted on 2014-08-21
Last Modified: 2014-08-22
I have the following rowset from a cursor:

Work_Item      Letter_Name
3            Letter2
3            4133-a
3            TEST1
3            LETTER1
3            letter1
3            TEST3
5            lett-2

I need to iterate through the rowset and build a deimited string of letter names based on conditions.

Initialize a string array named arrLetterNameList
Initialize a variable named intRowCounter to 0.

Starting with the first row
Read the Work_Item value into a variable named intWorkItemCurr
Read the Letter_Name value into a variable named strLetterNameCurr
Read the Letter_Name value into the array as arrLetterNameList(intRowCounter)
Set intRowCounter =  intRowCounter +1

Read the next row
Read the Work_Item value into a variable named intWorkItemNext
Read the Letter_Name value into a variable named strLetterNameNext

If  (intWorkItemNext  = intWorkItemCurr and  intRowCounter <=3)
      Read the Letter_Name value into the array as arrLetterNameList(intRowCounter)
      Read the next row
End If

In essence, loop through the result set, and build a concatenated string of values from the Letter_Name column.
Build a new concatenated string each time the work item changes or three rows have been read for the same work item.

The strings would be "Letter2; 4133-a; TEST1" and "lett-2"

I am not sure how to loop through a rowset and act on a condition.

I am transitioning from C# and MS SQL Server to Oracle and PL/SQL.

Question by:Dovberman
    LVL 16

    Assisted Solution

    by:Swadhin Ray
    Are you looking for something like below:

    create table my_samp_tab
    (Work_Item  number ,
     Letter_Name varchar2(100));
     insert into my_samp_tab values(3, 'Letter2');
      insert into my_samp_tab values(3, '4133-a');
       insert into my_samp_tab values(3, 'TEST1');
        insert into my_samp_tab values(3, 'LETTER1');
    	 insert into my_samp_tab values(3, 'letter1');
    	  insert into my_samp_tab values(3, 'TEST3');
    	   insert into my_samp_tab values(5, 'lett-2');
                  ,';') within
     ORDER BY WORK_ITEM) AS column_value , work_item
      FROM my_samp_tab
    group by work_item ;

    Open in new window


    Author Comment

    I believe that the sql statement you suggest  may be what I am looking for to build the delimited string for each work item. This would be more straightforward than a conditional loop.

    I will try it tomorrow and give you feedback.

    LVL 31

    Accepted Solution

    The problem with slobaray's solution, as far as I can tell, is that it doesn't take into consideration the criteria for not adding values after the rowcount reaches 3. The following should do what you need -

    select distinct string from
    (select work_item, rn, listagg(letter_name,';') within group (order by work_item, rn) over (partition by work_item) string from
     (select work_item, letter_name, row_number() over (partition by work_item order by rowid) rn from yourcursor)
     where rn <= 3) order by string;

    Just note that this works using rowid for ordering but, without seeing the cursor, we can't tell if anything is generating a specific order. Inserts and deletes to the table could produce rowids that are not in sequence, which could change the contents of the string.

    Author Comment

    Yes, I noticed that the rule to limit rows to a max of three was not addressed. I will test both suggestions.
    LVL 34

    Assisted Solution

    For best performance from Oracle, you should try to solve the business problem with a SQL "select ..." statement if possible.  And, as others have suggested here, these can include multi-level queries (select ... from (select ...)) but you do have to be careful with these.  If the inner "select..." returns a large number of rows, performance can be poor.

    If you can't get the job done (at all, or for some reason not efficiently) in a "select..." statement, you do always have the option of using a PL\SQL procedure (or function) that can include cursor loops, conditional logic, etc.

    Author Closing Comment

    The project has taken a different direction.

    Thanks for your help.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now