Making some changes to a function in Oracle

Question

I have a function which is not working as expected. How can I modify to get the desired output?

CREATE OR REPLACE FUNCTION f_get_all_programs_test(
 pidm in number,aidy in varchar2
) RETURN VARCHAR2
IS
  TERM NUMBER;
 result  VARCHAR2(300);
   CURSOR C
   IS
      SELECT stvterm_code  FROM stvterm
                  WHERE stvterm_fa_proc_yr = aidy;
      
BEGIN
Open C;
loop
 fetch C into TERM;
exit when C%NOTFOUND;
SELECT  LISTAGG(rzkutil.f_get_program (pidm,TERM, aidy), ',') WITHIN GROUP (ORDER BY stvterm.stvterm_code)
  INTO    result
  FROM    stvterm stvterm
  WHERE   stvterm.stvterm_fa_proc_yr = aidy;
end loop;

RETURN result;
END;

Cursor select Query returns multiple rows. For each row, the function rzkutil.f_get_program must run and separate them by comma. The existing code is not working as expected. instead the output is repeating multiple times.

Example:

select rzkutil.f_get_program(12098136,'',2122) from dual -- AAS-PG2-AOTO (result) select f_get_all_programs_test(12098136,'2122') from dual --AAS-PG2-AOTO,AAS-PG2-AOTO,AAS-PG2-AOTO,AAS-PG2-AOTO,AAS-PG2-AOTO,AAS-PG2-AOTO (result, instead it should return AAS-PG2-AOTO)

Answer 1

As values you're aggregating come from the cursor, it means that its (cursor's) query returns duplicates. To avoid them, use the DISTINCT keyword:

CURSOR C
IS
  SELECT DISTINCT stvterm_code  
  FROM stvterm
  WHERE stvterm_fa_proc_yr = aidy;

---

Though, I believe that you don't need such a complicated (and potentially slow, because of loop processing) code. I don't have any test data to try it, but - see if this helps:

CREATE OR REPLACE FUNCTION f_get_all_programs_test (pidm  IN NUMBER,
                                                    aidy  IN VARCHAR2)
   RETURN VARCHAR2
IS
   result  VARCHAR2 (300);
BEGIN
   SELECT LISTAGG (term, ',') WITHIN GROUP (ORDER BY stvterm_code)
     INTO result
     FROM (SELECT DISTINCT
                  rzkutil.f_get_program (pidm, stvterm_code, aidy) AS term,
                  stvterm_code
             FROM stvterm
            WHERE stvterm_fa_proc_yr = aidy);

   RETURN result;
END;