Writing PL/SQL in Oracle Application Express

Minimize the code you write, be careful where you put it, and relocate it to packages as much as possible.

By Steven Feuerstein

March/April 2014

Oracle Application Express and PL/SQL: what a great combination—and what a sense of déjà vu!

It’s very easy for me to deeply appreciate Oracle Application Express, due to my history with Oracle application development technology. I joined Oracle in 1987 and stayed for five years. During that time, PL/SQL was first made available in SQL*Forms V3 and then more generally as a programming language for applications based on Oracle Database.

PL/SQL was such a joy to use in SQL*Forms V3. Rather than working with the clumsy and limited step-based triggers of SQL*Forms V2, I could write real algorithms by using a real language. I could more quickly develop applications that could handle more-complex logic, and I was able to improve the usability of the forms. I could maintain my applications much more easily, now that I could read the logic in PL/SQL blocks.

Yet even with (or because of?) that relatively early version of PL/SQL and the relatively primitive nature of SQL*Forms V3, I ran into all the same challenges I encounter now with Oracle Application Express.

Today I work with Oracle Application Express to implement the PL/SQL Challenge website (plsqlchallenge.com), which offers daily, weekly, and monthly quizzes on PL/SQL, SQL, deductive logic, and database design. I mostly write the back-end code (PL/SQL packages), and my son, Eli, has primary responsibility for the website itself, but we move back and forth between these two sides of the application.

We have been able to build—and maintain—a robust, feature-rich site with a very small team. We have, however, also struggled with ways to improve the maintainability of our codebase to make it easier to enhance the site and support more PL/SQL Challenge players. This article shares what we’ve learned about ways to write PL/SQL in the Oracle Application Express environment.

In the world of Oracle Application Express, developers spend a lot of time pointing at certain options with a cursor and clicking choices, thereby specifying many behaviors for an application without having to write any (or much) code. It’s a true rapid application development (RAD) environment and a very easy way to build applications, but it doesn’t have to be this way. Working in a different environment, you could write (and maintain) an application entirely in code, using PHP or Java along with the usual cast of characters: JavaScript, Cascading Style Sheets (CSS), and HTML.

There’s a certain comfort in that: you have total control, and anything and everything that happens in the application is reflected right there in the characters you typed—and can change.

The downside of 100 percent coding for applications is that developers are generally much less productive and the resulting code is much less structured than application code developed with a RAD framework, such as Oracle Application Express (or Oracle JDeveloper). A framework means that you cannot help but build your application according to the rules and formats supported by the framework—so anyone else trained in Oracle Application Express has a pretty good chance at maintaining what you built.

Therefore, I am happy to trade off some loss of control for dramatic improvements in productivity and maintainability. Yet I have also found that unless I am careful about how I leverage Oracle Application Express, I can still create big problems for those who will come after me in maintaining the PL/SQL Challenge application.

First, let me make clear: this is not an article about general best practices for Oracle Application Express development. I am not nearly expert enough with the product to offer such guidance. Instead, I will focus on the key challenges Oracle Application Express developers will face when it comes specifically to writing the PL/SQL code in their applications.

These developer challenges can generally be summed up as follows: there’s too much code inside the Oracle Application Express application and not enough code in PL/SQL packages and views.

When a framework can do a lot of work for you (that is, generate code instead of requiring you to write all of it), it is very tempting to want to spend too much time in that framework. I click this button, I select that HTML widget to use here, I write some code for an item’s condition, I write some more code for a page-level process (an Oracle Application Express element that is used to execute PL/SQL code and control rendering of the page, among other things), and so on.

Before you know it, you are spending almost all of your time inside Oracle Application Express, copying code from one process to another, writing query after query, updating this table, inserting into that table.

The result? Lots of repetition (the same query in multiple places or the same rule written out in multiple conditions) and, soon, a sense of being lost in your own application.

I suggest following these guidelines when writing PL/SQL code in Oracle Application Express applications:

• The only SQL statements you should write in the Application Builder (the Oracle Application Express UI for building applications) are queries to populate reports and tables. Even then, you should simplify those queries as much as possible through use of views and, for some complex scenarios, table functions.

• Avoid repetition of code whenever possible. This advice is not specific to Oracle Application Express; it is one of the most important guidelines for high-quality programming overall.

