Coding standards in Oracle Application Express

The problem

With many clients the same problem often arises in different situations, different company contexts but the root of the problem stays the same, whether it's about big data, PL/SQL back-end stored procedures/interfaces, Oracle Forms or Oracle Application Express, and so on... : the coding standards are often horrible. Not two companies use the same standards. What, for instance, is a good prefix for a variable name? l_variable, v_variable, lv_variable, ln_variable, ... or for a cursor. cur_emp, c_emp... a record structure? type_book, book_type, book_typ, ... v_book_type, t_book, ... I have seen them all, even mixed... "Oh, but we have some document somewhere on our network drive describing standards", yeah sure, but how much of it is being applied in the actual code? Application Express, being even to this day still often (unfairly) downgraded as a basic tool to create rapid application (wizards) without complexity... click, click, click, ready, is raising in popularity. It gets a better and better of a reputation nowadays, and it is not just for rapid development. but for complex web applications.

Application Express

Application Express allows you to create whatever web application you can imagine. There is no need to think that you'll better rely on Java NetBeans or Microsoft's ASP.NET framework if you think the business is "too complex", despite it being promoted as a "rapid tool". Nothing is "too complex" for Oracle Application Express. You don't even need a separate license, unlike Oracle Fusion Middleware. The thing is, you'll need coding and working standards and... these are two scenarios I've heard of:

Some companies use MVC, which is, in my humble opinion, a good way of applying patterns in APEX, but just do not over-engineer it. What I mean by that is that it is a great idea to separate business logic and GUI, but keep in mind that your core is still PL/SQL, a 4GL language, not a 3GL like Java or C#. The line between data layer and business logic layer is much thinner in a 4GL like PL/SQL, so separating that entirely may create a lot of unnecessary overloaded functions and procedures. The idea of calling GUI packages from the APEX engine and call BL packages from the GUI packages is great though.

Some companies use no standards, which is in - also my own humble opinion - not recommended .... and that is, by the way, an understatement. 

Many tutorials show you examples of PL/SQL code (anonymous blocks) in APEX page rendering/processing processes. Do not do this; use packages. You'll thank me later. ;-)
In a large environment it is even recommended to use a different schema for each application.

A great way to build your GUI layer is to create a package for each page in the application, e.g. "SCHEMA.GUI_PXXX_CONTEXT" where CONTEXT is a description. APP1024.GUI_P100_INPUT_ORDER is a good name. All PL/SQL code on that page, for processing, rendering, dynamic actions, validations ... can use the package's procedures. The name of this package immediately leads you to page 100 in application 1024. The logical components come in a business layer, for instance: APP1024.BL_MANAGE_ORDERS.

You are not alone

If you have questions while learning or developing in APEX, remember you can always check APEX Discussion Forum, one of the most active communities found on OTN. If you cannot find your question you can post a new question and you'll get it answered in a short time. I am amazed how people in this era of social media still use books and print-outs to find their answer to a question, Use these social media to your advantage!

The importance of collections

Introduction

Today I will be addressing some cool features in PL/SQL that are often not taken advantage of.

Even to this day I often see people write code without using collections, even though they exist already for a long time. Many people learned PL/SQL and moved on with their current knowledge. But time does not stand still and many things have been optimized and changed in the Oracle Database.

Cursor problem

One of the things I (unfortunately) come across too often is creating cursors, explicitly or implicitly. The main rule is to avoid keeping them opened for a long time. Opening and opened cursors when not necessary affects general performance, opened cursors use memory and fetching from them requires I/O as Oracle needs to read data blocks on disk. Nowadays, Oracle is pretty good at creating good optimization plans based on correct statistics, but it can still heavily impact your performance.

Using collections as an alternative

As stated in my previous blogpost, using collections is much faster than writing cursor for loops as it prevents context switching. It also reads more in-memory data instead of disk data. This means it reduces I/O.

Here are 2 examples. The first one uses a "classic" for loop, the second uses a bulk operation.

DECLARE
  CURSOR c_emps
  IS
    SELECT e.ename employee, e.job, m.ename manager, 
           d.dname department, d.loc location
    FROM emp e, emp m, dept d
    WHERE e.deptno = d.deptno
    AND m.mgr(+) = e.empno
    ORDER BY MANAGER NULLS FIRST
  ;
  
  PROCEDURE p(s IN VARCHAR2)
  IS
  BEGIN
    dbms_output.put_line(s);
  END; 
  
  PROCEDURE ph(s IN VARCHAR2) -- fancy heading
  IS
  BEGIN
    dbms_output.put_line(chr(13)||chr(10));
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line('-- '||s||' --');
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line(chr(13)||chr(10));
  END;     
BEGIN

  ph('Using "classic" cursor for loop');
  
  FOR r_emps IN c_emps 
  LOOP 
    p(r_emps.employee||'|'
    ||r_emps.job||'|'
    ||r_emps.manager||'|'
    ||r_emps.department||'|'
    ||r_emps.location);
  END LOOP; -- cursor gets closed after the loop  
END;
/

Example 2.1 - using a "classic" cursor for loop

DECLARE
  CURSOR c_emps
  IS
    SELECT e.ename employee, e.job, m.ename manager, 
           d.dname department, d.loc location
    FROM emp e, emp m, dept d
    WHERE e.deptno = d.deptno
    AND m.mgr(+) = e.empno
    ORDER BY MANAGER NULLS FIRST
  ;
  
  TYPE t_emps_tab IS TABLE OF c_emps%ROWTYPE 
                  INDEX BY PLS_INTEGER;  

  v_emps_tab t_emps_tab;  
  
  PROCEDURE p(s IN VARCHAR2)
  IS
  BEGIN
    dbms_output.put_line(s);
  END; 
  
  PROCEDURE ph(s IN VARCHAR2) -- fancy heading
  IS
  BEGIN
    dbms_output.put_line(chr(13)||chr(10));
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line('-- '||s||' --');
    dbms_output.put_line(rpad('-',length(s)+6,'-'));
    dbms_output.put_line(chr(13)||chr(10));
  END;     
BEGIN

  ph('using bulk and collection');

  OPEN c_emps;
  FETCH c_emps BULK COLLECT INTO v_emps_tab;
  CLOSE c_emps; -- note that the cursor is closed before processing the data
      
  FOR i IN v_emps_tab.first .. v_emps_tab.last
  LOOP
    p(v_emps_tab(i).employee||'|'
    ||v_emps_tab(i).job||'|'
    ||v_emps_tab(i).manager||'|'
    ||v_emps_tab(i).department||'|'
    ||v_emps_tab(i).location||'|');
  END LOOP;
END;
/

Example 2.2 - Using bulk collect

In the simple example above it seems rather cumbersome, but imagine more complex problems, such as opening other cursors inside a cursor for loop, or calling stored procedures that take some time in the for-loop. In the second example your first cursor is already closed, so the golden rule is to close the cursor as soon as possible.

Level up your PL/SQL code

Introduction

The time has come to rejuvenate this blog. And what better start is there than immediately talk about PL/SQL?

Object oriented programming vs. PL/SQL

Comparing them

Years before Java and .NET made their entrance, PL/SQL already had a way of encapsulating data en giving the right responsibilities to your program units using packages. Unfortunately, I often stumble upon code where people do not fully understand the need of a package specification and a package body. They basically put everything in the specification and the body.

Basically, you need to remember 2 things:

Specification: Globally accessible by everyone
Body: Accessible only by the package itself

A rule often used in object oriented programming is that an object has attributes and methods. To access these attributes an external function needs to use a method to retrieve it. PL/SQL provides a way to logically structure your code in a same manner. Comparing OO class components with PL/SQL components in a package:

• public attribute: global package spec variable
• private attribute: global package body variable
• public method: function/procedure declared in spec and body
• private method: function declared only in the body

"But I am not programming object oriented, I am programming PL/SQL"

That is a contradiction! You ARE accessing objects all the time. Within the database everything is an object: A table, a sequence, a trigger... Think of collection types for instance. The type definition is a class and the declaration of it is an object. A record type even has a constructor.You are working with objects. On top of that, many principles in PL/SQL exist before object oriented programming, so why not use their power at its fullest?

Real life experiences

Something I often see is variables declared in the specification. There are exceptions like cursors and user-defined exceptions, or packages without a body used as a global structure in your session, etc... but my general rule is: "don't make it a habit". The problem is that the variables are globally accessible which impairs control of it.

A second thing are packages where all procedures and functions exist in both the spec and the body. If a function or procedure is not used outside the package, you should only put it in the body.

Here is an example of an alternative way on how a global variable inside a package could be accessed:

CREATE OR REPLACE PACKAGE dennis_test
IS   
   FUNCTION get_my_global
   RETURN NUMBER;
   
END dennis_test;
/

CREATE OR REPLACE PACKAGE BODY dennis_test
IS   
   g_my_global NUMBER;
   
   FUNCTION get_my_global
   RETURN NUMBER
   IS
   BEGIN
      RETURN g_my_global;
   END get_my_global;

END dennis_test;
/

Rather than putting it in the specification we use a function to return the body variable. The body variable will have session state just like specification variables. In this example it may look tedious, but this actually makes your codemore transparent  as it progresses and easier to maintain. After all, we will always rely on this package if one wants to acquire the value of the variable, plus we can seperate business logic in the function and calling application or procedure.

Working with context switches

A second topic I would like to talk about are context swtiches. If you have just started to learn PL/SQL chances are high you are unfamiliar with this concept. A context switch is when the parser switches his context mode from PL/SQL to SQL and visa versa. For example, when you open a cursor or when you invoke a DML or DDL (EXECUTE IMMEDIATE) statement. It is important to understand that many repeated context switching attacks the performance of your code. Here is an example you can test yourself.

