Deleting a saved report from APEX Interactive Report

This post describes the way to delete a saved report from APEX Interactive Report.

I have an 10+ year-old APEX application that runs on Oracle Database 11g and APEX 5. Don't have time/motivation to upgrade it to latest Oracle, as that a-decade old version works just fine.

On <2024-05-20 Mon> I was asked to delete some unused Public reports from Interactive Report on a page. To my surprise there were no such a button from APEX development interface, the only way to delete is to use REMOVE_SAVED_REPORT Procedure.

The interface of this procedure is quite simple:

APEX_INSTANCE_ADMIN.REMOVE_SAVED_REPORT(
    p_application_id     IN NUMBER,
    p_report_id          IN NUMBER);

I found an alias of the saved report, which was 408036 and ran this under SYSDBA user:

BEGIN
  apex_instance_admin.remove_saved_report(111, 408036);
END;
/

COMMIT;

The procedure succeeded, but the report was still there. Shoot. Then I started digging into the hole.

How do I find all the saved report in APEX? Use APEX_APPLICATION_PAGE_IR_RPT view, check its columns, find clues.

Keeping code here:

SELECT view_name FROM user_views;

SELECT username FROM dba_users;

SELECT view_name FROM all_views;

DESCRIBE all_views;

SELECT owner, view_name FROM all_views WHERE owner='APEX_050000';

DESCRIBE APEX_050000.APEX_APPLICATION_PAGE_IR_RPT;

I found that the report has two more ids, and that alias that looks like an id is NOT the report_id that REMOVE_SAVED_REPORT procedure expects.

The correct way is to first find the report_id using alias and use it to delete the report.

-- 1. find the report_id
SELECT report_id, report_name, report_alias
FROM apex_050000.apex_application_page_ir_rpt
WHERE application_id=111;

-- 2. remove saved report, from SYS user
BEGIN
  apex_instance_admin.remove_saved_report(111, 40803570679159701);
END;
/

Also, TIL that saved reports have these types:

• PUBLIC: public reports, obvisously
• PRIMARY_DEFAULT: the default report used when user first opens a page with interactive report
• PRIVATE: private reports saved by users, visible only to users
• SESSION: non-saved modifications of existing reports

SELECT report_type, count(*)
FROM apex_050000.apex_application_page_ir_rpt
GROUP BY report_type;

REPORT_TYPE           COUNT(*)
__________________ ___________
SESSION                     43
PUBLIC                      13
PRIMARY_DEFAULT              6
PRIVATE                     39