• Keep the volume of code inside the Oracle Application Express application to a minimum. Move as much code as possible to PL/SQL packages.

This article focuses on specific techniques and features you can leverage in Oracle Application Express to improve the maintainability of your PL/SQL code, with a particular focus on ways to avoid repetition. My next article will discuss how to manage SQL statements in your Oracle Application Express application and, in particular, how and when to use table functions.

Oracle Application Express makes it possible to design applications while minimizing the volume of code that developers have to write. Still, unless you are building a very simple application, you will write a whole lot of SQL and PL/SQL logic. I recommend that you keep the code that’s in Oracle Application Express to an absolute minimum and move as much of that logic as possible into packages and views. (In my next article, I will address views and how they relate to when and where to write SQL statements in Oracle Application Express applications.)

To make it easier (possible) to maintain your application in Oracle Application Express, you need to avoid repetition and hide complexity. Clearly, if the same complex expression is copied and pasted into several items to control their conditional display, it is going to be hard to remember to change that expression in all locations when a bug is discovered.

Wouldn’t it be so much better if all that functionality were moved to a function and then the function were simply called as needed?

Let’s take a look at an example: Page 659 is the PL/SQL Challenge (plsqlchallenge.com) Quiz Details page. You visit this page after you’ve taken a quiz or that quiz is over. Players must be restricted in what they can see here, depending on their quiz status. In addition, this page is used by reviewers, and if you are a reviewer, you’ll see a Reviewer Actions toolbar, as shown in Figure 1. For nonreviewers, this and other areas on the screen should not be displayed.

Figure 1: Quiz Details page with Reviewer Actions

For this Quiz Details page in the Oracle Application Express Application Builder, I set the condition on the Reviewer Actions region as shown in Listing 1, and it works exactly as intended. But as I continue to test the page, I realize I need this same expression to control the display of an item in another region. No problem. A quick copy/paste applies the condition. Oh, wait! There’s another item needing the same condition. Problem solved again, with another simple, quick copy/paste.

Code Listing 1: Condition on the Reviewer Actions region

DECLARE
   l_dummy   CHAR (1);
BEGIN
   SELECT 'x'
     INTO l_dummy
     FROM qdb_question_reviews qr, qdb_domain_reviewers_v dr
    WHERE     dr.user_id = :ai_user_id
          AND dr.domain_reviewer_id = qr.domain_reviewer_id
          AND qr.question_id = :p659_question_id;
   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
   WHEN TOO_MANY_ROWS
   THEN
      RETURN TRUE;
END;

Why shouldn’t I copy and paste the logic? It’s not like it’s going to change.

Oh really? The very next day, I realize that the situation is a bit more complex: besides the reviewers, the author of a quiz should be able to see that toolbar and the other items.

That’s easy enough. So I go back to the Reviewer Actions region and change the condition to the code in Listing 2. And then I copy/paste this new solution into those other items in other regions on other pages—and hopefully I don’t miss any.

Code Listing 2: Condition on the Reviewer Actions region, take 2

DECLARE
   l_dummy       CHAR (1);
   l_author_id   PLS_INTEGER;
BEGIN
   SELECT ‘x’
     INTO l_dummy
     FROM qdb_question_reviews qr, qdb_domain_reviewers_v dr
    WHERE     dr.user_id = :ai_user_id
          AND dr.domain_reviewer_id = qr.domain_reviewer_id
          AND qr.question_id = :p659_question_id;
   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      SELECT qu.author_id
        INTO l_author_id
        FROM qdb_questions qu
       WHERE qu.question_id = question_id_in;
      RETURN l_author_id = :ai_user_id;
   WHEN TOO_MANY_ROWS
   THEN
      RETURN TRUE;
END;

But what if I do miss any of those items? And what about my productivity? And, last but far from least, what happens when others have to maintain this page? How will they ever have any idea of the number of places they need to change when they fix or upgrade the application?

There has got to be a better way, and there certainly is: move that logic to one of my packages, hide it behind a function, and then call that function in the Application Builder.

So I open the qdb_review_mgr package and add this IS_REVIEWER_OR_AUTHOR function to the package specification:

FUNCTION is_reviewer_or_author (
   user_id_in       IN INTEGER,
   question_id_in   IN INTEGER)
   RETURN BOOLEAN

