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
Post a Comment