Snippets and Thoughts of One Oracle Developer

A Any developer who has created a RESTful data service in APEX has faced the following issue: you create a service, and it does not work. It is a pain to debug REST services . This is typical of most APEX examples out there. You write your query or copy and paste your code here and there, and then move on. Low code, right? Never mind that it cannot perform proper code validation. Never mind that it cannot detect broken components in the future. As you might know from my previous blueprint articles or presentations, you should encapsulate everything in packages and views. That way, you can properly control your code and make sure your app continues to work. RESTful data services In short, REST services allow you to expose your data (basically SELECT statements) or logic (DML operations or more complex code) to other apps or systems. And it does not have to be a third-party system—you can consume your own services even from the same app. On the service side, you have to de...

A As a continuation of the first article , today I will focus mainly on APEX-related checks. To avoid one huge email, this part covers APEX-related checks, which you can find in the SEND_APPS procedure . Let's get started. I usually ignore all working copies, but even with this approach you might still have some leftover apps in your environment. So, in the CORE_CUSTOM package (under g_apps), you can limit the scope of your apps. This might be interesting: how would you select the same specific apps in multiple queries (without hardcoding the values in more than one place)? You declare the list in the package specification and create a function so you can use it in any statement: g_apps apex_t_varchar2 := apex_t_varchar2( 100, 110, 120, ... ); FUNCTION get_apps RETURN apex_t_varchar2 AS BEGIN RETURN core_custom.g_apps; END; Then you can use it like this: SELECT t.* FROM apex_applications t JOIN TABLE ...

I It sucks. You work your ass off for 10–20 years, you might have been a top performer, you bring many ideas to the company, you devote your free time to your project and team, and one day, they just let you go. Many employees seek an employment contract because of this false sense of security. I got a few opportunities to work for Oracle in the past, but after reading the contract, I had to say no. At least the contract in my country was ridiculous. Companies don't share the same ethics as you. They don't share the same values as you. They are not your family. Do you think your company owes you anything? You got your paycheck, didn't you? Anything else specified in your contract? Companies are built for profit, and large companies have to restructure from time to time to get leaner, remain competitive, and ramp up profits. As a shareholder, you would probably like this. As a top-performing employee being kicked out after 10 years of service, probably not so much. ...

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

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