The implementation is precisely what I had in the condition on the Reviewer Actions region, plus the function header.

Then I create an item named P659_IS_REV_OR_AUTH, along with an On Load - Before Header process to set the value of that item, containing the code in Listing 3.

Code Listing 3: P659_IS_REV_OR_AUTH/On Load - Before Header process code

BEGIN
   CASE
      WHEN    qdb_review_mgr.is_reviewer_for_quest_domain (
                 :ai_user_id,
                 :p659_question_id)
           OR qdb_content.question_author_id (:p659_question_id) =
                 :ai_user_id
      THEN
         :p659_is_rev_or_auth := qdb_config.c_yes;
         :p659_show_answers := qdb_config.c_yes;
      ELSE
         :p659_is_rev_or_auth := qdb_config.c_no;
   END CASE;
END;

An experienced Oracle Application Express developer might suggest that I should instead create a computation to specifically set the value of an application or a page item. I would agree, except that I also set the value of another item in that PL/SQL block, determined by the same logic as that of P659_IS_REV_OR_AUTH. Because a computation is item-specific, a process makes more sense in this context.

And now my condition for the region and all items is nothing more than

:P659_IS_REV_OR_AUTH = qdb_config.c_yes

(Yes, that’s right. I even try to avoid hard-coding Y in my Oracle Application Express code.)

By moving this logic to my packaged function, I not only greatly clean up the logic on page 659 and reduce the chance of introducing bugs in the future but I also increase the percentage of reusable code in my application. The IS_REVIEWER_OR_AUTHOR function can be (and is) called from other packaged subprograms and other pages in the application.

Joel Kallman, director of software development for Oracle Application Express, also suggests that developers can achieve further simplification and deeper reuse by defining this logic as an authorization scheme in Oracle Application Express and then applying that scheme wherever needed. One benefit of doing this is that you can quickly identify, in the Application Builder, all the places where a particular authorization scheme is used. An authorization scheme isn’t dramatically different from the conditional display of something (as implemented above). But authorization schemes typically are used for, well, authorizations, which is the purpose of this function.

Apply this same process rigorously throughout your application and across all developers on your team, and you will soon find that you have reached a kind of critical mass of reusable code: new subprograms, new conditions, and new processes that require little more than calls to previously defined functions and procedures.

You must also be very careful about how you pass information from the application to the back-end subprogram. The Oracle Application Express V function makes it possible to obtain the value of an item from its name. So I could have written the IS_REVIEWER_OR_AUTHOR function as shown in Listing 4.

Code Listing 4: The parameterless function relying on the V function

FUNCTION is_reviewer_or_author
   RETURN BOOLEAN
IS
   l_dummy       CHAR (1);
   l_author_id   PLS_INTEGER;
BEGIN
   SELECT 'x'
     INTO l_dummy
     FROM qdb_question_reviews qr, qdb_domain_reviewers_v dr
    WHERE     dr.user_id = v ('ai_user_id')
          AND dr.domain_reviewer_id = qr.domain_reviewer_id
          AND qr.question_id = v ('p659_question_id');
   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      SELECT qu.author_id
        INTO l_author_id
        FROM qdb_questions qu
       WHERE qu.question_id = v ('p659_question_id');
      RETURN l_author_id = v ('ai_user_id');
   WHEN TOO_MANY_ROWS
   THEN
      RETURN TRUE;
END;

And then I could call the function from Application Builder as

IF qdb_review_mgr.is_reviewer_or_author
THEN

Code Listing 5: IS_REVIEWER_OR_AUTHOR function as parameterized function

CREATE OR REPLACE FUNCTION is_reviewer_or_author (
   user_id_in          INTEGER,
   question_id_in   IN INTEGER)
   RETURN BOOLEAN
IS
   l_dummy       CHAR (1);
   l_author_id   PLS_INTEGER;
BEGIN
   SELECT 'x'
     INTO l_dummy
     FROM qdb_question_reviews qr, qdb_domain_reviewers_v dr
    WHERE     dr.user_id = user_id_in
          AND dr.domain_reviewer_id = qr.domain_reviewer_id
          AND qr.question_id = question_id_in;
   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      SELECT qu.author_id
        INTO l_author_id
        FROM qdb_questions qu
       WHERE qu.question_id = user_id_in;
      RETURN l_author_id = user_id_in;
   WHEN TOO_MANY_ROWS
   THEN
      RETURN TRUE;
