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


i have written below procedure in want return output of variable value brand_name.brand_name. getting correct output. want return in proper format. example right getting output without header 'brand_name'

,fnc,lidl 

but want return output header 'brand_name' as:

brand_name: fnc,lidl 

here 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 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  select br.name brand_name v_brand_name 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 kd.kpi_def_id = v_kpi_def_id;  begin select kpi_def_id v_kpi_def_id kpi_logs kpi_log_id = in_kpi_log_id;  select kt.kpi_type_id v_kpi_type_id rator_monitoring_configuration.kpi_definition kd join rator_monitoring_configuration.kpi_type kt on kd.kpi_type = kt.kpi_type_id kd.kpi_def_id = v_kpi_def_id;  select name v_kpi_type rator_monitoring_configuration.kpi_definition kd join rator_monitoring_configuration.kpi_type kt on kd.kpi_type = kt.kpi_type_id kd.kpi_def_id = v_kpi_def_id;  select kpi_name v_kpi_name rator_monitoring_configuration.kpi_definition 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);  brand_name in brand_names_cur loop if v_first_record   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; 

it has way.

v_first_record := true;  brand_name in brand_names_cur loop if v_first_record   body := body || 'brand_name : '|| brand_name.brand_name;   v_first_record := false; else   body := body || ',' || brand_name.brand_name; end if; end loop;   

edit: overwrite variable here. add append it...

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

Comments

Popular posts from this blog

java - Date formats difference between yyyy-MM-dd'T'HH:mm:ss and yyyy-MM-dd'T'HH:mm:ssXXX -

c# - Get rid of xmlns attribute when adding node to existing xml -