PLSQL: Fetching cursor rowcount and add to an array based on count

Question

I have a cursor which fetches records from table based on filename (filenames are passed from array). now if filename is present more than once in table i need to add the filename to duparray such many number of times.

For example, if test2.txt is present 2 times and test3.txt is present 3 times, i need to have duparray as {test2.txt,test2.txt,test3.txt,test3.txt,test3.txt}

But as per below code, duparray is coming as {test2.txt,test3.txt,test3.txt} since i am having ROWCOUNT>1 check.

If that check is not there, filename which is present single time in table also gets added to it. Please advise where should i correct it.

CURSOR duplicateData IS
   SELECT file_name from tablename where file_name=p_filearray(i)           
   dupRow duplicateData%rowtype;

Inside the procedure:

OPEN duplicateData ;
   loop
   fetch duplicateData INTO dupRow;
   EXIT WHEN duplicateData %NOTFOUND;
      IF duplicateData %ROWCOUNT >1
      THEN
      p_duparray.EXTEND;
      p_duparray(p_duparray.LAST):=dupRow.file_name;
      END IF;
   end loop;
CLOSE duplicateData ;

Bob Jarvis - Слава Україні · Accepted Answer · 2016-09-17 12:44:04Z

2

Bob's First Law Of Database Programming states:

NEVER USE A LOOP TO DO A SELECT'S JOB

In this case you can use

DECLARE
  FILENAME_COL_TYPE AS TABLE OF TABLENAME.FILENAME%TYPE INDEX BY PLS_INTEGER;

  colFile_names  ROW_COL_TYPE;
BEGIN
  SELECT FILE_NAME
    BULK COLLECT INTO colFile_names
    FROM TABLENAME
    ORDER BY FILE_NAME;
END;

This doesn't address the issue of the desired filenames already being in a collection, but presumably that collection of filenames was derived from a SELECT statement, so the criteria for choosing the appropriate filenames can be included in the above.

Loops are bad. NO LOOPS! Never! (What, never?) No, never! (Never?) Well, hardly ever...

:-)

edited Sep 17, 2016 at 12:44

answered Sep 16, 2016 at 20:12

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PKey · Accepted Answer · 2016-09-16 19:32:37Z

0

I would suggest to collect into cursor file_name and number of occurances (cnt)

CURSOR duplicateData IS
select file_name,count(*) cnt from tablename where file_name=p_filearray(i)
group by file_name; 

dupRow duplicateData%rowtype;
i number:=0;

An then use for loop to fill the array ...

OPEN duplicateData ;
   loop
   fetch duplicateData INTO dupRow;
   EXIT WHEN duplicateData %NOTFOUND;
for i in 1..dupRow.cnt loop
      p_duparray.EXTEND;
      p_duparray(p_duparray.LAST):=dupRow.file_name;
end loop;
CLOSE duplicateData ;

answered Sep 16, 2016 at 19:32

PKey

3,8712 gold badges21 silver badges45 bronze badges

Comments

William Robertson · Accepted Answer · 2016-09-17 09:25:50Z

Perhaps I'm missing something, but it looks as though you do nothing for the first loop iteration due to the IF duplicateData%ROWCOUNT > 1, and this is why you're losing the first value.

I'd automatically refactor the open-fetch-exit-end-close loop into the simple form:

for r in (
    select file_name from tablename where file_name=p_filearray(i)           
)
loop
    p_duparray.extend;
    p_duparray(p_duparray.last) := r.file_name;
end loop;

Although as Bob Jarvis mentioned you don't even need a loop just to fetch cursor results into an array, when you can just bulk collect it.

Depending on what you need the array for, it might be possible to make it an associative array of counts indexed by the filename. That is, store each filename only once and maintain the count for each one, so that filecounts('test3.txt') = 3, rather than storing test3.txt three times.

Collectives™ on Stack Overflow

PLSQL: Fetching cursor rowcount and add to an array based on count

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related