END;

With a parameterized function, Oracle Application Express can check at the time of compilation of any process or condition that calls this function whether the item name is misspelled (and therefore undefined). And reading a call to this function inside the application, anyone can also see immediately that the function relies on the values of the user ID and the question ID, making it much easier to maintain the code.

Again, the back end of the PL/SQL Challenge application, which consists of more than 1,900 procedures and functions collected into 40 packages, offers an excellent example. One of the main packages, qdb_content (QDB, the application prefix for PL/SQL Challenge, stands for Quiz Database), manages content for quizzes. This package contains 180 procedures and functions (and the package body contains more than 6,800 lines of code, bringing to mind another best practice and technique covered in “PL/SQL Enhancements” in the September/October 2013 issue of Oracle Magazine: when packages get too large, you should break them up to make them more manageable. Oracle Database 12c offers the ACCESSIBLE_BY clause to make it easier to do this).

Put your own code into many tightly focused packages whose names reflect that focus, making it easy to find existing subprograms and to figure out where to put new ones. Moving as much logic as possible into packages also greatly increases the likelihood that you can make a basic change in logic or fix a bug with nothing more than a package recompilation.

You all know that you should never hard-code literal values in your applications, because they are likely to change at some point. Inside your PL/SQL code, a fine way to avoid hard-coded literals is to declare a constant and then “hide” the value behind the name of the constant.

In Oracle Application Express, although you can still reference that constant in many places, you should also consider using substitution strings, static variables (constants) defined at the application level. The value of a substitution string can be referenced throughout the application with this syntax:

&STRING_NAME.

In PL/SQL Challenge, for example, Eli and I have built a generic platform for taking quizzes. Because a different usage of this platform will not necessarily be called PL/SQL Challenge, we use a substitution string for some of the appearances of that name, as in

Welcome to the &PLCH_QUIZ_NAME.!

Careful use of substitution strings will reduce the cost of maintenance of your own application and make it more flexible. Another side benefit of using substitution strings is that they can be modified by a developer during the installation of an application that has Supporting Objects. (Supporting Objects is a feature of Oracle Application Express, but I cannot cover it in this article, due to space constraints.)

If you have foreign keys defined on your tables, you almost certainly will be using lots of lists of values (LOVs)—from which the user picks a valid choice for a column or an item—in your application. The good news is that Oracle Application Express offers a built-in List of Values widget that can be reused throughout your application. The bad news is that unless you are careful, you can easily end up with lots of duplication in those LOVs.

I ran into this problem in the PL/SQL Challenge application. The application offers quizzes on multiple “domains” (SQL, PL/SQL, and so on), each of which has its own set of versions. When you define a quiz, you specify the minimum version for that quiz. Players can also filter quizzes by minimum version, so the LOV is needed on different pages and on each page, the domain ID is used in the WHERE clause of the LOV query, as in

SELECT version_name display,
       domain_version return_value
  FROM qdb_domain_versions
 WHERE domain_id = :p2010_domain_id;

So off we go, merrily building our application, being very productive, as is generally the case with Oracle Application Express. And one day I’m looking through our list of LOVs and discover that we have three different LOVs that all seem to offer the same list but for different pages, as shown in Figure 2.

Figure 2: Three versions by domain LOVs

Just looking at the names of those LOVs makes me shudder. They are the same except for the page number. As a developer trained in data normalization, I think that it just doesn’t seem right for a name to contain information about the location in which it was used. Yet each LOV has to be filtered by a different page item, so I figure I have no choice. Or do I?

I open the source queries for these LOVs in an attempt to gain clarity and find that I am, indeed, correct. The only differences between these LOVs is the use of a different page item in the WHERE clause, as shown in these queries for the 2010 and 2051 LOVs:

SELECT version_name display_value,
       domain_version_id return value
  FROM qdb_domain_versions
 WHERE domain_id = :p2010_domain_id
ORDER BY 1
SELECT version_name display_value,
       domain_version_id return value
  FROM qdb_domain_versions
 WHERE domain_id = :p2051_domain_id
ORDER BY 1

Even though I am far from being an Oracle Application Express expert, it’s immediately clear to me that it really would be better if page-specific item references were not stuck inside a named LOV. These LOVs do not “live” inside a single page and can, theoretically, be used across an entire application.

