For loop does not return result in correct format in oracle procedure

Question

I have written below procedure in which i want to return the output of variable value brand_name.BRAND_NAME. I am getting the correct output. But i want to return it in proper format. For example right now i am getting the output without header 'BRAND_NAME' as

,FNC,LIDL

But i want to return the output with header 'BRAND_NAME' as:

BRAND_NAME: FNC,LIDL

Here is my stored procedure:

FUNCTION BUILD_ALERT_EMAIL_BODY
(
  IN_ALERT_LOGS_TIMESTAMP IN TIMESTAMP
, IN_ALERT_LOGS_LOG_DESC IN VARCHAR2
, IN_KPI_LOG_ID IN NUMBER
) RETURN VARCHAR2 AS
BODY VARCHAR2(4000) := '';
V_KPI_DEF_ID NUMBER := '';
V_KPI_TYPE VARCHAR2(100) := '';
V_KPI_NAME VARCHAR2(100) := '';
V_BRAND_NAME VARCHAR2(100) := '';
V_KPI_TYPE_ID NUMBER := '';
V_FIRST_RECORD Boolean := false;

CURSOR brand_names_cur 
IS
Select BR.NAME AS BRAND_NAME INTO V_BRAND_NAME FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION_BRAND KDB ON KD.KPI_DEF_ID = KDB.KPI_DEF_ID JOIN
RATOR_MONITORING_CONFIGURATION.BRAND BR ON KDB.BRAND_ID = BR.BRAND_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;

BEGIN
Select KPI_DEF_ID INTO V_KPI_DEF_ID FROM KPI_LOGS WHERE KPI_LOG_ID = IN_KPI_LOG_ID;

Select KT.KPI_TYPE_ID INTO V_KPI_TYPE_ID FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.KPI_TYPE KT ON KD.KPI_TYPE = KT.KPI_TYPE_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;

Select NAME INTO V_KPI_TYPE FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD JOIN RATOR_MONITORING_CONFIGURATION.KPI_TYPE KT ON KD.KPI_TYPE = KT.KPI_TYPE_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;

Select KPI_NAME INTO V_KPI_NAME FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION WHERE KPI_DEF_ID = V_KPI_DEF_ID;

BODY := BODY || 'KPI_TYPE : ' || V_KPI_TYPE || Chr(13) || Chr(10);
BODY := BODY || 'KPI_NAME : ' || V_KPI_NAME || Chr(13) || Chr(10);

FOR brand_name IN brand_names_cur
LOOP
if v_first_record then
  body := 'BRAND_NAME : '|| brand_name.BRAND_NAME;
  v_first_record := true;
else
  body := body || ',' || brand_name.BRAND_NAME;
  v_first_record := false;
end if;
END LOOP;    

    RETURN BODY;
END BUILD_ALERT_EMAIL_BODY;

Answer 1

It has to be this way.

v_first_record := true;

FOR brand_name IN brand_names_cur
LOOP
if v_first_record then
  body := body || 'BRAND_NAME : '|| brand_name.BRAND_NAME;
  v_first_record := false;
else
  body := body || ',' || brand_name.BRAND_NAME;
end if;
END LOOP;  

EDIT: You overwrite the variable here. So add append to it...

body := body || 'BRAND_NAME : '|| brand_name.BRAND_NAME;