Skip to main content

APEX_COLLECTIONS and dynamic interactive report or grid with named headers

D

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


Comments