Snippets and Thoughts of One Oracle Developer

S So I just published two Claude Code plsql-formatter and sql-formatter skills. The first one handles packages, procedures, functions, and triggers. The second handles the SQL side: standalone queries, any SQL embedded inside PL/SQL and views. Together they cover everything that lands in a real project. I split them into two skills because that is how Claude routes, by intent. Two skills, two trigger sets, no token waste on rules that don't apply. The Problem SQL Developer's built-in formatter is configurable, but every developer has a slightly different preferences and not everything can be customized there as you want. The result on a shared repo is a diff full of whitespace noise, columns realigned three different ways, WHERE clauses re-indented on every commit. So you spend time in code review fixing formatting instead of logic. Eventually you settle with a style which you don't really like. A skill fixes this differently. Instead of running a tool that rew...

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

D During my preparation for the amazing APEX Alpe Adria conference in Maribor I have realized that I no longer consider some of my previous recommendations valid, hence there is a time for an update. You can download my presentation at AAA24 review article. Here are the original Blueprint articles published 01/2022: Part 1 about the obvious things + backend and Part 2 focused on APEX . I will repeat some thoughts from these two previous articles and put all things here, so you can refer just to this one new article. I will also add few new thoughts. And it will be longer than usual (around 10 pages actually). Clean code Clean code is a code that can be understood correctly with minimal effort in minimal time. It is not some utopia and you don't have to go to any extremes. In fact you can do just minimal changes in your applications for a huge improvements. How you done things might be obvious to you now, but not the future you, not to mention your colleagues. So if ...

O On my projects I usually use a lot of grids to customize the app - handle the users, roles, navigation, LOVs, settings... so I create a page, which lists all IG regions and performs some checks, to make sure grids are setup in a way I like it. If you hover on the orange warning icon it will actually show you the message describing what is wrong. You can see the list of grids, source, target (procedure handling the grid changes and table behind) and some other checks. Things I check: region source type (T) - I prefer to use the view, so I check for that and for the name, if the view contains same page id grid process/handler (T) - Invoke API is my new best friend, PL/SQL code is fine... I also check if the process name match the procedure name and if the package name contains same page id passed/submitted page items (P) - I also check if I pass every page item which I use in the view, to make sure that I have proper grid refresh authorizations - check authoriz...

I Invoke API is available since APEX 22.2, since all my projects upgraded recently I can finally start using it. For me it was one of the greatest news introduced in APEX 22.2, not much invisible, but allowing me to shrink down Embedded Code report significantly. If you are into classic passing of arguments, checkout this Invoke API article from Jon Dixon . I will be talking about a more minimalistic approach. I am a huge fan of storing logic in PL/SQL packages and views. This Invoke API feature allows me to just reference the procedure same way as I can reference view in grid, as a reference, not as a written code which need to be parsed and maintained. And this is not limited just for grids. What I did in the past created view and assigned it to the grid, preferably via object reference (region source type = table/view) created procedure to handle the changes created caller to pass arguments from grid to the handler hoped that arguments wont change - I wou...

I In previous article I have showed you a way how you can manage the whole application security through user defined roles (and I mean defined by business users not by developers). Today I will show you how you can achieve the same with interactive grids. How you can use the same IS_USER role to manage which DML actions will be allowed on the grid for specific application role. I will be using the Tasks application and demonstrating it there. The security for grids is assured on multiple levels: AUTH_PAGES - protects access to the pages and page visibility in navigation (protecting access to the whole page with grid) AUTH_COMPONENTS - protects access to the individual page components (protecting access to the grid region, grid handler, specific columns, related DAs...) AUTH_TABLES - protects against unauthorized DML actions inside of the TAPI procedures, but also on a grid through dedicated IS_USER_C|U|D autorization schemes AUTH_PROCEDURES - protects access ...

I I was working on a project, where we have multiple pages with multiple regions, multiple business roles and very complex requirements what to show and allow to do to these specific roles. So we implemented one authorization scheme per one business role and map them to regions, items, buttons, columns, processes... As the requirements grew, we had to add merged auth schemes like role1_or_role2, role1_except_role2 to cover overlaps, since APEX allows us to have just one autorization scheme asssigned. Doing this with 2-3 roles is fine, doing that with 10+ complex roles is a nightmare especially when the requirements are changing all the time. Well, I wish I had used the component based authorization from the start. And this is not a new feature, this is available since APEX 5! You basically create an authorization scheme, set the caching to Always or Per Component. When you do this and select type PL/SQL fn. returning boolean, APEX will pass extra values to your function: RETURN is...

