Introduction

With so many emerging technologies inside and outside the Oracle database ecosystem, it might seem trivial, even old-fashioned, to talk about database views in APEX.

A recent project reminded me of the importance of getting the basics right, so I thought I would share this insight in a brief post.

Use Case

Recently, a project reminded me why views are still critical. I was building an inventory count application with three core tables: items, count_sheets, and item_categories. Several pages in the APEX app displayed similar data combinations, initial count, verified count, administrative adjustments, and extended values.

Here’s a simplified version of one of those queries:

SELECT ctgy.category_name
,      ctlg.part_number
,      ctlg.part_description
,      ctlg.uom
,      ctlg.price
,      ROUND(ctlg.price / NULLIF(ctlg.qty_conversion, 0), 2) AS unit_price
,      csh.counter_count                                     AS counter_count
,      csh.reconcile_count                                   AS reconcile_count
,      csh.adjustment_count                                  AS adjustment_count
,      COALESCE(csh.adjustment_count, 
                csh.reconcile_count, 
                csh.counter_count)                           AS final_count
FROM   inv_count_sheet csh
,      inv_catalog     ctlg
,      inv_category    ctgy
WHERE  csh.item_id      = ctlg.item_id
AND    ctlg.category_id = ctgy.category_id;

There is nothing overly complicated about the SQL. If I use it in the four or five pages, you may not think it is a big deal to replicate the SQL.

💡 What if the calculation for final_count needs to change?

If you’ve repeated this SQL across several APEX pages, even a small change means hunting down and updating each instance, then testing every affected page.

By encapsulating the query in a database view, that same change takes seconds to apply and test.

Of course, this approach is not a panacea. If I needed to add columns or remove columns, I still need to make some code changes. Also, be mindful of performance implications. While views simplify code maintenance, they can sometimes hide expensive joins or aggregations.

Other Benefits

Re-use and encapsulation of complex logic is the primary benefit of using views, but there are others:

• Elegant APEX Apps - Instead of embedding complex joins or aggregations in multiple APEX pages, you define them once in a view. This centralized logic makes your APEX pages much simpler and easier to maintain.

• Security - You can limit the scope of views and expose the views instead of the underlying tables to consumers. When combined with APEX_SESSION.SET_TENANT_ID, and SYS_CONTEXT('APEX$SESSION', 'APP_TENANT_ID'), you can use views to limit access in Multi-Tenant Applications.

• Consistent Data Model for APEX Components - Interactive grids, reports, and charts all query the same logical layer. This ensures uniform results across the application and reduces data inconsistency issues.

• Reusability Across Consumers - The same view can serve multiple APEX apps, RESTful services, AOP Reports, and Document Generator Reports, promoting modular design and avoiding duplicated SQL definitions.

SQL Macros

For even more flexibility, Oracle provides SQL Macros (introduced in Oracle Database 19c). SQL Macros let you encapsulate reusable SQL logic, either as scalar or table macros, that expand at parse time. Unlike static views, they can accept parameters. This makes them a powerful complement to traditional views when you need more adaptable query definitions.

In the table SQL Macro example below, I can pass in the Business Unit ID (p_bu_id) as a paremeter:

CREATE OR REPLACE FUNCTION example_sql_macro (p_bu_id IN NUMBER) RETURN CLOB SQL_MACRO AS
BEGIN
  RETURN q'{
SELECT ctgy.category_name
,      ctlg.part_number
FROM   inv_count_sheet csh
,      inv_catalog     ctlg
,      inv_category    ctgy
WHERE  csh.item_id      = ctlg.item_id
AND    ctlg.category_id = ctgy.category_id
AND    csh.bu_id        = p_bu_id
  }';
END example_sql_macro;

You use table SQL Macros in a SELECT statement just like a regular view:

SELECT *
FROM   example_sql_macro (p_bu_id => 1);

Parameters are substituted at parse time, and then the SQL is executed.

Conclusion

This post is a reminder to myself and others that views are an essential part of thoughtful APEX Application Development. Even though there are plenty of shiny new tech tools out there, there is no excuse not to do the basics right.