Did you ever wanted to pass dynamic query to interactive report or grid? I did. I have a uploader page where you can upload multiple files to multiple tables. Lets say there is a 60 tables. All these tables have their clones for DBMS_ERRLOG handling so I can catch errors on upload (including wrong data). Now how do you display these tables to the user? I dont want to create 60 reports for 60 error tables, I want just one. Here is how to achieve that.
1) Create form on APEX_COLLECTIONS table and hide it, we need just the items to hold real column names.
2) Create interactive report or grid to display the data from your query.
SELECT seq_id AS row#, c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020, c021, c022, c023, c024, c025, c026, c027, c028, c029, c030, c031, c032, c033, c034, c035, c036, c037, c038, c039, c040, c041, c042, c043, c044, c045, c046, c047, c048, c049, c050 FROM apex_collections c WHERE c.collection_name = 'P' || :APP_PAGE_ID;
3) Modify all columns so they have Heading from the related item and also adjust the column visibility (server side condition).
4) Initialize report with data from the query using the magic procedure below. You can pass any query you like (probably with using some page item like P100_TABLE_NAME), just keep in mind you have to fit in 50 columns. If you need more, you have to use multiple collections. And keep your query safe from SQL injection (by usign DBMS_ASSERT)!
query_to_collection ( in_query => 'SELECT * FROM' || DBMS_ASSERT.SQL_OBJECT_NAME(APEX_UTIL.GET_SESSION_STATE('P100_TABLE_NAME')), in_page_id => :APP_PAGE_ID );
And here is the magic procedure:
CREATE OR REPLACE PROCEDURE query_to_collection ( in_query VARCHAR2, in_page_id apex_application_pages.page_id%TYPE ) AS in_collection CONSTANT apex_collections.collection_name%TYPE := 'P' || TO_CHAR(in_page_id); -- out_query VARCHAR2(32767); out_cols PLS_INTEGER; out_cursor PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; out_desc DBMS_SQL.DESC_TAB; BEGIN -- initialize and populate collection IF APEX_COLLECTION.COLLECTION_EXISTS(in_collection) THEN APEX_COLLECTION.DELETE_COLLECTION(in_collection); END IF; -- APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY ( p_collection_name => in_collection, p_query => in_query ); -- pass proper column names via page items DBMS_SQL.PARSE(out_cursor, in_query, DBMS_SQL.NATIVE); DBMS_SQL.DESCRIBE_COLUMNS(out_cursor, out_cols, out_desc); DBMS_SQL.CLOSE_CURSOR(out_cursor); -- FOR i IN 1 .. out_desc.COUNT LOOP BEGIN APEX_UTIL.SET_SESSION_STATE ( p_name => 'P' || in_page_id || '_C' || LPAD(i, 3, 0), p_value => out_desc(i).col_name, p_commit => FALSE ); EXCEPTION WHEN OTHERS THEN NULL; -- item might not exists END; END LOOP; END; /