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.