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;