Object referencing in APEX

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 wont be enough and you would have to copy paste the query to the editor and back, probably multiple times. And with bind variables it will be even more fun. How can you test a subquery on APEX component? How can you check the explain plan? How much time will you loose by going back a forth between APEX and your editor? How do you know if the similar query doesn't exists somewhere else?

So if you would build your app in this style, you could print a book for long winter evenings just from the Embedded code report. And you can just wish a good luck to a guy who will be working on the page after you. That is the reality.

It reminds me a good old days when we mixed the PHP code with MySQL do often, that the code was unreadable and very hard to maintain. I would never go back to this mess.

Pros and cons

Lets look at some benefits of puting queries directly to APEX components:

• it is quick
• you can export page and not care about relevant database objects
• it is easier to deploy these pages

And some disadvantages:

• it is more difficult to read page changes, specially on more complex pages and queries
• it is more difficult to understand what is going on
• it prevents code reuse, because you can't easily see what is on the other components
• you will quickly lose track of dependencies
• when something breaks, user will be the first one who notice (see compilation warnings section)
• it will quickly become maintenence nightmare

Solution

With the object reference when you open the page you will instantly see that these 12 charts use the same view and just the WHERE conditions are different. Then you open the view in your editor and you can start fixing the query. On one place. Without the need to copy paste the query to the editor. Without the hassle with bind variables. Without worrying that you break something else.

I use this story as a great example, because even more stubborn developers can see my point. But often they struggle to grasp the concept that pages grow and you are suppose to do this from the start for every component. If you start the page by saying it is just this one region, just this query, you are doomed.

I like to keep things consistent and on one place. You have views and packages in database, there is no need to store business logic inside APEX. And there is more...

Compilation warnings

This is the best advantage of using views and packages. I change object in database and I recompile invalid objects. I can immediately see that I did broke something (or not). How can you check this in APEX?

Imagine you changed a view, function, or basically any object in database. If you are using the oldschool approach and you have the queries all over the place, how can you check that these pages/regions/components are still working properly? Will you search APEX for all these object names you changed and for the dependent objects? Will you test the whole app manually?

With the reference mode you still can get broken pages, you will still have to synchronise your regions after a column changes. You will still have to adjust your custom PL/SQL handlers manually (or not in APEX 22.2 with the Invoke API Processes feature). And your deployment might be more difficult (or not, check out the OPY project), specially if you really don't have any automated CI/CD pipeline.

Wrap up

A year ago, a colleague of mine, 65+ years with zero Oracle experience joined our APEX team and started working on our apps as a developer. He picked it up incredibly fast, just in a month maybe two. Guess how the app was written? He is working on my apps and he is working on some legacy apps. Guess which apps he likes more?

So if you can, try this out. I think you will fall in love with this approach sooner or later.