Home > Application Express, Oracle > Apex Interactive Reports with Dynamic Sql

Apex Interactive Reports with Dynamic Sql

Interactive Reports in Apex, presents too many features that will be performed on reports. You can apply specified column filtering, highlighted specified values in a column, use aggregation functions, ajax paginations are some of these features. To use these features you can create a report region as Interactive report ( In a page attributes, Create Region > Report > Interactive Report ).

While creating Interactive Reports, apex wants from you an sql word. It must be pure sql unlike varchar2 variable that stores sql ( in standart reports you can use a varchar2 variable that stores sql. For example lv_dynamic_sql := ‘SELECT EMP_NO FROM EMPLOYEE’; return lv_dynamic_sql; ). So in normally you can’t use dynamic sql in interactive report. I said “in normally” ­čÖé

By using PIPELINED FUNCTIONS you can use dynamic sql in Apex Interactive Reports.

In Interactive Reports, you will specify sql as “SELECT * FROM TABLE(table_function);”

After that you should design your “table_function()” function.

First of all you should create an object type that matches a report row fields.

CREATE OR REPLACE TYPE OBJ_STREAM_FILTERED AS OBJECT
(
a_rowid VARCHAR2(100),
ActivationType VARCHAR2(100),
ActivationCellCgi VARCHAR2(100),
TerminationCellCgi VARCHAR2(100),
IMSI VARCHAR2(100),
IMEI_TAC VARCHAR2(100),
IMEI_LAC VARCHAR2(100),
ActivationSec VARCHAR2(100),
OriginationCellCgi VARCHAR2(100),
TargetCellCgi VARCHAR2(100),
ENQ_DATE DATE,
INSERT_DATE DATE,
REMOTE_HOST VARCHAR2(100),
IMEI VARCHAR2(100),
MSISDN_NUMBER VARCHAR2(100),
GENERAL_SEGMENT VARCHAR2(100),
GENERAL_SUB_SEGMENT VARCHAR2(100)
)
/

Your report fields should match these object type fields.

After that you should design your “table_function” function.

CREATE OR REPLACE FUNCTION AQ_ADM_PRTP_V204.get_table_rows2(p_table_name VARCHAR2)
RETURN OBJ_STREAM_FILTERED_ARR
PIPELINED IS
out_rec OBJ_STREAM_FILTERED := OBJ_STREAM_FILTERED(NULL,
null,
NULL,
NULL,
null,
null,
null,
null,
null,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL);
p sys_refcursor;

v0 VARCHAR2(100);
v1 VARCHAR2(100);
v2 VARCHAR2(100);
v3 VARCHAR2(100);
v4 VARCHAR2(100);
v5 VARCHAR2(100);
v6 VARCHAR2(100);
v7 VARCHAR2(100);
v8 VARCHAR2(100);
v9 VARCHAR2(100);
v10 VARCHAR2(100);
v11 VARCHAR2(100);
v12 VARCHAR2(100);
v13 VARCHAR2(100);
v14 VARCHAR2(100);
v15 VARCHAR2(100);
v16 VARCHAR2(100);
t1 TIMESTAMP(6);
t2 TIMESTAMP(6);
BEGIN
OPEN p FOR ‘SELECT
t.rowid,
t.*,
t2.imei,
t2.msisdn_number,
t2.general_Segment,
t2.general_sub_segment
FROM
‘ || p_table_name || ‘ t,
syn_rds_imsi_info t2
WHERE
CONCAT(CONCAT(column15,column16),column17) = t2.IMSI(+)’;
LOOP
FETCH p
INTO v0, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, t1, t2, v12, v13, v14, v15, v16;
EXIT WHEN p%NOTFOUND;
out_rec.a_rowid := v0;
out_rec.ActivationType := v1;
Out_rec.ActivationCellCgi := v2;
out_rec.TerminationCellCgi := v3;
out_rec.IMSI := v4 || v5 || v6;
out_rec.IMEI_TAC := v7;
out_rec.IMEI_LAC := v8;
out_rec.OriginationCellCgi := v10;
out_rec.TargetCellCgi := v11;
out_rec.ActivationSec := v9;
out_rec.ENQ_DATE := t1;
out_rec.INSERT_DATE := t2;
out_rec.REMOTE_HOST := v12;
out_rec.IMEI := v13;
out_rec.MSISDN_NUMBER := v14;
out_rec.GENERAL_SEGMENT := v15;
out_rec.GENERAL_SUB_SEGMENT := v16;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

You can check this function accuracy:

SELECT * FROM TABLE(get_table_rows2(‘galata_report.FUAT123’));

Anymore, there is no need to do anything to use this sql in interactive report.

If you get an uniquely related error you can specify a unique column ( a_rowid in this example ) in Report Attributes > Link Column section.

Edited on 05.10.2010 :
There is a better solution to handle more dynamic sql. Which means that you can prepare dynamic interactive report for any sql. Those sqls do not need to has same number of columns or same column names. It is demonstrated at http://www.oracleapplicationexpress.com/tutorials/71 .

Advertisements
  1. esraaktas
    May 12, 2011 at 7:26 pm

    Merhaba
    interactive reportta bir proceduru ├ža─č─▒rman─▒n herhangi bir yolu var m─▒ acaba ? Yaz─▒n─▒z arad─č─▒m bir sorun i├žin ├žok iyi ├ž├Âz├╝m bana asl─▒nda te┼čekk├╝rler fakat bir procedurun apexde bir reportta ├ža─č─▒r─▒labilmesi m├╝mk├╝n m├╝ ├Â─črenmek i├žin sormak istedim.
    ┼×imdiden te┼čekk├╝rler

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: