Writing 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

May/June 2014

In the last issue of Oracle Magazine, I offered advice on how best to write PL/SQL code for Oracle Application Express applications. In this article, I take a look at how, when, and where to write SQL statements for those same applications.

I offered three guidelines in the last article that are worth repeating here (and then exploring in the context of SQL, specifically):

• The only SQL statements you should write in the Application Builder (the Oracle Application Express UI for building applications) are queries for populating reports and tables. Even then, you should simplify those queries as much as possible by using 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 (which, for the remainder of this article, I will refer to simply as “application”) to a minimum. Move as much code as possible to PL/SQL packages.

This article zeroes in on that first guideline but that guideline is also closely related to, and implemented through, the other two guidelines.

I will again turn to the PL/SQL Challenge application for examples demonstrating both compliance with and violations of the above guidelines.

Let’s begin by taking a look at how to implement data manipulation language (DML) statements (specifically INSERT, UPDATE, and DELETE) you need to run in response to user input in your application.

It’s certainly easy enough to write an INSERT or UPDATE statement in an Oracle Application Express application. Because PL/SQL natively supports compilation and execution of SQL statements, you simply create a PL/SQL process and then type the DML statement into the appropriate field.

There are very few applications that do not change the contents of database tables in response to user actions, so your application will likely require the execution of many DML statements. I recommend that you never write those statements directly inside the Application Builder. Instead, you should

• Let Oracle Application Express generate as many of your DML statements as possible. For example, if you need to create a page that enables a user to perform DML operations on a table, you can define a region as “Form on a Table for View,” and then Oracle Application Express will implement all the DML for you—using the Automatic Row Processing feature. Adding a column to the table adds a new item to the region, and Oracle Application Express will adjust its DML statements to incorporate this new column.
• Define packaged procedures, put your DML statements inside those procedures, and then call the procedures from within the application’s processes. By doing this, you can more easily change the DML statement (and even add more DML statements, which I will demonstrate), with minimal impact on your application.

One of the most compelling reasons to move your DML to PL/SQL packages is this basic reality of programming: things (and, in particular, transactions) get more complicated over time. What today might be a single insert into table T1 gradually morphs into

And when you think you need to use something in only one place, you then discover you need to use it in a second and third location in your application as well.

So if you write that INSERT INTO T1 in multiple places, you’ll find yourself copying and pasting the expanded transaction into each location when that simple transaction expands to a three-step transaction. The end result is an application that is extremely difficult to debug, manage, optimize, and enhance.

I ran into precisely this issue with the PL/SQL Challenge website, which is built on Oracle Application Express and run by my son, Eli, and me. The main activity on this website is offering quizzes that test knowledge of various Oracle technologies. Users find out how well they did by visiting the Quiz Details page after the quiz has closed and reviewing their performance.

I keep track of each quiz viewing by a player in the qdb_quiz_visits table. So my first iteration of the Oracle Application Express page process on the Quiz Details page simply executed this statement:

INSERT INTO qdb_quiz_visits
           (user_id, quiz_id)
     VALUES (:p659_user_id,
             :p659_quiz_id);

But after implementing this process, I realized that I also wanted to record a viewing of a quiz whenever a player checks out the survey results for that quiz. So off I went to another page and with a quick copy/paste (along with careful review and editing to replace page numbers), a new page process was created:

INSERT INTO qdb_quiz_visits
           (user_id, quiz_id)
     VALUES (:p740_user_id,
             :p740_quiz_id);

Nothing is different except the page number. That’s certainly simple and obvious enough. So why should I bother creating a procedure in a package and putting the INSERT there? If the processing for recording a visit never changed, I suppose there wouldn’t be any reason to do that.

But, as was going to be the case for just about every one of my requirements, I did need to change this processing when I added points to the PL/SQL Challenge website. As encouragement to study, players get five points every time they view a quiz.

The code for assigning points is not much more complicated than the INSERT into the qdb_quiz_visits table:

INSERT INTO qdb_points
            (user_id,
             activity_date,
             activity_id,
             activity_key_value,
             points)
     VALUES ( :p659_user_id,
             SYSDATE,
             'QUIZVIEW',
             :p659_quiz_id,
             5); 

And therein lies the trap, the awful temptation. Having already duplicated the INSERT statement, should I now continue down that path and add this new INSERT statement to both places?

Fortunately, the solution—the way to avoid chaos—is straightforward: hide all the transaction logic behind a single procedure, change all item references to parameters, and then call that procedure wherever it is needed. Here’s the record_quiz_view procedure that hides the inserts for visits and points:

PACKAGE BODY qdb_quiz_mgr
IS
   PROCEDURE record_quiz_view (
      user_id_in   IN PLS_INTEGER,
      quiz_id_in   IN PLS_INTEGER)
   IS
   BEGIN
      INSERT INTO qdb_quiz_visits
             (user_id, quiz_id)
           VALUES (user_id_in, quiz_id_in);
      INSERT INTO qdb_points (user_id,
             activity_date,
             activity_id,
             activity_key_value,
             points)
           VALUES (user_id_in,
             SYSDATE,
             'QUIZVIEW',
             quiz_id_in,
             5);
   END;
END;

And then my process code is slimmed down to nothing more than

BEGIN
   qdb_quiz_mgr.record_quiz_view (
      user_id_in   => :p659_user_id,
      quiz_id_in   => :p659_quiz_id);
END;

Taking the little bit of extra time needed to isolate your DML statements into procedures is like putting money into a retirement plan. You might feel a little pinch right now (a little bit less money, a little bit less time), but in the future, you will have more money and time, because you will be able to modify and enhance your code much more easily.

But don’t worry! You won’t have to wait till you are 65 years of age to feel the benefits of “DML hiding.” That happens almost immediately, because applications change so rapidly and because application developers often don’t get it completely right the first time.

Consider the PL/SQL Challenge record_quiz_view procedure. I soon realized that if players can get five points for every viewing of the quiz details, some players might click that quiz link over and over again, artificially inflating their point total. So I needed to go back to that procedure and give points only for the first viewing on a given day.

I am sure you can all think of similar examples from your own applications. Everything gets more complicated over time; you rarely think of everything up front; you will be going back and changing that code. And when that code contains transactions that can be initiated by a user, you really need to get it right.

I’ve demonstrated the hiding of INSERT statements. With inserts, you are always creating new rows. When executing updates and deletes, however, you modify existing rows, which raises the possibility that an update can be lost. Suppose that Users A and B query the same row of data on a page of the website. Then suppose they both make changes. User A presses the Submit button, and the changes are saved. Then User B presses Submit, and those changes are made—overwriting the changes saved by User A.

If, on a given page, you are not able to take advantage of Automatic Row Processing and instead are writing your own updates and deletes, be sure to take the following steps:

You can access Create Package with Methods on Tables via SQL Workshop -> Utilities -> Methods on Tables, as shown in Figure 1. You can then utilize these subprograms to both hide the DML statements and ensure that user changes are not lost.

Figure 1: Create Package with Methods on Tables location

So I’ve addressed inserts, updates, and deletes. What about that other DML statement SELECT? I have two recommendations for you:

Suppose a PL/SQL Challenge process needed the date on which a player answered a quiz. I could write this block inside the Application Builder:

DECLARE
   l_date   DATE;
BEGIN
   SELECT taken_on
     INTO l_date
     FROM qdb_quiz_answers
    WHERE     user_id = :p659_user_id
          AND quiz_id = :p659_quiz_id;
   IF l_date < SYSDATE - 2
   THEN
      ....
   END IF;
END;

Instead, I should move the query to a function and reduce the process code to

CREATE OR REPLACE PACKAGE BODY
qdb_quiz_mgr
IS
   FUNCTION quiz_taken_on (
      user_id_in   IN PLS_INTEGER,
      quiz_id_in   IN PLS_INTEGER)
      RETURN DATE
   IS
      l_date   DATE;
   BEGIN
      SELECT taken_on
        INTO l_date
        FROM qdb_quiz_answers
       WHERE     user_id = user_id_in
             AND quiz_id = quiz_id_in;
      RETURN l_date;
   END;
END;
/
DECLARE
   l_date   DATE;
BEGIN
   l_date :=
      qdb_quiz_mgr.quiz_taken_on (
         :p659_user_id,
         :p659_quiz_id);
   IF l_date < SYSDATE - 2
   THEN
      ...
   END IF;
END;
/

And now when I need to fetch multiple rows, I’ll use BULK COLLECT and return an array, as in

CREATE OR REPLACE PACKAGE qdb_quiz_mgr
IS
  TYPE answers_t
    IS TABLE OF qdb_quiz_answers%ROWTYPE;
  FUNCTION quizzes_taken_by (
    user_id_in   IN PLS_INTEGER)
RETURN answers_t;
END;
/
CREATE OR REPLACE PACKAGE BODY
qdb_quiz_mgr
IS
   FUNCTION quizzes_taken_by (
      user_id_in   IN PLS_INTEGER)
      RETURN answers_t
   IS
      l_answers   answers_t;
   BEGIN
        SELECT *
          BULK COLLECT INTO l_answers
          FROM qdb_quiz_answers
         WHERE user_id = user_id_in
      ORDER BY taken_on;
      RETURN l_answers;
   END;
END;
/

In a properly normalized relational table design, you will end up with lots of tables and need to do the same joins over and over again. On the PL/SQL Challenge website, for example, each quiz has a topic (the Oracle feature to which it is related) and a quiz format (multiple choice, true/false, and so on). Consequently, I find myself writing queries like this a lot:

SELECT qz.question_text the_question,
       t.title feature,
       qf.text quiz_format
  FROM qdb_quizzes qz,
       qdb_topics t,
       qdb_quiz_formats qf
 WHERE     qz.topic_id = t.topic_id
       AND qz.quiz_format_id =
              qf.quiz_format_id;

My fingers quickly get tired of typing that WHERE clause, and my inner voice never stops badgering me: “Why are you writing this again? Don’t you have something better to do?”

Yes, I do. So instead of writing this three-way join more than once, I create a view:

CREATE OR REPLACE VIEW qdb_quizzes_v
AS
   SELECT qz.question_text the_question,
          t.title feature,
          qf.text quiz_format
     FROM qdb_quizzes qz,
          qdb_topics t,
          qdb_quiz_formats qf
    WHERE     qz.topic_id = t.topic_id
          AND qz.quiz_format_id =
                 qf.quiz_format_id;

Now the query for my report is nothing more than

SELECT * FROM qdb_quizzes_v

And if I discover a week from now that my report needs another column from one of those tables, I can add it to the view, and—voilà!—it will be available in the report.

But watch out! If you create lots of views that contain joins and then join those views to each other, you may end up doing a bunch of unnecessary work. Consider my topics table, used in the qdb_quizzes_v view. A topic is always in a domain (a technology area, such as SQL or PL/SQL). In addition, PL/SQL Challenge offers a set of resources related to a topic, including a link to the Oracle documentation.

So I create this handy view:

CREATE OR REPLACE VIEW qdb_topic_details_v
AS
   SELECT d.domain_name technology,
          t.title feature,
          r.title doc_title,
          r.url   doc_link
     FROM qdb_topics t,
          qdb_resources r,
          qdb_domains d
    WHERE     t.topic_id = r.topic_id
          AND r.resource_type =
                 'FEATURE_DOC'
          AND t.domain_id = d.domain_id;

And I then use it in my Topic Details report, which is perfectly appropriate. But the next day, I need to combine quiz information with the documentation URL and the domain name. What could be easier than joining those two views?

SELECT t.technology,
       q.question_text,
       r.doc_link
  FROM qdb_quizzes_v q,
       qdb_topic_details_v t
 WHERE q.topic_id = t.topic_id;

Yet by doing this, I pay the price of a join with the qdb_topics table twice. Will this cause performance problems? In this simple example, probably not. But as your queries—and views—grow increasingly complex (it is not uncommon to have 10-way joins in views, for example), performance may well degrade. In addition, future generations of programmers will wonder why you didn’t just write exactly the query you needed. “Just lazy” is usually not an acceptable answer.

To conclude, keep your report queries as simple as possible through the use of views, but pay attention and maintain discipline when using those views. Don’t ask the SQL engine to do lots of extra work just so you can avoid writing a query that does what is needed and nothing more.

The ability to create interactive reports is among the best aspects of Oracle Application Express. It not only makes it easier to build powerful reports but also gives users a tremendous amount of control over the content and appearance of those reports. I use them all over the PL/SQL Challenge website and, in particular, have found them to be critical for rankings reports.

Players want to see rankings for a variety of criteria, such as

• By player. Players can learn their own ranking and find out who is top-ranked in PL/SQL quizzes.
• By company. Players can compare the performance of everyone working in the same company or development team.
• By affiliation. Do members of ODTUG score higher than members of DOAG (the German Oracle User Group)?
• By country. Nationalism is alive and well on PL/SQL Challenge!
• By different periods. Rankings are available for a week, month, quarter, year, or lifetime.

The need for ranking across different periods introduces the greatest complexity to these reports, because I populate and rely on a different materialized view for each period. To obtain quarterly rankings, for example, I must query from the mv_qdb_rankings_q view.

Different views initially gave me a headache, because an interactive report cannot be based on a dynamic SQL statement. As a result, I built almost two dozen different reports, as shown in Figure 2.

Figure 2: The 21 initial PL/SQL Challenge interactive reports

I didn’t feel particularly clever or productive while I built these reports, but at least I figured that I would have to build them just once and then not mess with them anymore.

Yeah, right.

That fantasy lasted for a couple of months. By that time, I’d accumulated a solid half-dozen high-priority enhancement requests from players. I then faced the challenge of a very tedious and error-prone job of going through 21 interactive reports and making all the necessary changes.

Surely there is a better way (besides Oracle’s enhancing Oracle Application Express to support dynamic SQL in interactive reports). And there is. You can use a table function, a function that can be called inside the FROM clause of a query, wrapped inside the TABLE operator. The function returns a collection of data, and the TABLE operator converts that collection into rows and columns, which can then be consumed by the report (and anything else that executes a query using that table function).

It is outside the scope of this article to provide a full explanation of table functions; I encourage you to explore them through the link provided at the end of the article, because they are incredibly useful. Here I walk through only the basic steps I took to use table functions to reduce the number of interactive reports for ranking to just two.

Taking a top-down approach, I start with the query I want to be able to execute in the report. If I want to see company rankings for the daily PL/SQL quiz in the fourth quarter of 2013, the query will be nothing more than

SELECT *
 FROM TABLE (
  qdb_ranking_mgr.ir_rankings_tf (
   category_in     => 'COMPANY',
   period_type_in  => 'QUARTERLY',
   competition_in  => 'DAILY PL/SQL QUIZ',
   period_in       => '2013-4'));

I hard-coded the values passed to the function; in the application, these values are selected by the player.

I hope you agree that that is a very simple query—but only because I’ve hidden all the details away in the function.

So now let’s go under the covers of this table function. A table function must return a collection, so I must define a collection type. Because I am returning multiple pieces of information (company name, ranking, percentage correct, and so on), I must create a collection of object types:

CREATE OR REPLACE TYPE
ir_ranking_ot IS OBJECT
(
   period VARCHAR2 (500),
   type_name VARCHAR2 (300),
   score INTEGER,
   pct_correct_answers NUMBER,
   answer_time VARCHAR2 (500),
   overall_rank INTEGER
);
/
CREATE OR REPLACE TYPE ir_rankings_nt
   IS TABLE OF ir_ranking_ot
/

The header of my function now becomes

PACKAGE qdb_ranking_mgr
IS
   FUNCTION ir_rankings_tf (
      category_in      IN VARCHAR2,
      period_type_in   IN VARCHAR2,
      competition_in   IN INTEGER,
      period_in        IN VARCHAR2)
      RETURN ir_rankings_nt;

