Wednesday 15 May 2013

Getting Interactive Report Query

I know this is nothing new but I had a hard time to find out how to get the SQL of the currently viewed Interactive Report. In one of my projects I needed the exact query including the filtered values and sorting  in order to save the data into a PL/SQL collection and process it further. I tried using the existing IR Application Views but those do not provide all the information I needed. I stumbled upon this documentation page

http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_ir.htm#BABEFDJE

but was confused by the statement for getting the IR Query:

DECLARE
   l_report  apex_ir.t_report;
   l_query   varchar2(32767);
BEGIN     
    l_report := APEX_IR.GET_REPORT (
                    p_page_id   => 1,
                    p_region_id => 2505704029884282,
                    p_report_id => 880629800374638220);
    l_query := l_report.sql_query;
    for i in 1..l_report.binds.count
    loop
        dbms_output.put_line(i||'. '||
                             l_report.binds(i).name||
                             '='||l_report.binds(i).value);
    end loop;
END; 

If you run this statement, you will receive a concatenated string of binds used in for the filtering and the corresponding values and not the actual query (it is just not printed out). In addition to that, you need to combine this statement with the one for getting the last viewed report id:

DECLARE
    l_report_id number;
BEGIN     
    l_report_id := APEX_IR.GET_LAST_VIEWED_REPORT_ID (
        p_page_id   => 1,
        p_region_id => 2505704029884282);
END;

After talking to Patrick Wolf I realized that this statement delivers almost everything you need in order to get the complete query. I combined the two statements and created a function which you can use to get a query for any of your interactive reports including replaced binds. The function code is:

CREATE OR REPLACE FUNCTION get_report_sql (
   p_app_id     IN   NUMBER,
   p_page_id    IN   NUMBER,
   p_all_cols   IN   BOOLEAN DEFAULT TRUE
)
   RETURN VARCHAR2
IS
   v_report_id   NUMBER;
   v_region_id   NUMBER;
   v_report      apex_ir.t_report;
   v_query       VARCHAR2 (32767);
   v_column      VARCHAR2 (4000);
   v_position    NUMBER;
BEGIN
   SELECT region_id
     INTO v_region_id
     FROM apex_application_page_regions
    WHERE application_id = p_app_id
      AND page_id = p_page_id
      AND source_type = 'Interactive Report';

   v_report_id :=
      apex_ir.get_last_viewed_report_id (p_page_id        => p_page_id,
                                         p_region_id      => v_region_id
                                        );
   v_report :=
      apex_ir.get_report (p_page_id        => p_page_id,
                          p_region_id      => v_region_id,
                          p_report_id      => v_report_id
                         );
   v_query := v_report.sql_query;

   FOR i IN 1 .. v_report.binds.COUNT
   LOOP
      v_query :=
         REPLACE (v_query,
                  ':' || v_report.binds (i).NAME,
                  '''' || v_report.binds (i).VALUE || ''''
                 );
   END LOOP;

   IF p_all_cols
   THEN
      FOR c IN (SELECT   *
                    FROM apex_application_page_ir_col
                   WHERE application_id = p_app_id AND page_id = p_page_id
                ORDER BY display_order)
      LOOP
         v_column := v_column || ', ' || c.column_alias;
      END LOOP;

      v_column := LTRIM (v_column, ', ');
      v_position := INSTR (v_query, '(');
      v_query := SUBSTR (v_query, v_position);
      v_query := 'SELECT ' || v_column || ' FROM ' || v_query;
   END IF;

   RETURN v_query;
EXCEPTION
   WHEN OTHERS
   THEN
      v_query := SQLERRM;
      RETURN v_query;
END get_report_sql;
 
You can call this function in your application or in a PL/SQL package run from an application session like this:

DECLARE
   v_sql   VARCHAR2 (4000);
BEGIN
   v_sql := get_report_sql (:app_id, :app_page_id, FALSE);
   HTP.prn (v_sql);
END;
 
Setting the parameter

p_all_cols 
 
to TRUE would export all columns used in the IR SQL.

Enjoy.

