CODEWITHSUNDEEP
oracle-databaseplsqlcursor
Karthik
Karthik

Reputation: 145

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

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 ;

Upvotes: 0

Views: 296

Answers (3)

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

Reputation: 50017

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

:-)

Upvotes: 2

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 0

PKey
PKey

Reputation: 3841

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 ;

Upvotes: 0

Related Questions