Set page items based on SQL query

I

If you want to create a lot of items on APEX page, you should use the Form region. Based on a table/view or query it will create items for every column and add init process to prefill these items for you when you pass a primary key. Neat.

The thing is, sometimes I can't use the form processes, I just need to set items based on a query. The usual approach is to SELECT INTO (select columns into the items). For 20 items you will get 40+ lines query, for 60 items you will get 120+ lines, you get the idea. You have to be careful about the column names and items names and not to mix them. And this also leads to different item names than columns.

SELECT
	column_name1,
	column_name2,
	column_name3,
	column_name4 ...
INTO
	:P100_column_name1,
	:P100_column_nameee2,
	:P100_column_name3,
	:P100_col_name4 ...
FROM your_table
WHERE ROWNUM = 1;

How about this?

DECLARE
    l_curs SYS_REFCURSOR;
BEGIN
    OPEN l_curs FOR
        SELECT *                        -- your columns which you would like to set
        FROM your_table                 -- your table name
        WHERE ROWNUM = 1;               -- your primary key
    --
    app_util.set_page_items(l_curs);    -- set these items on current page
END;
/

No need to list column and page items.

I created a small package that contain overloaded set_page_items procedures and functions. You can pass either cursor or a string query. You can call it from SQL and from PL/SQL. And you dont have to set the page_id if you call this from APEX.

    -- query based
    PROCEDURE set_page_items (
        in_query            VARCHAR2,
        in_page_id          NUMBER          := NULL
    );

    FUNCTION set_page_items (
        in_query            VARCHAR2,
        in_page_id          NUMBER          := NULL
    )
    RETURN page_items_table PIPELINED;

    -- cursor based
    PROCEDURE set_page_items (
        in_cursor           SYS_REFCURSOR,
        in_page_id          NUMBER          := NULL
    );

    FUNCTION set_page_items (
        in_cursor           SYS_REFCURSOR,
        in_page_id          NUMBER          := NULL
    )
    RETURN page_items_table PIPELINED;

These functions are basically one line UNPIVOT, but dynamic due to leveraging DBMS_SQL because I can get list of columns from that. And using PIPE ROW I can create the output as a table, so you can also run queries like these:

SELECT *
FROM TABLE (
    app_util.set_page_items(CURSOR(
        SELECT *
        FROM your_table
        WHERE ROWNUM = 1
    ),
    100
));
--
SELECT *
FROM TABLE (
    app_util.set_page_items('
        SELECT *
        FROM your_table
        WHERE ROWNUM = 1
        ',
        100
    ));

Go ahead, download the app_util package and give it a go.

You can check the page items on a specific page:

SELECT
    i.item_name,
    APEX_UTIL.GET_SESSION_STATE(i.item_name) AS item_value
FROM apex_application_page_items i
WHERE i.application_id      = &APP_ID.
    AND i.page_id           = &PAGE_ID;