Tuesday, March 7, 2023
In the previous blog posts, we have shown you how to:
Now we will talk about how to create advanced rules enabling you to check every inch of your APEX Applications and your Database.
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.
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:
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.
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.
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.
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.
No comments yet, be the first one to let us know what you think of this article!