6 Replies Latest reply on Jul 13, 2018 11:44 AM by Kirsten

    apex_ir.get_report in background job

    Kirsten

      Hi all,

       

      I'm looking for a possibility to get the sql statement of different saved reports as dbms_job in the background.

      The report alias is known. I can get region and report ID from apex_application_page_ir_rpt. But nevertheless I get the error "ORA-20987: APEX - Interactive report region does not exist in application".

      If I run the same code in APEX the correct SQL statement is returned.

       

      DECLARE

         v_report_sql   VARCHAR2 (32767);

         v_report       apex_ir.t_report;

         v_region_id    NUMBER;

         v_report_id    NUMBER;

         v_page_id      NUMBER;

         v_app_id       NUMBER;

      BEGIN

         SELECT application_id,

                page_id,

                region_id,

                report_id

           INTO v_app_id,

                v_page_id,

                v_region_id,

                v_report_id

           FROM apex_application_page_ir_rpt

          WHERE report_alias LIKE '339864';

       

          v_report :=

               apex_ir.get_report (p_page_id     => v_page_id,

                                   p_region_id   => v_region_id,

                                   p_report_id   => v_report_id);

       

          v_report_sql := v_report.sql_query;                              

          DBMS_OUTPUT.put_line (v_report_sql);

       

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (SQLERRM);

      END;

       

      Does somebody know a workaround for this?

       

      Thank's,

      Kirsten