APEX Training

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.

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