In the previous blog posts, we have shown you how to:

• create quality rules and standards for APEX components,
• transfer quality standards between environments,
• use the rule builder to create consistency  rules and
• run quality assessments live in your runtime applications.

Now we will talk about how to create advanced rules enabling you to check every inch of your APEX Applications and your Database.

Database rules

Previously we have mentioned only rules for your APEX Components, but APE can also be used to assess your database objects. The rule builder does not yet support the creation of the database rules, so you have to create them manually. 

Luckily APE creates a default SQL Statement after selecting a database component type to get you started.

Here is an example of a database rule which checks if your tables have all four audit columns

SELECT at.owner|| ':' || at.table_name AS component_id,
       NULL AS additional_comment
  FROM all_tables at
 WHERE 4 != (
          SELECT COUNT(*)
            FROM all_tab_cols atc
           WHERE atc.table_name = at.table_name
             AND atc.owner = at.owner
             AND atc.column_name IN ('CREATED', 'CREATED_BY', 'UPDATED', 'UPDATED_BY')
       )

If your quality standard contains database rules, you must also define the database schemas you want to assess in the assessment scope. Use the checkbox to include the parsing schemas of your applications automatically:

After running the assessment, you can see the list of database objects that don’t follow your guidelines:

If you are assessing database schemas where APE is not installed by default, you will only have access to the database objects which have been granted access privileges to APE’s parsing schema. 

Custom rules for multiple component types

With the Rule Builder, it’s possible to create rules for specific APEX components, but in some cases, you want to write a single rule to assess different component types simultaneously. Some examples from our Quality Standards are:

• Instead of using the “Never” server-side conditions, use Build options.
• A shared component is not used anywhere in your applications.
• Report columns are not escaping special characters.

To support these use cases, you can use the APEX Custom component or Database Custom component types when creating rules manually. These types allow you to write a SQL Statement for different component types at the same time. The syntax is a bit more complicated as you must provide additional information in the SQL statement, but APE offers a template to get you started.

The most important part is to define the correct APEX or DB dictionary view for the COMPONENT_TYPE_CODE column inside your statement. Here is a code snippet from our rule for assessing the “Never” server-side condition.

