Hi,
I am trying to mail the interactive report as excel attachment using pl/sql procedure.
I am using this procedure
create or replace PROCEDURE SEND_REPORT_MAIL(
P_APP_ID IN NUMBER,
P_APP_PAGE_ID IN NUMBER,
P_STATIC_ID IN VARCHAR2,
P_WORKSPACE IN VARCHAR2
)
IS
l_export apex_data_export.t_export;
l_mail_id NUMBER;
l_region_id NUMBER;
l_session_id NUMBER;
l_to VARCHAR2(500);
l_from VARCHAR2(500);
l_cc VARCHAR2(500);
l_subject VARCHAR2(500);
l_body VARCHAR2(2000);
l_sgid VARCHAR2(2000);
BEGIN
l_sgid := apex_util.find_security_group_id(p_workspace);
IF l_sgid IS NULL THEN
RAISE_APPLICATION_ERROR(-20010, 'Workspace "' || p_workspace || '" not found.');
END IF;
apex_util.set_security_group_id(l_sgid);
-- Create session
apex_session.create_session(
p_app_id => P_APP_ID,
p_page_id => P_APP_PAGE_ID,
p_username => 'ADMIN'
);
for i in (
select COLUMN_NAME COL_NAME,COLUMN_VALUE COL_VALUE ,column_data_type col_type – USING THIS I'M FETCHING ------THE PAGE ITEM VALUES
from REPORTDETAIL_TABLE where rep_id= (select rep_id from REPORT_TABLE where application_id = p_app_id and page_id = p_app_page_id)
)
loop
if i.col_type = 'DATE' then
apex_util.set_session_state(i.COL_NAME, TRUNC(resolve_date_token(i.COL_VALUE)));
else
apex_util.set_session_state(i.COL_NAME,i.COL_VALUE);
end if;
INSERT INTO DEBUG_TABLE VALUES (apex_custom_auth.get_session_id,SYSTIMESTAMP,I.COL_NAME,I.COL_VALUE);-→ FOR DEBUGGING THE VALUES
end loop;
commit;
-- Get region ID
SELECT region_id INTO l_region_id
FROM apex_application_page_regions
WHERE application_id = P_APP_ID
AND page_id = P_APP_PAGE_ID
AND upper(static_id) = upper(P_STATIC_ID);
SELECT EMAIL_FROM,EMAIL_TO,EMAIL_CC,EMAIL_BODY,EMAIL_SUBJECT INTO l_from,l_to,l_cc,l_body,l_subject FROM REPORT_TABLE WHERE APPLICATION_ID = P_APP_ID AND PAGE_ID = P_APP_PAGE_ID;
-- Export
l_export := apex_region.export_data(
p_page_id => P_APP_PAGE_ID,
p_region_id => l_region_id,
p_format => apex_data_export.c_format_xlsx
);
-- Email
l_mail_id := apex_mail.send(
p_to => l_to,
p_from => l_from,
p_cc => l_cc,
p_subj => l_subject,
p_body => l_body
);
apex_mail.add_attachment(
p_mail_id => l_mail_id,
p_attachment => l_export.content_blob,
p_filename => 'Report.xlsx',
p_mime_type => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
);
apex_mail.push_queue;
COMMIT;
--apex_session.detach;
apex_session.delete_session;
END;
/
The problem is, I'm getting the mail with attachment but the excel has correct column headers with no data.
Rarely the datas are fetching correctly,
Anyone please help me which point i'm missing.