O
One of the most underrated things is checking the state of your database and APEX apps. You deploy things to Prod, nothing crashes (so far), so you move on. This is a strategy I've seen on many projects: no unit tests, barely some random user tests, and that's it. On top of that, in some projects I don't even have access to UAT. I only have access to Dev, but I somehow still have to make things work, even without read-only access anywhere else.
So, how would you check things in a place where you don't have any access? Fortunately, I can at least send emails. So today, it's about the first email.
What to check?
The backend side contains mostly things for developers, like errors, logs, and security issues, or just to see what changed or what is different. Each link will take you to the source code of the appropriate view (because views are awesome: they keep the package readable and your reports flexible, since you can add your own views or remove the ones you don't need). I won't copy-paste the code here, so I can show you the more interesting parts, how to make this whole thing flexible.
- Versions (of database, APEX, ORDS)
- Invalid Objects
- Disabled Objects (because I face this a lot)
- Compile Errors
- Schedulers (very important, can affect the whole system)
- Materialized Views (also important, they might get stuck)
- Synonyms (with grants and referenced object status)
- Failed Authentications (in APEX)
- Missing VPD Policies
- Broken APEX Components (you need to scan the apps first)
- Workspace Errors (also APEX related)
- APEX Debug Messages
- Mail Queue Errors (to see if you have mails stuck in the queue)
- Web Service Calls (to see how many calls you are doing)
Technical Part
How would you query 20 views and convert the results into HTML tables so you can stitch them together into one email? And how would you do it in a smart way, so you don't need to know the view names or the structure (columns)?
First, we need a cursor which will find all relevant views (CORE_DAILY_%_V) and sort them (by the comments).
CURSOR related_views (
in_prefix VARCHAR2 -- like CORE_DAILY
)
IS
SELECT
t.view_name,
COALESCE(
REGEXP_SUBSTR(c.comments, '\|\s*([^\|]+)', 1, 1, NULL, 1),
INITCAP(REPLACE(REGEXP_SUBSTR(t.view_name, '^' || in_prefix || '_(.*)_V$', 1, 1, NULL, 1), '_', ' '))
) AS header3,
REGEXP_SUBSTR(c.comments, '\|\s*([^\|]+)', 1, 2, NULL, 1) AS header2
FROM user_views t
LEFT JOIN user_tab_comments c
ON c.table_name = t.view_name
WHERE t.view_name LIKE in_prefix || '\_%\_V' ESCAPE '\'
AND t.view_name NOT LIKE in_prefix || '\_\_%\_V' ESCAPE '\'
ORDER BY
c.comments || t.view_name;
I utilized the view comments to handle the order and headers. You could use a regular table or annotations. In the comments you can also define the table header and main header (to group tables). The pattern is "Order# | Header | Group header".
SELECT
t.table_name,
t.comments
FROM user_tab_comments t
WHERE t.table_name LIKE 'CORE_DAILY_%\_V' ESCAPE '\'
ORDER BY 2;
| Table Name | Comments |
|---|---|
| CORE_DAILY_VERSIONS_V | 10 | Versions | Database Overview |
| CORE_DAILY_INVALID_OBJECTS_V | 11 | Invalid Objects |
| CORE_DAILY_DISABLED_OBJECTS_V | 12 | Disabled Objects |
| CORE_DAILY_COMPILE_ERRORS_V | 13 | Compile Errors |
| CORE_DAILY_SCHEDULERS_V | 20 | Schedulers | Schedulers & Materialized Views |
| CORE_DAILY_MATERIALIZED_VIEWS_V | 21 | Materialized Views |
| CORE_DAILY_SYNONYMS_V | 22 | Synonyms |
| CORE_DAILY_FAILED_AUTHENTICATION_V | 30 | Failed Authentication | Security Issues |
| CORE_DAILY_MISSING_VPD_POLICIES_V | 31 | Missing VPD Policies |
| CORE_DAILY_BROKEN_APEX_COMPONENTS_V | 40 | Broken APEX Components | APEX Issues |
| CORE_DAILY_WORKSPACE_ERRORS_V | 41 | Workspace Errors |
| CORE_DAILY_APEX_DEBUG_MESSAGES_V | 42 | APEX Debug Messages |
| CORE_DAILY_MAIL_QUEUE_ERRORS_V | 43 | Mail Queue Errors |
| CORE_DAILY_WEB_SERVICE_CALLS_V | 44 | Web Service Calls |
The whole header structure will be more clear after you read the generated email.
Second, we need to convert generic cursor to HTML table:
FUNCTION get_content (
io_cursor IN OUT SYS_REFCURSOR,
--
in_header VARCHAR2 := NULL
)
RETURN CLOB
AS
v_cursor PLS_INTEGER;
v_desc DBMS_SQL.DESC_TAB;
v_cols PLS_INTEGER;
v_number NUMBER;
v_date DATE;
v_value VARCHAR2(4000);
v_header VARCHAR2(32767);
v_line VARCHAR2(32767);
v_align VARCHAR2(2000);
v_style VARCHAR2(2000);
v_out CLOB := EMPTY_CLOB();
--
TYPE t_array IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(128);
v_styles t_array;
BEGIN
v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(io_cursor);
-- get column names
DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
-- process headers
FOR i IN 1 .. v_cols LOOP
-- retrive value and do formatting
v_align := ' align="left"';
--
IF v_desc(i).col_type = DBMS_SQL.NUMBER_TYPE THEN
DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_number);
v_align := ' align="right"';
ELSIF v_desc(i).col_type = DBMS_SQL.DATE_TYPE THEN
DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_date);
ELSE
DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_value, 4000);
END IF;
-- identify and store column position with style value
IF v_desc(i).col_name LIKE '%\_\_STYLE' ESCAPE '\' THEN
v_styles(REPLACE(RTRIM(v_desc(i).col_name, '_'), '__STYLE', '')) := i;
CONTINUE;
END IF;
--
v_line := v_line || '<th' || v_align || '>' || get_column_name(v_desc(i).col_name) || '</th>';
END LOOP;
--
v_out := v_out || TO_CLOB('<table cellpadding="5" cellspacing="0" border="1"><thead><tr>' || v_line || '</tr></thead><tbody>');
-- fetch data
v_line := '';
WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0 LOOP
v_line := '';
--
FOR i IN 1 .. v_cols LOOP
-- process all columns except style ones
IF RTRIM(v_desc(i).col_name, '_') LIKE '%\_\_STYLE' ESCAPE '\' THEN
CONTINUE;
END IF;
--
v_align := '';
--
IF v_desc(i).col_type = DBMS_SQL.NUMBER_TYPE THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_number);
v_value := TO_CHAR(v_number);
v_align := ' align="right"';
ELSIF v_desc(i).col_type = DBMS_SQL.DATE_TYPE THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_date);
v_value := CASE WHEN v_date IS NOT NULL THEN TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI') END;
ELSE
DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_value);
END IF;
-- apply styles
IF v_styles.EXISTS(v_desc(i).col_name) THEN
DBMS_SQL.COLUMN_VALUE(v_cursor, v_styles(v_desc(i).col_name), v_style);
v_align := v_align
|| ' style="' || CASE
WHEN v_style = 'RED' THEN 'color: #f00;'
ELSE v_style END || '"';
END IF;
--
v_line := v_line || '<td' || v_align || '>' || TRIM(v_value) || '</td>';
END LOOP;
--
v_out := v_out || '<tr>' || v_line || '</tr>';
END LOOP;
-- cleanup
close_cursor(v_cursor);
-- prepend headers
IF v_line IS NULL THEN
v_out := '';
END IF;
--
RETURN
CASE WHEN in_header IS NOT NULL THEN TO_CLOB('<h3>' || in_header || '</h3>') END ||
v_out ||
CASE
WHEN v_line IS NOT NULL THEN TO_CLOB('</tbody></table><br />')
ELSE TO_CLOB('<p>No data found.</p><br />')
END;
--
EXCEPTION
WHEN core.app_exception THEN
close_cursor(v_cursor);
RAISE;
WHEN OTHERS THEN
close_cursor(v_cursor);
core.raise_error();
END;
One more cool thing about this is that you can define "__style" columns in your views and these values will get applied to the individual cells as style, and not be presented as data. And numbers are automatically right aligned.
SELECT
t.status,
CASE WHEN t.status != 'VALID' THEN 'color: red;' END AS status__style, -- not visible in table, but applied to "status" column
...
FROM t;
In the next part I will cover more APEX related checks and in the part after that I will cover REST services and how you can install this.
If you are keen to explore it right away, all the code is in the CORE_JOBS package.
Comments
Post a Comment