Snippets and Thoughts of One Oracle Developer

I I'm using Git and the APEX Deployment Tool , so I very rarely lose code because I effortlessly export any changes and commit them right away. Mostly, I'm on the other side, breaking the code of others, those who still live in the stone age and keep everything only in the database. A friend of mine, Rafal , wrote about a great tool called Loki based on APEX. It might be a bit heavy, and I also have some customers who really don't want an APEX app to manage object locking. So here is mine lightweight implementation. All you need is: schema level DDL trigger CORE_LOCKSMITH table CORE_LOCKS to manage the locks small CORE_LOCK package to not have code in the trigger and for the extra actions (like extending or releasing the locks) Simple process you want to edit package, so you open it and compile it (right away, not after you work on it for half day) this will trigger the DDL event which will be catched by our AFTER DDL schema level trigger ...

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