Yet with a page reference in an LOV, its usage is restricted or—even worse—error-prone. Surely there must be a way to normalize the LOV.

And there is: I can define an LOV with a dynamic query. So I create a new LOV that contains neither the page number in its name nor a hard-coded page item reference in the query, as shown in Figure 3. Now the only requirement for using this LOV is that the current page in the Oracle Application Express application must contain an item of the form

Figure 3: New LOV using dynamic query

:PNNNN_domain_id

where NNNN is the page number. So it’s not quite completely generic, but it solves my problem. We can then remove several LOVs, replacing them with a single LOV that can be used on any page.

Goodbye to (the worst of the) hard-coding; goodbye, repetition!

I learned a few lessons from this experience:

• Assume that the Oracle Application Express team has thought of such issues.

• Check the documentation and especially inline help whenever you are doing something that seems suboptimal.

• Always take the most soft-coded, dynamic approach possible.

It is very common, for example, to write logic to specify when an item, region, or other UI element should be rendered on the application page. Oracle Application Express offers numerous predefined condition types and validations from which to choose, not write.

Suppose you want to make sure that a user enters only digits and no characters in a field. I am certain that you could build a function to check whether a string is a valid number (hint: use TO_NUMBER). I am even more certain, however, that when you define the item, you can create a String Comparison Validation for that item, specifying that it be a number.

Should an item be displayed only when another item is not NULL? Then define a condition on that item and choose the predefined “Value of Item / Column in Expression 1 is not NULL” rather than writing code such as

:P203_my_item IS NOT NULL

By using a built-in element of Oracle Application Express, you not only increase your productivity but also reduce the code volume (and the cost of maintenance) and almost always improve the performance of your application, because this logic is “burned into” the Oracle Application Express framework.

It’s nice when you can write code so that you like it and other developers can maintain it, but what really matters is the impact on your users.

Oracle Application Express enables you to build applications rapidly and to change those applications over time to keep up with changing user requirements. If you are not careful about how you write the code inside those applications, it will be increasingly difficult to keep up with user requests.

Conversely, if you are careful to avoid repetition and hide all the complex logic in your application behind carefully designed package interfaces, it will be easier to satisfy user requirements and therefore easier to keep users happy.

In my next article, I will explore the challenge of how best (and when) to write SQL statements in the Application Builder.

BEGIN
   RETURN     :p100_favorite_flavor = 'CHOCOLATE'
          AND :p100_contains_nuts = 'Y';
END;
/

CREATE OR REPLACE FUNCTION is_nutty_chocolate
   RETURN BOOLEAN
IS
BEGIN
   RETURN     v ('p100_favorite_flavor') = 'CHOCOLATE'
          AND v ('p100_contains_nuts') = 'Y';
END;
/

BEGIN
   RETURN is_nutty_chocolate;
END;
/

CREATE OR REPLACE FUNCTION is_nutty_chocolate (
   fav_flavor_in      IN VARCHAR2,
   contains_nuts_in   IN VARCHAR2)
   RETURN BOOLEAN
IS
BEGIN
   RETURN fav_flavor_in = 'CHOCOLATE'
          AND contains_nuts_in = 'Y';
END;
/

BEGIN
   RETURN is_nutty_chocolate ( :p100_favorite_flavor,
                              :p100_contains_nuts);
END;
/

CREATE OR REPLACE PACKAGE favorite_mgr
IS
   FUNCTION is_nutty_chocolate (fav_flavor_in      IN VARCHAR2,
                                contains_nuts_in   IN VARCHAR2)
      RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY favorite_mgr
IS
   FUNCTION is_nutty_chocolate (fav_flavor_in      IN VARCHAR2,
                                contains_nuts_in   IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN     fav_flavor_in = 'CHOCOLATE'
             AND contains_nuts_in = 'Y';
   END;
END;
/

BEGIN
   RETURN favorite_mgr.is_nutty_chocolate (
             :p100_favorite_flavor,
             :p100_contains_nuts);
END;
/

DOWNLOAD
Oracle Database 12c
Oracle Application Express

TEST your PL/SQL knowledge

READ more Feuerstein

READ more about
Oracle Database 12c
PL/SQL
Oracle Application Express