SELECT    
       -- component details
       component_id,
       component_type_code,
       component_name,
       component_location,
       additional_comment,
       -- scope details
       workspace,
       application_id,
       page_group_id,
       page_id,
       region_id,
       db_owner
  FROM ( 
       SELECT
              -- Page items
              CAST(aapi.item_id AS VARCHAR2(100)) AS component_id,
               'APEX_APPLICATION_PAGE_ITEMS' AS component_type_code,
              aapi.item_name AS component_name,
              'Application: ' || aapi.application_id || ' > Page: ' || aapi.page_id AS component_location,
              NULL AS additional_comment,
               -- 
              aapi.workspace,
              aapi.application_id,
              aap.page_group_id,
              aapi.page_id,
              aapi.region_id,
              NULL AS db_owner
         FROM apex_application_page_items aapi
         JOIN apex_application_pages aap ON aapi.application_id = aap.application_id AND aapi.page_id = aap.page_id
        WHERE aapi.condition_type_code = 'NEVER'
       UNION ALL
       SELECT
              -- Buttons
              CAST(aapb.button_id AS VARCHAR2(100)) AS component_id,
               'APEX_APPLICATION_PAGE_BUTTONS' AS component_type_code,
              aapb.button_name AS component_name,
              'Application: ' || aapb.application_id || ' > Page: ' || aapb.page_id AS component_location,
              NULL AS additional_comment,
              --
              aapb.workspace,
              aapb.application_id,
              aap.page_group_id,
              aapb.page_id,
              aapb.region_id,
              NULL AS db_owner
         FROM apex_application_page_buttons aapb
         JOIN apex_application_pages aap ON aapb.application_id = aap.application_id AND aapb.page_id = aap.page_id
        WHERE aapb.condition_type_code = 'NEVER'
       UNION ALL
       SELECT 
              -- Regions
              CAST(aapr.region_id AS VARCHAR2(100)) AS component_id,
               'APEX_APPLICATION_PAGE_REGIONS' AS component_type_code,
              aapr.region_name AS component_name,
              'Application: ' || aapr.application_id || ' > Page: ' || aapr.page_id AS component_location,
              NULL AS additional_comment,
              --
              aapr.workspace,
              aapr.application_id,
              aap.page_group_id,
              aapr.page_id,
              aapr.region_id,
              NULL AS db_owner
         FROM apex_application_page_regions aapr
         JOIN apex_application_pages aap ON aapr.application_id = aap.application_id AND aapr.page_id = aap.page_id
        WHERE aapr.condition_type_code = 'NEVER'
       UNION ALL
SELECT ...

You can always write separate rules for each component type, but we find navigating through the assessment results easier if all found issues are linked to the same rule.

You can also write custom rules for your database objects. One common example is to check the naming conventions across all your database objects.

Using pipelined functions

Writing a SQL Statement can be challenging for certain more complex use cases. Sometimes it makes more sense to prepare some PL/SQL code which can be called from your SQL Statement. Usually, PL/SQL Pipelined Functions are a good approach to help you with your rule logic.

With APE, some of these functions are already delivered and can be reused for different scenarios. Two of those functions are:

1. The APE_QA_API.F_GET_SOURCE_FOR_CHECK function returns all SQL and PL/SQL sources in your APEX application. You can then write rules logic based on those sources, for example, to check if the syntax is valid or if there are any SQL Injection threats inside the code because of the use of EXECUTE IMMEDIATE statements.

2. The APE_SEARCH_ENGINE.SEARCH returns all APEX Components based on a search string. With this, it’s possible to search the whole application for the use of deprecated functionality. For example, you can make sure that all APP_IMAGES substitution strings are replaced by the new APP_FILES substitution string:

Remember, you can copy rules from the preloaded UNITED CODES Way of working Quality Standards, adapt them for your needs, or write your own PL/SQL logic and use it in your rule SQL statements.

Using PL/Scope

PL/Scope is a compiler-driven tool that collects PL/SQL and SQL identifiers and SQL statement usage in PL/SQL source code. (link to source)

You can query the ALL_IDENTIFIERS for various information about your program code. In the example below, we are trying to identify unused variables in database packages.

Unused variables aren’t doing anything wrong, but they unnecessarily clutter the program logic. They were usually used at some point for testing or were left over after code was moved around or removed. 

We use the APE Component Type DB Custom component type in this example. Let’s have a look at the SQL:

with identifiers_inline 
  as ( select *
         from all_identifiers ide
        where  (  regexp_like( ide.object_name, '^UC_(+)')
               or regexp_like( ide.object_name, '^APE_(+)')
               )
           and object_type = 'PACKAGE BODY'
     )
select
    --object info
    owner || ':' ||object_name as component_id,
    'ALL_OBJECTS' as component_type_code,
    name as component_name,
    line || ':' || col as component_location,
    'Package: ' || object_name || ' - Line: ' || line as additional_comment,
    --scope columns
    null as workspace,
    null as application_id,
    null as page_group_id,
    null as page_id,
    null as region_id,
    owner as db_owner
  from identifiers_inline ide
 where type in ('VARIABLE', 'CONSTANT')
   and usage = 'DECLARATION'
   and implicit = 'NO'
          and upper (name) != ('L_PARAMS')
   and (  not exists
            ( select 1
                from identifiers_inline
               where signature = ide.signature
                 and usage <> 'DECLARATION'
            )  
       or not exists
            ( select 1
                from identifiers_inline
               where signature = ide.signature
                 and usage <> 'DECLARATION'
                 and line <> ide.line
                 and col <> ide.col
            )
       )
 order by object_name, line

In the with statement, we are limiting the assessment to identifiers in package bodies only. With the regexp_like function, we filter only objects we actually want to investigate. In this example, only packages with the prefix APE and UC are picked up.

To identify the unused variables, we select from the result in the with statement and limit it to declared variables and constants. The two not exists clauses identify those variables that are declared but not used anywhere else in the code.

It’s important to know that this information is only available if PL/Scope information is stored in the database dictionary views. You can enable this by changing the database session parameters before compiling your PL/SQL code:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';

There is also a compilation setting in your SQL Developer which is enabled by default:

In our project, we run an APEX Automation periodically overnight, which alters the session parameters and re-compiles all database packages on the development environment with the necessary PL/Scope information.

Conclusion

With each project, we find new ideas and opportunities to build new quality rules, which we include in our Quality Standards. 

APEX Project Eye offers a lot of different possibilities to document these rules and use them in our projects to bring the quality of our APEX applications to a higher grade. Often, only our lack of imagination is holding us back. I hope this article has given you some ideas for writing new quality rules. 

And after you write them, don’t hesitate to exchange ideas with other teams and the APEX community. Together we can always achieve more.