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:

Stew said...

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

Patrick Wolf said...

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

Denes Kubicek said...

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

Anonymous said...

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

Anonymous said...

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.

APEX Developer In the USA said...

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?

Denes Kubicek said...

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;
...

Denes Kubicek said...

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

APEX Developer In the USA said...

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..

raj said...

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

Denes Kubicek said...

Raj,

unfortunately I don't think so.

Regards,

Denes

Garry Lawton said...

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

Regards, Garry

Garry Lawton said...

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

Regards, Garry

Denes Kubicek said...

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

Regards,

Denes

APEX Developer In the USA said...

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?

Denes Kubicek said...

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

APEX Developer In the USA said...

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

Denes Kubicek said...

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

APEX Developer In the USA said...

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!!

Scott Wesley said...

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 ;-)

Jeremy Webb said...

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

Igor Filko said...

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,


Sanjaya said...

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

Unknown said...

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

Denes Kubicek said...

What if you run it after region refresh?

Denes

APEX Developer In the USA said...

What changes need to be made for APEX 5?

Jason McCaul said...

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.

Denes Kubicek said...

Try with Report Static ID.

Unknown said...
This comment has been removed by the author.
Jason McCaul said...

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.

Unknown said...

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

APEX Developer In the USA said...

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

Unknown said...

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

Denes Kubicek said...

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

APEX Developer In the USA said...

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

Unknown said...
This comment has been removed by the author.
APEX Developer In the USA said...

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..

Unknown said...

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

Dipling said...

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