Oracle PL/SQL to Loop through a rowset

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)
Else  
      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.

Thanks,
DovbermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swadhin RaySenior Technical Engineer Commented:
Are you looking for something like below:

create table my_samp_tab
(Work_Item  number ,
 Letter_Name varchar2(100));
 
 begin 
 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');
	   commit;
	   end;
	   
	   
SQL> SELECT listagg(LETTER_NAME
              ,';') within
 GROUP(
 ORDER BY WORK_ITEM) AS column_value , work_item
  FROM my_samp_tab
group by work_item ;

Open in new window

0
DovbermanAuthor Commented:
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.

Thanks,
0
awking00Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DovbermanAuthor Commented:
Yes, I noticed that the rule to limit rows to a max of three was not addressed. I will test both suggestions.
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
DovbermanAuthor Commented:
The project has taken a different direction.

Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.