I I was experimenting lately with page items and how to setup APEX page (preprocessing section) in more cleaner, reusable and generic way. I also wanted to move the logic from APEX to PL/SQL packages to fulfil my MVC vision (to keep as much as possible code in PL/SQL, not in APEX). So I made this up. For every page in application I have created a package, "P0" for page zero, "P100" for page 100... For 100 pages I would create 100 packages, but that is still better then have the logic scattered on individual pages. And most of the times I have to create packages anyway because I need a place where to store form/grid handlers and page related logic. Then I have created INIT_DEFAULTS procedures in each of these packages. And in this procedure I setup page items and run the pre-rendering processes. Since we are talking about packages, I can reuse other procedures and function and limit the amount of copy pasted code. I use my wrapper around these session state call...

E Everyone should know the power of WITH clause these days. For me this is a great way how to have more readable views and with some magic it also allows me to have dynamic views (well, the views returning different rows based on page items). You are asked to show a monthly based calendar on a page (and let's skip the Calendar region for demo purposes) based on a page item. Here is how you can utilize WITH clause on a regular query (returning a calendar data for a whole year): WITH days AS ( SELECT TRUNC(SYSDATE, 'Y') + LEVEL - 1 AS day, TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'IYIW') AS week, TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'MM/YYYY') AS month FROM DUAL CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - TRUNC(SYSDATE, 'Y') ) SELECT d.month, d.week, MAX(DECODE(TO_CHAR(d.day, 'DY'), 'MON', TO_NUMBER(T...

U Usually I see (and do) evaluations at the start of the procedure. When you check for multiple things, it can get lengthy and the reusability of the code is very low. Does this look familiar? DECLARE in_user_id CONSTANT VARCHAR2(30) := 'USER_NAME'; BEGIN -- check inputs IF in_user_id IS NULL THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MANDATORY'); END IF; -- IF LENGTH(in_user_id) < 3 THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MIN_LENGTH'); END IF; -- IF NOT REGEXP_LIKE(in_user_id, '@') THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_FAILED'); END IF; -- continue with your code NULL; END; / Imagine this instead: DECLARE in_user_id CONSTANT VARCHAR2(30) := 'USER_NAME'; BEGIN -- check inputs assert.is_not_null('USER_ID_MANDATORY', in_user_id); assert.is_false('USER_ID_MIN_LENGTH', LENGTH(in_user_id) ...

I I saw over 100 APEX apps written by different people over the years. But I never saw an app which would leverage the object references. I believe these apps are out there hidden and protected as top secrets. Story behind Imagine you get a brief to fix charts on a Dashboard page. You open a page and there are 12 chart regions. All of them have 100+ lines query as a source with a lot of joins and multiple levels of subqueries. Just to read one query will take o lot of time. Then you have 11 more. In the end and after few hours you will realize that the code for this charts is basically the same. And now what? Fix the query on 12 regions one by one and move to another task? I asked 12 APEX senior developers how they would proceed with the queries. 9 would copy paste the query between regions 1 would use APEX_COLLECTION 1 would PL/SQL function returning query 1 would use view (finally) Yes, you can do simple changes directly on APEX components, but in many times this w...

W We all have to concatenate strings and varibles and you have to admit that it will get ugly very quickly. Luckily someone created a sprint function we know from other programming languages. It is called APEX_STRING.FORMAT . Lets jump to the example first: What would you rather to read and maintain, before or after version? If you like the before version, you can stop reading. And if you are still here then we can be friends :-) You can pass up to 20 arguments and you have two options how to reference them. You either use "%s" and then the values are replaced in the same order as you passed them. Or you can use numbers like "%0..19" and reuse them however you need. I usually stick with "%s" unless I need to replace same value multiple times. There is a nice side effect of this type of concatenation. It is easy to run. Just copy paste the code, replace "!" with nothing, replace "%" with "&" and you can ac...

I If you have a constants in a package you might want to expose them to your SQL queries. You wont able to access them from your SQL statements (for example from views or adhoc queries) unless you create a function for each of them and call that function instead of the constant. With the generator your functions will be consistent, typo free and with correct datatypes. Here is a generator for that: SET SERVEROUTPUT ON SIZE UNLIMITED DECLARE in_package_name CONSTANT VARCHAR2(30) := 'CONSTANTS'; -- source package with constants in_constant_prefix CONSTANT VARCHAR2(30) := ''; -- process just constants starting with this prefix in_fn_prefix CONSTANT VARCHAR2(30) := 'get_'; -- add optional prefix to functions in_generate_body CONSTANT BOOLEAN := TRUE; -- switch between spec and body generator BEGIN FOR c IN ( SELECT LOWER(s.name) AS packag...