CREATE TABLE my_logging
  (
     log_date DATE, 
     message VARCHAR2(4000), 
     backtrace VARCHAR2(4000)
  )
;

This testing table is just for logging. It should quickly insert as there are no constraints nor indexes.
Following procedure loops through a cursor returning 100000 rows, and does some intensive logging when looping. + 2 additional logging statements at the beginning and the end of the procedure.

CREATE OR REPLACE PROCEDURE some_procedure
IS
   CURSOR c_cur
   IS
      SELECT 1
      FROM DUAL  
      CONNECT BY ROWNUM <= 100000
   ;
   
   v_logging_enabled BOOLEAN := TRUE;   
   v_cur             c_cur%ROWTYPE;        

   PROCEDURE log_msg (p_msg IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
   
      IF v_logging_enabled THEN
         INSERT INTO my_logging(message, backtrace, log_date)
         VALUES (p_msg, dbms_utility.format_error_backtrace, sysdate);
         
         COMMIT;                  
      END IF;
      
   END log_msg;
    
BEGIN
   
   log_msg('START');
   
   FOR v_rec IN c_cur
   LOOP      
      log_msg('..10: do intersting stuff');
      
      -- ... interesting stuff happens here
      
      log_msg('..20: another set of interesting stuff');
      
      -- ... interesting stuff happens here
      
      log_msg('..30: end of loop');
   END LOOP;   

   log_msg('END');

END some_procedure;

Our procedure has a big problem. It has 300002 (yes, three hundred thousand and two) context switches. Each time the log_msg procedure is called. This heavily impacts its performance. We can test this:

declare
   v_time NUMBER;
begin
   v_time := dbms_utility.get_time;
   some_procedure;
   v_time := (dbms_utility.get_time - v_time) / 100;
   DBMS_OUTPUT.PUT_LINE ( 'log time without bulk (300002 context switches) takes [' || v_time || '] seconds.' );
end;

And here is my output:

log time without bulk (300002 context switches) takes [28.74] seconds.

28.74 seconds is quite a while, but then again it needs to swtich its context from PL/SQL to SQL 300002 times!

We can write exactly the same procedure using a BULK insert. We stock our messages in a collection and persist the elements only at the end. This way, only the persistence itself has a context switch...

CREATE OR REPLACE PROCEDURE some_procedure_2
IS
   CURSOR c_cur
   IS
      SELECT 1
      FROM DUAL  
      CONNECT BY ROWNUM <= 100000
   ;

   TYPE v_log_tab_type IS TABLE OF my_logging%ROWTYPE INDEX BY PLS_INTEGER;
   
   v_logging_enabled BOOLEAN := TRUE;   
   v_cur             c_cur%ROWTYPE;
   v_i               PLS_INTEGER := 1;
   v_log_tab         v_log_tab_type;

   PROCEDURE log_msg (p_msg IN VARCHAR2)
   IS
   BEGIN
   
      IF v_logging_enabled THEN
         v_log_tab(v_i).message := p_msg;
         v_log_tab(v_i).log_date := sysdate;
         v_i := v_i + 1; 
                  
      END IF;
      
   END log_msg;
   
   PROCEDURE persist_msgs
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
   
      IF v_logging_enabled THEN
         FORALL i IN 1 .. v_log_tab.COUNT
            INSERT INTO my_logging(message, backtrace, log_date)
            VALUES (v_log_tab(i).message,dbms_utility.format_error_backtrace, v_log_tab(i).log_date);
             
         v_log_tab.DELETE;
         v_i := 1;
      END IF;
      
      COMMIT;     
   END persist_msgs;   
BEGIN
   v_i := 1;
   
   log_msg('START');
   
   FOR v_rec IN c_cur
   LOOP      
      log_msg('..10: do intersting stuff');
      
      -- ... interesting stuff happens here
      
      log_msg('..20: another set of interesting stuff');
      
      -- ... interesting stuff happens here
      
      log_msg('..30: end of loop');
   END LOOP;   

   log_msg('END');
   persist_msgs;

END some_procedure_2;

Again, we can test this code:

declare
   v_time NUMBER;
begin
   v_time := dbms_utility.get_time;
   some_procedure_2;
   v_time := (dbms_utility.get_time - v_time) / 100;
   DBMS_OUTPUT.PUT_LINE ( 'log time with bulk (1 context switch) takes [' || v_time || '] seconds.' );   
end;

And here is my output:

log time with bulk (1 context switch) takes [5.71] seconds.

This procedure only takes 5.71 seconds instead of 28.74 seconds, and the result is the same!

We can conclude that BULK operations are a very interesting and useful way to optimize your PL/SQL code. There are exceptions but it is always good practice to make the least possible context switches.