Snippets and Thoughts of One Oracle Developer

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...

I If, for whatever reason, you decide you don't want the identity columns anymore and want to switch back to the classic sequences, this is for you. Here is the plan for how you can achieve it: get identity columns (identify tables and identity sequences) create new sequences for every table with "{TABLE_NAME}_ID" name get maximum value for each column to use it as a start value for new sequence create backup for objects source code replacements (in annotations) remove identity from all columns add new sequences as a default values fix source code (triggers, packages...); this is solved just partially To identify columns with identities: SELECT s.sequence_name, c.table_name, c.column_name, a.annotation_value AS old_seq_backup FROM user_sequences s LEFT JOIN user_tab_cols c ON c.data_default_vc LIKE '"' || USER || '"."' || s.sequence_name || '".nextval' LEFT JOIN user_annotat...

M Most of the projects I've worked on have this in common: "Release 1.0" as a version number. Only some projects have incremented this number, while keeping the same format. I don't like these artificial numbers, their informative value is close to zero. What I prefer instead is using the date and time of the last change. That can be easily automated, and you instantly know from which date and time the app is. For example, instead of "Release 1.0" you would have "2025-08-13 1.10.27", which consists of the date (YYYY-MM-DD) and time (HH24:MI), with "1." in the middle to make it look less like a time and more like an old-school version number. So, the app with the version number "2025-07-02 1.8.4" was changed on July 2nd at 08:04. Clear? As a developer, you can simply run a script when creating your patch, or even after deploying the app to the target environment, or schedule it to run as a job every hour or so. Since the...

T Thanks to the extended namespaces introduced in Oracle 12c (12.2 actually), you don't have to worry about making constraint names fit into 30 characters. You don't have to create aliases which nobody understands anymore — you can use longer names, you just have to fit within 128 characters. Here are the patterns I like: primary key - {TABLE_NAME}_PK unique key - {TABLE_NAME}_UQ (if there is only one), {TABLE_NAME}_UQ_{COLUMN_NAME} (if there are more of them) foreign key - {TABLE_NAME}_FK_{COLUMN_NAMES} (I used to do {TABLE_NAME}_FK_{REFERENCED_TABLE} but sometimes I have to reference the same table multiple times and then this does not work) not null - {TABLE_NAME}_NN_{COLUMN_NAME} (much better than the generated name) check - {TABLE_NAME}_CH_{COLUMN_NAME} (using just the first column name, so it might not work every time) And here is a 200+ lines long nifty script that will rename your constraints according to these patterns. You can also use prefi...

S Sometimes, when I'm working on a new package, it's difficult to keep the specification updated to reflect changes in arguments. Adding new procedures becomes tedious, and when I change the formatting, it's time-consuming to update the specification accordingly. When I reformat a legacy package, I usually copy the whole body into the specification and keep only the declarations. But that also takes some effort. So I created this nifty script, which generates a specification from your package body. It keeps everything that exists in your current package spec before the first procedure or function (like constants, exceptions, comments...). Everything else is replaced. It does not support forward declarations, and your private procedures will get exposed (which is a good thing, because you should be using the ACCESSIBLE BY clause instead). Here is the script: DECLARE in_package_name CONSTANT VARCHAR2(30) := '&PACKAGE_NAME.'; -- v_head...

S Sometimes, you have to modify all triggers, if you were for example using SYSTIMESTAMP and now SYSDATE is enough. To modify all triggers by hand might be a lot of work (it is not just about modifing, you also have to compile all of them in the database). So let's write a script for that (replace LOCALTIMESTAMP with SYSDATE in all triggers), but you can easily modify it to whatever you need to change: DECLARE v_query VARCHAR2(32767); v_modified BOOLEAN; BEGIN FOR c IN ( SELECT t.name, ROWNUM AS r#, d.referenced_name FROM user_source t JOIN user_dependencies d ON d.name = t.name AND d.name LIKE '%' AND d.type = t.type AND d.type = 'TRIGGER' AND d.referenced_type = 'TABLE' WHERE t.line = 1 ORDER BY 1 ) LOOP v_query :=...

A Another issue I faced on each project is data type inconsistency. In this older article I mentioned how to fix data types mismatches on keys . Today, I show you a script, which fix it for you (based on a column name match). Let's explore this issue on the audit columns (who created or updated the row and when). To check the audit columns for user: SELECT c.column_name, c.data_type, c.char_used, c.data_length, c.nullable, c.data_default_vc, COUNT(*) AS count_tables FROM user_tab_cols c JOIN user_tables t ON t.table_name = c.table_name WHERE c.table_name LIKE '%' AND c.column_name LIKE 'CREATED_BY' GROUP BY c.column_name, c.data_type, c.char_used, c.data_length, c.nullable, c.data_default_vc ORDER BY 1, 2, 3, 4; Let's check the audit column for date. This gets more trickier, because we might not have consistent names. To keep the changes low, I am not changing the column names, just ...

I In this article, I will show you a script that deletes all rows from all tables for a specific tenant. You don't need to be familiar with your schema or manually piece together queries to perform the deletion. You can also easily modify this script to delete any entity, not just a tenant. There are several ways to achieve this, and I will show you two of them: To find (even compound) references to a specific table, you can use this query: SET DEFINE ON SELECT r.table_name, c.column_name, r.constraint_name, k.column_name AS primary_column FROM user_constraints r JOIN user_constraints p ON p.constraint_name = r.r_constraint_name AND p.constraint_type IN ('P', 'U') JOIN user_cons_columns c ON c.constraint_name = r.constraint_name AND c.table_name = r.table_name AND c.column_name NOT IN ('TENANT_ID') -- we should match on position JOIN user_constraints s ON s.table_name = r...

T The space on free OCI can sometimes be a bit limiting, especially if you're using table partitions. Even an empty partition consumes 8 MB, and if you have partitions per tenant in every table — and over 100 tables — it can quickly grow with each new tenant. So here is the script which helps you to remove empty partitions and also rename the partitions so you can easily identify specific tenants: DECLARE in_table_like CONSTANT VARCHAR2(128) := '%'; in_partition_prefix CONSTANT VARCHAR2(128) := '__P'; -- v_found PLS_INTEGER; v_high_value VARCHAR2(4000); v_new_name VARCHAR2(128); BEGIN FOR c IN ( SELECT t.table_name, t.partition_name, t.high_value FROM user_tab_partitions t WHERE t.table_name LIKE in_table_like ORDER BY 1, 2 ) LOOP v_found := NULL; v_high_value := c.high_value; -- LONG convers...