The implementation of a table function is very application-dependent. My ir_ rankings_tf function contains 230 lines of code that construct a dynamic query based on the parameter values, the most critical of which is the period type, because that determines which materialized view is needed. Here is a highly simplified version of this function, demonstrating the declaration of a local collection, the population of that collection using EXECUTE IMMEDIATE - BULK COLLECT for a very dynamic query, and the return of that collection from the function:

   l_report_data ir_rankings_nt;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT ir_ranking_ot('
      || c_select_list
      || ') FROM mv_qdb_rankings_'
      || period_type_in
      || ' mv,'
      || c_detail_tables
      || ' WHERE '
      || c_where_clause
      || ' GROUP BY '
      || c_period_qualifier_column
      BULK COLLECT INTO l_report_data;
   RETURN l_report_data;
END;

Table functions can be quite complicated, but their complexity is hidden behind the function interface. Developers who need the data returned by a table function can write a simple query that gets them the data without making them sort through the details.

The main benefit I have found for table functions inside Oracle Application Express, though, is to dramatically reduce the number of interactive reports I have to maintain.

SQL statements are the most important parts of any Oracle Database–based application. They are also the parts of your applications that cause the most performance problems and change the most frequently. It is therefore extremely important to establish and follow guidelines about how, when, and where to write SQL statements.

I recommend that you follow these guidelines for SQL in Oracle Application Express:

• Hide complex query logic behind views, and then, when defining reports and tables in the Application Builder, write simple SELECTs against those views.
• Hide all DML statements (inserts, updates, deletes) behind packaged procedures, and then call those procedures from the Application Builder, passing items as parameters.
• Hide all queries you execute inside processes behind packaged functions that return the desired data (in the form of a scalar value, a record, or a collection).
• For queries that drive reports and tables, hide as much complexity as possible behind views, but watch out for misuse of those views that can mean excessive I/O.
• Explore ways to utilize table functions in Oracle Application Express queries to avoid redundancy in interactive reports and other UI elements.

Follow these guidelines, and you will find yourself spending less time maintaining your applications. You will, instead, have more time to think about, design, and implement exciting new features for your users.

CREATE TABLE plch_employees
(
   employee_id   INTEGER,
   last_name     VARCHAR2 (100)
)
/
CREATE TABLE plch_departments
(
   department_id     INTEGER,
   department_name   VARCHAR2 (100)
)
/
BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Shubin');
   INSERT INTO plch_employees
        VALUES (200, 'Gould');
   INSERT INTO plch_employees
        VALUES (300, 'Dawkins');
   INSERT INTO plch_departments
        VALUES (10, 'Analysis');
   INSERT INTO plch_departments
        VALUES (20, 'Discovery');
   COMMIT;
END;
/

BEGIN
   RETURN    'SELECT '
    || CASE :p1000_report_type
       WHEN 'D'
       THEN
          'employee_id, last_name
      FROM plch_employees'
       WHEN 'E'
       THEN
          'department_id, department_name
      FROM plch_departments'
   END;
END;

CREATE OR REPLACE TYPE plch_report_data_ot
   IS OBJECT
(
   report_id INTEGER,
   report_text VARCHAR2 (100)
)
/
CREATE OR REPLACE TYPE plch_report_data_nt
   IS TABLE OF plch_report_data_ot
/
CREATE OR REPLACE PACKAGE plch_pkg
IS
   FUNCTION id_and_name (
      type_in   IN VARCHAR2)
      RETURN plch_report_data_nt;
END;
/
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   FUNCTION id_and_name (
      type_in   IN VARCHAR2)
      RETURN plch_report_data_nt
   IS
      l_return   plch_report_data_nt;
   BEGIN
      CASE type_in
         WHEN 'E'
         THEN
            SELECT plch_report_data_ot (
                      employee_id,
                      last_name)
              BULK COLLECT INTO l_return
              FROM plch_employees;
         WHEN 'D'
         THEN
            SELECT plch_report_data_ot (
                      department_id,
                      department_name)
              BULK COLLECT INTO l_return
              FROM plch_departments;
      END CASE;
      RETURN l_return;
   END;
END;
/

SELECT *
  FROM TABLE (
          plch_pkg.id_and_name (
             :p1000_report_type))
/

SELECT employee_id report_id,
       last_name report_text
  FROM plch_employees
 WHERE :p1000_report_type = 'E'
UNION
SELECT department_id, department_name
  FROM plch_departments
 WHERE :p1000_report_type = 'D';

DOWNLOAD
Oracle Database 12c
Oracle Application Express

TEST your PL/SQL knowledge

READ more Feuerstein

READ more about table functions
Oracle Database PL/SQL Language Reference 12c Release 1 (12.1)

READ more about
PL/SQL
Oracle Application Express