39 comments:

  1. It's good to see another way to get at this information. Thanks for posting it.

    ReplyDelete
  2. Hi Denes,

    your function will be vulnerable to SQL injection attacks. It's not a good security best practice if you replace bind variables with the actual values. Always use bind variable binding if you execute the statement.

    With DBMS_SQL you can do that with dbms_sql.bind_variable or with EXECUTE IMMEDIATE with the IN parameter. APEX_PLUGIN_UTIL.GET_DATA* does also support passing in bind variables.

    Regards
    Patrick

    ReplyDelete
  3. Hello Patrick, I am aware of that but it is easier to say than to do it. I was quite happy to get it working finaly and I could imagine a similar function could be usefull in one of the future versions - the one that will be secure enough and out of the box.

    Now, what I am not sure about is:

    1. I am using this statement with an internal PL/SQL collection within a statement like this

    EXECUTE IMMEDIATE v_query BULK COLLECT INTO c_stop_collection_p41_tab;

    What exactly is the issue there?

    2. Would it be enough if I use

    dbms_assert.enquote_literal (v_report.binds (i).VALUE)

    instead of using the method you mentioned?

    And of course, thanks again for your valuable input.

    Denes

    ReplyDelete
  4. Not sure why, but this is what I get when I execute it:

    SQL> set serveroutput on
    DECLARE
    v_sql VARCHAR2 (4000);
    BEGIN
    v_sql := get_report_sql (100, 1, FALSE);
    dbms_output.put_line(v_sql);
    END;SQL> 2 3 4 5 6
    7 /
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.OWA_UTIL", line 356
    ORA-06512: at "SYS.OWA_COOKIE", line 67
    ORA-06512: at "SYS.OWA_COOKIE", line 183
    ORA-06512: at "APEX_040200.WWV_FLOW_SESSION", line 270
    ORA-06512: at "APEX_040200.WWV_FLOW_SESSION", line 118
    ORA-06512: at "APEX_040200.WWV_FLOW_DEBUG", line 248
    ORA-06512: at "APEX_040200.WWV_FLOW_ERROR", line 151
    ORA-06502: PL/SQL: numeric or value error

    PL/SQL procedure successfully completed.

    SQL>


    Did you experience this while using it. Thank you

    ReplyDelete
  5. Sorry about my last comment Denis,

    I've got it on AskTom - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:347617533333

    Thank you for the useful post.

    ReplyDelete
  6. Denes,
    Wonderful Post!! I just have one small issue, Minor Actually..
    \
    If we don't want to pass all columns through, but would like to see the report label for selected columns, do you have an easy idea on how to mesh this into your function?

    ReplyDelete
  7. I think you can use this:

    ...
    IF p_all_cols
    THEN
    FOR c IN (SELECT *
    FROM apex_application_page_ir_col
    WHERE application_id = p_app_id AND page_id = p_page_id
    ORDER BY display_order)
    LOOP
    v_column := v_column || ', ' || c.column_alias || ' "' || c.report_label || '"';
    END LOOP;

    v_column := LTRIM (v_column, ', ');
    v_position := INSTR (v_query, '(');
    v_query := SUBSTR (v_query, v_position);
    v_query := 'SELECT ' || v_column || ' FROM ' || v_query;
    END IF;
    ...

    ReplyDelete
  8. Finaly, this should do the work :)

    CREATE OR REPLACE FUNCTION get_report_sql (
    p_app_id IN NUMBER,
    p_page_id IN NUMBER,
    p_all_cols IN BOOLEAN DEFAULT TRUE,
    p_show_labels IN BOOLEAN DEFAULT FALSE
    )
    RETURN VARCHAR2
    IS
    v_report_id NUMBER;
    v_region_id NUMBER;
    v_report apex_ir.t_report;
    v_query VARCHAR2 (32767);
    v_column VARCHAR2 (4000);
    v_position NUMBER;
    v_from NUMBER;
    v_labels VARCHAR2 (4000);
    BEGIN
    SELECT region_id
    INTO v_region_id
    FROM apex_application_page_regions
    WHERE application_id = p_app_id
    AND page_id = p_page_id
    AND source_type = 'Interactive Report';

    v_report_id :=
    apex_ir.get_last_viewed_report_id (p_page_id => p_page_id,
    p_region_id => v_region_id
    );
    v_report :=
    apex_ir.get_report (p_page_id => p_page_id,
    p_region_id => v_region_id,
    p_report_id => v_report_id
    );
    v_query := v_report.sql_query;

    FOR i IN 1 .. v_report.binds.COUNT
    LOOP
    v_query :=
    REPLACE (v_query,
    ':' || v_report.binds (i).NAME,
    '''' || v_report.binds (i).VALUE || ''''
    );
    END LOOP;

    IF p_show_labels AND NOT p_all_cols
    THEN
    v_from := INSTR (UPPER (v_query), ' FROM ');
    v_labels := SUBSTR (v_query, 1, v_from);

    FOR b IN (SELECT *
    FROM apex_application_page_ir_col
    WHERE application_id = p_app_id AND page_id = p_page_id
    ORDER BY display_order)
    LOOP
    v_labels :=
    REPLACE (v_labels,
    '"' || b.column_alias || '"',
    b.column_alias || ' "' || b.report_label || '"'
    );
    END LOOP;

    v_query := v_labels || SUBSTR (v_query, v_from + 1);
    END IF;

    IF p_all_cols
    THEN
    FOR c IN (SELECT *
    FROM apex_application_page_ir_col
    WHERE application_id = p_app_id AND page_id = p_page_id
    ORDER BY display_order)
    LOOP
    v_column :=
    v_column
    || ', '
    || c.column_alias
    || CASE
    WHEN p_show_labels
    THEN ' "' || c.report_label || '"'
    ELSE NULL
    END;
    END LOOP;

    v_column := LTRIM (v_column, ', ');
    v_position := INSTR (v_query, '(');
    v_query := SUBSTR (v_query, v_position);
    v_query := 'SELECT ' || v_column || ' FROM ' || v_query;
    END IF;

    RETURN v_query;
    EXCEPTION
    WHEN OTHERS
    THEN
    v_query := SQLERRM;
    RETURN v_query;
    END get_report_sql;
    /

    Regards,

    Denes

    ReplyDelete
  9. Denes,
    Thanks for the quick reply!!! I was just going to modify the function t return th elabels out as a separate parameter, since the process I am doing is using some dynamic sql to produce CSV for users..

    ReplyDelete
  10. Thanks for sharing the tip , It looks like we don't have APEX_IR.GET_REPORT function in APEX 4.0 , do we have any other way to obtain the IR report sql ?, I have a IR report and a sub region with flash chart picturing the IR data , the challenge i have is when ever the IR report is changed by applying a filter by end user the underlying chart is not reflecting the change , so the plan is to get the IR Report SQL and apply the same to flash chart.

    Thank you
    -raj

    ReplyDelete
  11. Raj,

    unfortunately I don't think so.

    Regards,

    Denes

    ReplyDelete
  12. I have used the apex_ir_query package from Stew Stryker for some time now: http://pastebin.com/zwLBaZgz

    Regards, Garry

    ReplyDelete
  13. Denes,
    great post. I wanted to point out to Raj that there is a possiblity for pre 4.2 developers.

    Regards, Garry

    ReplyDelete
  14. Sure, but one should always try to get the newest version installed.

    Regards,

    Denes

    ReplyDelete
  15. Denes,
    My question is, and it's not in your code, but the APEX function used here:

    v_report_id := apex_ir.get_last_viewed_report_id (p_page_id => p_page_id,
    p_region_id => v_region_id);

    v_report := apex_ir.get_report (p_page_id => p_page_id,p_region_id => v_region_id,
    p_report_id => v_report_id);

    v_query := v_report.sql_query;

    If a report uses aggregate summations, the call to v_report.sql_query, is returning the aggregate columns as columns in the query.

    My users are seeing this and wanting those columns removed.. Can you think of a way to handle this?

    ReplyDelete
  16. I looked at it using computed columns - not sure what you mean by aggregate columns. The SQL returned in my case an SQL with a column named something like "APXWS_CC_001". This column doesn't appear in the list of columns when running

    SELECT *
    FROM apex_application_page_ir_col

    Maybe it is contained in an another IR Report but I couldn't find it. Eventually you could do a string manipulation with some custom PL/SQL but it could be tricky.

    Regards,

    Denes

    ReplyDelete
  17. Denes,
    The aggregate is produced in the interactive report through the tools menu.. It sums up the selected column and displays as a sub-totla or total depending upon if you're grouping rows or not..

    In my case, it produces those annoying "APXWS_CC_001"columns in the output when we run t he query produced by the apex function you produced..

    I tried posting to the forum asking ANYONE with Oracle how they handle getting the current query when they generate the CSV download option for a report and received NO response..

    So I guess I will have to go the route of doing some fancy manipulation to get rid of those "Ëxtra" columns..

    Thank you,

    Tony Miller
    LuvMuffin Software
    Ruckersville, VA

    ReplyDelete
  18. Hello Tony,

    I gues, this what you will need to do. I didn't go so far in my testing and haven't tried to group.

    Regards,

    Denes

    ReplyDelete
  19. Just wish I could get an answer from the Oracle folks on how THEY are doing it for CSV downloads...

    Thanks again for your help!!

    ReplyDelete
  20. Mildly amusing thought while reading through this - all IR examples with presumptions like this

    AND source_type = 'Interactive Report'

    will need to change from APEX 5 ;-)

    ReplyDelete
  21. Hi,

    Great post, and so easy to implement.

    I am struggling with a related, but different, issue.

    I have a client who has a lot of data, and is using some SQL with loads of joins which is resulting in a very slow query. Sadly we cannot change the query, however, we can force the user to enter filters, and return nothing if a filter is not applied.

    I have found various hits which give me a solution like

    select count(*)
    from
    apex_application_page_ir_cond c,
    apex_application_page_ir_rpt r
    where r.application_id = c.application_id
    and r.page_id = c.page_id
    and r.report_id = c.report_id
    and c.application_id = :APP_ID
    and c.page_id = :APP_PAGE_ID
    and r.session_id = :APP_SESSION
    and r.base_report_id = :P2_BASE_REPORT_ID
    and c.condition_type in ('Filter','Search')
    and c.condition_enabled = 'Yes'

    which if it results > 0 then the query returns results.

    However this does not seem to take into account whether the saved report has filters defined.

    It needs to
    1. Check if there are filters/searches for both the saved reports as well as any filters/search done on the fly.

    Any help on this would be greatly appreciated.

    Thanks,

    Jeremy

    ReplyDelete
  22. Hi all!

    Stew Stryker's APEX_IR_PACKAGE worked just fine until upgrade.
    It needs to be corrected for APEX 5.0 if possible.
    Anyone knows how to contact the author or care to fix the package (if IR 4.0->5.0 mapping is available)?

    Best regards from Croatia,


    ReplyDelete
  23. Hi Denes,

    Thanks for such a wonderful post.

    I have followed the post and i am able to populate the query to a textarea.
    Can you pleas let me know how can i use the result query in a gantt chart. When ever i am trying, i am getting error with an invalid app name saying the ir is not exists.

    Thanks,
    Sanjaya

    ReplyDelete
  24. Hi Denes,
    The function looks great and leads me on to an extra point, can I detect/trap changes to the IR filters so that I can rerun the function and possibly trigger other events depending on the results.

    Thanks,
    Alan

    ReplyDelete
  25. What if you run it after region refresh?

    Denes

    ReplyDelete
  26. What changes need to be made for APEX 5?

    ReplyDelete
  27. V5 will allow multiple interactive reports, in 4.2 you can only have 1, hence in 4.2 the "source_type = 'Interactive Report'" will always yield one row, in 5 it could return multiple, hence would need to refine the query further to ensure you select the correct report.

    ReplyDelete
  28. This comment has been removed by the author.

    ReplyDelete
  29. Agreed Denes, Static ID is the way to go, so for V5 change the query to be:

    SELECT region_id
    INTO v_region_id
    FROM apex_application_page_regions
    WHERE application_id = p_app_id
    AND page_id = p_page_id
    AND source_type = 'Interactive Report'
    AND static_id = '';

    You obviously then need to ensure your report region has the static id set accordingly.

    Thanks for this, helped me a lot, I refactored so I could pass in a list of columns as occasionally I only want a single column back. We use this so that we can drive a workflow based on the IR results (complete with any filters) on a subsequent page and allow users to move through the dataset without having to go back to the IR page.

    ReplyDelete
  30. Hi all,
    Is there another place where I can download the IR Query plugin Authored by Denes Kubicek. I want to experience it in a printing project under Apex 5 where I need to get the Interactive report Query. The related Download Link on apex-plugin.com is broken. And all the messages sent to the site Administrator still remains unanswered. I am trying a customization solution suggested by Karen Cannell in the book "Expert Oracle Apllication Express". Any personal assistance by sending me a download link would be appreciated.

    Regards

    ReplyDelete
  31. Here is a link to the blog article on this: http://deneskubicek.blogspot.com/search?q=IR+report+query

    Thank you,

    Tony Miller
    Los Alamos, NM

    ReplyDelete
  32. Hi Tony,
    Thanks for your quick reply! But I don't have any idea of how to use the code exposed there to customize the PDF printing based on a custom layout and an interactive report output driven by the end user. I was following a solution related to the dynamic action plugin suggested by Denes. But was more confused when I met the download problem since there is only one place where we can get it.

    Regards

    ReplyDelete
  33. Someone deleted all of my applications in the workspace I share including the one with the plugin. Send me your email contact to deneskubicek@yahoo.de and I will attach the plugin to it until I make it available again.

    Regards,

    Denes Kubicek

    ReplyDelete
  34. Creating a custom layout for an interactive report will entail using either an XLS style sheet or a bi publisher report. Dene's plugin will get the query but again, to use a custom layout will take more work.

    What exactly is the business requirement you are trying to resolve?

    Maybe post to the APEX support forum and others can help??

    Thank you,

    Tony Miller
    Los Alamos, NM

    ReplyDelete
  35. This comment has been removed by the author.

    ReplyDelete
  36. BI Publisher uses the RTF layout to product custom layouts, FOP uses the xls style sheets...

    Bi Publisher expects the query to be executed on the APEX end to build an XML data feed to go with the layout to produce PDF output. Thus the columns required are not really flexible in this sense of what you CAN do with an interactive report adding and subtracting columns from a report.

    I remember the topic you are talking about from the Experts Guide to APEX for APEX 5.. Just don't remember how author handled this with interactive reports. Since they can't really have the ability to modify source too much..

    ReplyDelete
  37. Hi Tony,
    There are certainly infinite solutions, but the only one I heard about is based on the IR Query Plugin authored by Denes.
    I couldn't get it because the downoload link was broken.
    I am working on a printing project where I must customize my PDF Outputs according to my interactive reports sorting an filtering.
    To make simple, I have an APEX 5 interactive report built on the following query --> SELECT EMPNO, ENAME, JOB, HIREDATE, COMM, SAL FROM EMP.
    Assume that this is the page 12 of my application.
    I created a report Query and an XSL-FO custom layout associated with this query. I am using a bipublisher server.
    Finally, I created a button on page 12 to download the PDF report associated with my report query. And it works but does not meet my needs. Now, the Goal is
    to produce the PDF Outputs based on the sorting and filtering of the end users. Any suggestion about this?

    Best Regards

    Kassoum Kone
    Abj, CIV

    ReplyDelete
  38. Hi.

    Does this still work in apex 21?

    I'm using simillar function, but in a package. When i call it in apex, in IR as "Function Body returnin SQL Query" (return package_name.get_report_sql(p_page_id => 1000)), apex developer breaks, with JSON.WRITER.NOT_OPEN error and Error: SyntaxError: Unexpected token { in JSON at position 232.

    If i show query with sys.htp.p(v_query) and then use it in apex or sql developer, it works.

    As soon as comment out
    apex_ir.get_last_viewed_report_id ...
    apex_ir.get_report ...
    page in apex developer works, but without query colums (expected).

    What could be the problem?

    Thanks.

    BR,
    Dip

    ReplyDelete