E
Everyone should know the power of WITH clause these days. For me this is a great way how to have more readable views and with some magic it also allows me to have dynamic views (well, the views returning different rows based on page items).
You are asked to show a monthly based calendar on a page (and let's skip the Calendar region for demo purposes) based on a page item.
Here is how you can utilize WITH clause on a regular query (returning a calendar data for a whole year):
WITH days AS (
SELECT
TRUNC(SYSDATE, 'Y') + LEVEL - 1 AS day,
TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'IYIW') AS week,
TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'MM/YYYY') AS month
FROM DUAL
CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - TRUNC(SYSDATE, 'Y')
)
SELECT
d.month,
d.week,
MAX(DECODE(TO_CHAR(d.day, 'DY'), 'MON', TO_NUMBER(TO_CHAR(d.day, 'FMDD')))) AS mon,
MAX(DECODE(TO_CHAR(d.day, 'DY'), 'TUE', TO_NUMBER(TO_CHAR(d.day, 'FMDD')))) AS tue,
MAX(DECODE(TO_CHAR(d.day, 'DY'), 'WED', TO_NUMBER(TO_CHAR(d.day, 'FMDD')))) AS wed,
MAX(DECODE(TO_CHAR(d.day, 'DY'), 'THU', TO_NUMBER(TO_CHAR(d.day, 'FMDD')))) AS thu,
MAX(DECODE(TO_CHAR(d.day, 'DY'), 'FRI', TO_NUMBER(TO_CHAR(d.day, 'FMDD')))) AS fri,
MAX(DECODE(TO_CHAR(d.day, 'DY'), 'SAT', TO_NUMBER(TO_CHAR(d.day, 'FMDD')))) AS sat,
MAX(DECODE(TO_CHAR(d.day, 'DY'), 'SUN', TO_NUMBER(TO_CHAR(d.day, 'FMDD')))) AS sun
FROM days d
GROUP BY d.month, d.week
ORDER BY 1, 2;
Almost always I see this done as a region with whole query as a region source, with page items binded here and there:
SELECT
...
FROM days d
WHERE d.month = :P100_MONTH
ORDER BY 1, 2;
I really don't like queries on regions. You can read about this on my Object Referencing article. So I would create a view and use that as a region source.
CREATE OR REPLACE VIEW p100_calendar_v AS
SELECT
...
FROM days d
ORDER BY 1, 2;
Since you can't use the binded variables in a view, you would have to specify the WHERE condition under the region source. Which sucks, because when you change the region source, you will loose this filter and also you now have half of the logic in database view and other on region WHERE filter.
month = :P100_MONTH -- :P100_MONTH := TO_CHAR(TRUNC(SYSDATE), 'MM/YYYY');
How can you use page items in a view?
Most people don't realize you can actually access the page items (and other APEX things) from PL/SQL through the APEX_UTIL.GET_SESSION_STATE function.
CREATE OR REPLACE VIEW p100_calendar_v AS
WITH x AS (
SELECT
APEX_UTIL.GET_SESSION_STATE('P100_MONTH') AS month
FROM DUAL
)
SELECT
...
FROM days d
JOIN x
ON x.month = d.month
ORDER BY 1, 2;
This is great, but it is slow. I didn't realized this at first, but calling APEX_UTIL.GET_SESSION_STATE will fire for each row in a query. Hence you will face performance issues and you might abandon this solution. Don't.
All you need is a hint (and I recommend to read article by Tomasz Lesinski and article by Tim Hall):
CREATE OR REPLACE VIEW p100_calendar_v AS
WITH x AS (
SELECT /*+ MATERIALIZE */
APEX_UTIL.GET_SESSION_STATE('P100_MONTH') AS month
FROM DUAL
)
SELECT
...
FROM days d
JOIN x
ON x.month = d.month
ORDER BY 1, 2;
With this solution you can have all logic stored in a view. You are not limited just to views or just to page items. Or just getting the values. You can get/set page/application item values from a procedure too. Or get values passed from IG.
Base views
I was asked by a client to create 3 similar reports on 3 different pages. I did used these WITH clause and page items there and I have realized these filters are basically the same on all reports. So I created a REPORT_BASE_V view to filter data on one place and in the reports I have selected data from this view. Later when I was asked to add a new filter, I have added it just to the base view. Simpler, faster, less bugs, less clutter, less copy pasting... I love this.
The trick is that I have checked the page number from which the view is called and adjusted the page item name based on that. So the same report_base_v view would use P100_MONTH item on page 100, but P200_MONTH item when called from page 200. Neat.
CREATE OR REPLACE VIEW report_base_v AS
WITH x AS (
SELECT /*+ MATERIALIZE */
core.app.get_item('$MONTH') AS month -- this return different item on different pages
FROM DUAL
)
SELECT
...
FROM days d
JOIN x
ON x.month = d.month
ORDER BY 1, 2;
You can find this in my APP package:
FUNCTION get_item (
in_name VARCHAR2
)
RETURN VARCHAR2
AS
v_item_name apex_application_page_items.item_name%TYPE;
BEGIN
v_item_name := app.get_item_name(in_name);
--
IF v_item_name IS NOT NULL THEN
RETURN APEX_UTIL.GET_SESSION_STATE(v_item_name);
END IF;
--
RETURN NULL;
END;
FUNCTION get_item_name (
in_name apex_application_page_items.item_name%TYPE,
in_page_id apex_application_page_items.page_id%TYPE := NULL,
in_app_id apex_application_page_items.application_id%TYPE := NULL
)
RETURN VARCHAR2
AS
v_item_name apex_application_page_items.item_name%TYPE;
v_page_id apex_application_page_items.page_id%TYPE;
v_app_id apex_application_page_items.application_id%TYPE;
is_valid CHAR;
BEGIN
v_app_id := NVL(in_app_id, app.get_app_id()); -- APEX_APPLICATION.G_FLOW_ID
v_page_id := NVL(in_page_id, app.get_page_id()); -- APEX_APPLICATION.G_FLOW_STEP_ID
v_item_name := REPLACE(in_name, '$', 'P' || v_page_id || '_');
-- check if item exists
BEGIN
SELECT 'Y' INTO is_valid
FROM apex_application_page_items p
WHERE p.application_id = v_app_id
AND p.page_id IN (0, v_page_id)
AND p.item_name = v_item_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT 'Y' INTO is_valid
FROM apex_application_items g
WHERE g.application_id = v_app_id
AND g.item_name = in_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
END;
--
RETURN v_item_name;
END;
MVC design pattern
Remember design patterns from "real" programming languages? Think about MVC and APEX...
- Model = your tables - basically your stored data
- Controller = your packages, views, sequences - basically ALL your business logic
- View = your APEX app (pages, regions, components) to present data to user and gather inputs
Why would you store some business logic on a View layer? It doesn't belong there.
Great !
ReplyDelete