Oracle APEX Best Practices: Make the most of Oracle Apex with this guide to best practices. It will help you look at the bigger picture when building applications and take more elements into account such as security and performance.

Have you ever found yourself in a situation where a user contacts you and reports a problem, which you can't reproduce on your own environment? What would really be helpful is knowing how your code was being used, with which values your stored procedures were called, and which code path the user took to get to the situation they found themselves in before they contacted you.

There is a way to know all this information, and the way to get it is by properly instrumenting your code. Instrumenting your developed code means putting in debug statements throughout. In these debug statements, there should be the information you need to track the execution of the developed code. Also, it should be complete with timestamps and other meaningful information.

Tyler Muth has written an excellent utility to help you with instrumenting your code. This package is called Logger and can be used to instrument your code—not only your APEX code, but also your database stored procedures. One of...

Properly designing the data model plays a crucial part in the success of your application. An application can have a really good-looking interface, but when the performance is very poor the users still won't be happy with the application. To ensure your user has an overall positive experience, the application needs to be visually attractive and responsive to the actions that the user carries out.

Analytic functions were introduced quite a long time ago—Oracle 8.1.6 Enterprise Edition in around 1999—yet they are still quite unknown to a lot of developers.

With analytic functions you can retrieve data from more than one row at the same time without the need for a self join. You can create a ranking based on a value within a group of values. They are not easy to use, but once mastered, analytic functions can make your life a lot easier. With analytic functions, you can create the overviews that the customer may want within a few lines of code.

Creating multilevel totals with aggregate functions might not be the first thing you think about. This has been a capability of the aggregate functionality for quite some time.

The purpose of the GROUP BY clause is to group rows together, based on the columns specified. But with aggregates, you don't always need to specify columns. When you want a grand total, you can omit the GROUP BY clause altogether:

SQL> select sum (sal)
  2    from emp
  3  /

  SUM(SAL)
----------
     29025

Omitting the GROUP BY clause leads to a grand total, but you can also use an empty set in the GROUP BY clause:

SQL> select sum (sal)
  2    from emp
  3   group by ()
  4  /

  SUM(SAL)
----------
     29025

On line 3 the empty set is used, denoted by the opening and closing braces ().

There is a lot more about aggregates, such as GROUPING SETS, ROLLUP, and CUBE.

Because we are creating a document management system, there are going to be documents stored inside the database, obviously. Searching through these documents is a must-have feature. Oracle supports this kind of functionality in the form of Oracle Text functionality.

In order to work with the Oracle Text feature, we need a special type of index—a context index—on our documents tables, more specifically on the column that stores the document:

SQL> create index doc_index on documents (document)
  2  indextype is ctxsys.context
  3  parameters ('SYNC (ON COMMIT) TRANSACTIONAL')
  4  /

Index created.

The index type that is needed for Oracle Text is CTXSYS.CONTEXT (line 2). On line 3, we specify that we want this index to be refreshed when a commit is issued.

In this chapter, we looked at some of the lesser-known features of SQL and PL/SQL, but there are many, many more. Thorough knowledge of SQL and PL/SQL is essential to an APEX developer.

We started by examining a method to instrument your database code. This enables you to keep track of the code path and the arguments that are passed down.

Next different options were investigated to have efficient lookup tables for your application. Creating a proper physical data model by using the right types of database object where appropriate will also contribute to a successful application and a better user experience.

Analytic and aggregate functions leverage the functionality that SQL has to offer out of the box. Using these functions, you can create statements that are both elegant and perform well.

When you have programs that take a long time, and you don't want to have the user wait for the response, you can offload these programs to the background. We saw an example on using DBMS_JOB to do...