[Last Call] Learn how to a build a cloud-first strategyRegister Now


Oracle PL/SQL to Loop through a rowset

Posted on 2014-08-21
Medium Priority
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
Swadhin Ray earned 800 total points
ID: 40277189
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

ID: 40277301
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 32

Accepted Solution

awking00 earned 800 total points
ID: 40277349
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.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

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

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 400 total points
ID: 40278713
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

ID: 40279750
The project has taken a different direction.

Thanks for your help.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

826 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