How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (6)

Last time in “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (5)”, I told you about Git, Subversion, Maven and Flyway.

In this final article, I will discuss the following tools & methods: Oracle SQL Developer, utPLSQL, SonarQube, Perl, Ant and DevOps.

Oracle SQL Developer

Oracle SQL Developer is a free, integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.

I use Oracle SQL Developer as my main database development environment but I will only discuss its External Tools section here because I needed that to launch Maven builds on a Citrix server. There the only way to launch Maven was via Oracle SQL Developer and the Java executable. The command line was forbidden, a security measure I can understand and approve of but as a developer I needed a way to launch the installation of my applications.

In the examples I assume that an environment variable MAVEN_HOME is defined that points to the Maven home environment.

External tools

The External Tools section allows you to define your own tool (command) that can be invoked either contextual (right click in an editor) or from a menu.

Go to the Tools menu and click External Tools...: 

The tool Maven scm:update install

This command is used to update a project from a source repository and install the package contents created by Maven in the local Maven repository. I have a Maven Oracle build project stored in a (private) GitHub source repository that is used by all other Maven Oracle application projects. This Maven Oracle build project needs to be installed (locally) before it can be referenced as a dependency by an application project.

I intend to create a public open-source Maven Oracle build project later on. This project contains:

• a Maven POM file for installing database migrations via Flyway
• a Maven POM file for exporting and importing APEX applications
• Ant and Perl scripts to support the above functionality

To create this external tool use executable java and these arguments:

-classpath "${env:var=MAVEN_HOME}\boot\plexus-classworlds-2.6.0.jar" "-Dclassworlds.conf=${env:var=MAVEN_HOME}\bin\m2.conf" "-Dmaven.home=${env:var=MAVEN_HOME}" "-Dlibrary.jansi.path=${env:var=MAVEN_HOME}\lib\jansi-native" "-Dmaven.multiModuleProjectDirectory=${file.dir}" org.codehaus.plexus.classworlds.launcher.Launcher clean scm:update scm:status scm:check-local-modification install

The Run directory is:

${file.dir}

This shows it all: 

The next window allows you to define a meaningful name in the Display window: 

And finally you have to use these settings in Integration: 

This will allow you to invoke a tool (Maven command) from a POM file using the Right Click menu:

The tool Maven db

This command installs the database migration scripts using Flyway.

As you may remember from  “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (2)” the project layout has a db folder below the project root folder. Hence the invocation of Maven is 1 level lower than the root as you can see from -Dmaven.multiModuleProjectDirectory in the following arguments:

-classpath "${env:var=MAVEN_HOME}\boot\plexus-classworlds-2.6.0.jar" "-Dclassworlds.conf=${env:var=MAVEN_HOME}\bin\m2.conf" "-Dmaven.home=${env:var=MAVEN_HOME}" "-Dlibrary.jansi.path=${env:var=MAVEN_HOME}\lib\jansi-native" "-Dmaven.multiModuleProjectDirectory=${file.dir}/.." org.codehaus.plexus.classworlds.launcher.Launcher compile flyway:repair flyway:migrate ${promptl:=option1} ${promptl:=option2}

The tool Maven apex

This command allows you to export or import an APEX application.

Same level as db since the apex folder has the same height as db, but other goals:

-classpath "${env:var=MAVEN_HOME}\boot\plexus-classworlds-2.6.0.jar" "-Dclassworlds.conf=${env:var=MAVEN_HOME}\bin\m2.conf" "-Dmaven.home=${env:var=MAVEN_HOME}" "-Dlibrary.jansi.path=${env:var=MAVEN_HOME}\lib\jansi-native" "-Dmaven.multiModuleProjectDirectory=${file.dir}/.." org.codehaus.plexus.classworlds.launcher.Launcher compile ${promptl:=option1} ${promptl:=option2}

External Tools defined

You will see this after you have defined the External Tools: 

utPLSQL

In the Java developer community it is best practice to have unit tests. Unfortunately that is not the case in the Oracle developer community. Created some 20 years ago by Steven Feuerstein, the third version of utPLSQL is really very useful. Easy to use and the documentation is excellent. It even integrates with Maven thanks to the utPLSQL-maven-plugin. And there is a plugin too for SQL Developer, see Running utPLSQL Tests in SQL Developer.

utPLSQL also has a code coverage report.

Do not wait any longer, just unit test it!

I add unit test code to my packages and the test code is conditional, guarded by a global configuration package header with some constants defined for testing and debugging. This way it is easy to de-activate testing code in production (where utPLQSL should not be installed).

The following sections show an example.

Conditional compilation

As I said above the test code is conditional (only active when utPLSQL is installed in a development environment) and I use Oracle conditional compilation for that.

I will quote from this article from oracle-base.com about conditional compilation:

Conditional compilation allows PL/SQL code to be tailored to specific environments by selectively altering the source code based on compiler directives. It is considered a new feature of Oracle 10g Release 2, but is available in Oracle 10g Release 1 (10.1.0.4.0).

Compiler flags are identified by the “$$” prefix, while conditional control is provided by the $IF-$THEN-$ELSE syntax.

$IF boolean_static_expression $THEN text
  [ $ELSIF boolean_static_expression $THEN text ]
  [ $ELSE text ]
$END

Configuration package

This package specification defines the boolean constants that can be used by conditional compilation as you will see in the two following sections:

create or replace package cfg_pkg
is

c_debugging constant boolean := false;
  
c_testing constant boolean := true;

end cfg_pkg;

A package specification with a function/procedure to unit test

A unit test procedure must be prefixed by:

--%test

as you can see below:

create or replace package data_api_pkg authid current_user is

/**
 * Raise a generic application error.
 *
 * @param p_error_code  For instance a business rule
 * @param p_p1          Parameter 1
 * @param p_p2          Parameter 2
 * @param p_p3          Parameter 3
 * @param p_p4          Parameter 4
 * @param p_p5          Parameter 5
 * @param p_p6          Parameter 6
 * @param p_p7          Parameter 7
 * @param p_p8          Parameter 8
 * @param p_p9          Parameter 9
 */
procedure raise_error
( p_error_code in varchar2
, p_p1 in varchar2 default null
, p_p2 in varchar2 default null
, p_p3 in varchar2 default null
, p_p4 in varchar2 default null
, p_p5 in varchar2 default null
, p_p6 in varchar2 default null
, p_p7 in varchar2 default null
, p_p8 in varchar2 default null
, p_p9 in varchar2 default null
);

$if cfg_pkg.c_testing $then

--%suite

--%test
procedure ut_raise_error;

$end

end data_api_pkg;

You can use any name you like for the unit test procedure but adding ut_ as prefix is the convention of the previous versions of utPLSQL.

Please note that the unit test procedure ut_raise_error is only defined when cfg_pkg.c_testing is true due to the conditional compilation construction.

A package body with a function/procedure to unit test

create or replace package body data_api_pkg
is

procedure raise_error
( p_error_code in varchar2
, p_p1 in varchar2 default null
, p_p2 in varchar2 default null
, p_p3 in varchar2 default null
, p_p4 in varchar2 default null
, p_p5 in varchar2 default null
, p_p6 in varchar2 default null
, p_p7 in varchar2 default null
, p_p8 in varchar2 default null
, p_p9 in varchar2 default null
)
is
  l_p varchar2(32767);
  l_error_message varchar2(2000) := "#" || p_error_code;
begin
$if cfg_pkg.c_debugging $then
  dbug.enter($$PLSQL_UNIT || '.RAISE_ERROR');
  dbug.print
  ( dbug."input"
  , 'p_error_code: %s; p_p1: %s; p_p2: %s; p_p3: %s; p_p4: %s'
  , p_error_code
  , p_p1
  , p_p2
  , p_p3
  , p_p4
  );
  dbug.print
  ( dbug."input"
  , 'p_p5: %s; p_p6: %s; p_p7: %s; p_p8: %s; p_p9: %s'
  , p_p5
  , p_p6
  , p_p7
  , p_p8
  , p_p9
  );
$end

  if p_error_code is null
  then
    raise value_error;
  end if;

  <<append_loop>>
  for i_idx in 1..9
  loop
    l_p :=
      case i_idx
        when 1 then p_p1
        when 2 then p_p2
        when 3 then p_p3
        when 4 then p_p4
        when 5 then p_p5
        when 6 then p_p6
        when 7 then p_p7
        when 8 then p_p8
        when 9 then p_p9
      end;

    l_error_message := l_error_message || "#" || l_p;
  end loop append_loop;

  -- strip empty parameters from the end
  l_error_message := rtrim(l_error_message, '#');

  raise_application_error(c_exception, l_error_message);
  
$if cfg_pkg.c_debugging $then
  dbug.leave;
exception
  when others
  then
    dbug.leave_on_error;
    raise;
$end  
end raise_error;

$if cfg_pkg.c_testing $then

procedure ut_raise_error
is
  l_msg_exp varchar2(4000 char);
begin
  for i_idx in 1..6
  loop
    begin
      case i_idx
        when 1
        then l_msg_exp := '#'; raise_error(null);             
        when 2
        then l_msg_exp := '#abc'; raise_error('abc');
        when 3
        then l_msg_exp := '#def#p1'; raise_error('def', p_p1 => 'p1');
        when 4
        then l_msg_exp := '#ghi##p2'; raise_error('ghi', p_p2 => 'p2');
        when 5
        then l_msg_exp := '#jkl#########p9'; raise_error('jkl', p_p9 => 'p9');
        when 6
        then l_msg_exp := '#MNO#a#b#c#d#e#f#g#h#i'; raise_error('MNO', p_p1 => 'a', p_p2 => 'b', p_p3 => 'c', p_p4 => 'd', p_p5 => 'e', p_p6 => 'f', p_p7 => 'g', p_p8 => 'h', p_p9 => 'i');
      end case;
      raise program_error;
    exception
      when others
      then
        case i_idx
          when 1
          then
            ut.expect(sqlcode, 'sqlcode '|| i_idx).to_equal(-6502);
            
          else
            ut.expect(sqlcode, 'sqlcode '|| i_idx).to_equal(c_exception);
            ut.expect(sqlerrm, 'sqlerrm '|| i_idx).to_be_like('%'||l_msg_exp||'%');
        end case;
    end;
  end loop;
end ut_raise_error;

$end

end data_api_pkg;

Please note that the unit test procedure ut_raise_error is only defined when cfg_pkg.c_testing is true due to the conditional compilation construction. And the debugging sections with package dbug are also only active when the cfg_pkg.c_debugging constant is true.

SonarQube

A small introduction:

SonarQube empowers all developers to write cleaner and safer code. Join an Open Community of more than 200k dev teams. Enhance Your Workflow with Continuous Code Quality & Code Security Thousands of automated Static Code Analysis rules, protecting your app on multiple fronts, and guiding your team.

Your teammate for Code Quality and Code Security

So where does SonarQube fit in the DevOps picture? I consider it a complement of utPLSQL and it covers the code quality and security part where utPLSQL covers the functionality part of your application.

So SonarQube allows you to make code quality and security part of your development and test cycle and it will warn you when there is a defect of any kind. If you use it with a build tool like Maven it will stop the build when there is a defect since that is treated as an error. So you have immediate feedback.

As shown by the SonarQube PL/SQL rules this static code analysis tool improves the quality of code. It is a lint like tool. Other PL/SQL lint checkers are described in this article from Steven Feuerstein.

Here are some rules:

• Inserts should include values for non-null columns
• Predefined exceptions should not be overridden

But I really question this rule:

• Quoted identifiers should not be used

SonarQube allows you to define your own rules and can be run by Maven using SonarScanner for Maven.

Perl

A long time personal favorite since I started developing. Maybe not as sexy as Python but it works for me. I use it as soon as I need to process Operating System tasks like working with files and so on.

Some people may prefer Bash (Unix/Linux) or Powershell (Windows) scripting but I like my development tools to be platform independent so I can use them in any project.

Ant

Another long time favorite that I use for executing simple tasks on the Operating System where dependencies are necessary. I use Ant to export and import APEX applications. It invokes the Oracle SQLcl client with the appropriate switches. Ant integrates well with Maven due to the Apache Maven AntRun Plugin.

SQLcl is a new Java-based command-line interface for Oracle Database.

By Jeff Smith, September/October 2015

DevOps

DevOps is a set of practices that combines software development (Dev) and IT operations (Ops). It aims to shorten the systems development life cycle and provide continuous delivery with high software quality. DevOps is complementary with Agile software development; several DevOps aspects came from the Agile methodology.

DevOps, Wikipedia

From the same Wikipedia page I repeat this list of DevOps processes:

• Coding – code development and review, source code management tools, code merging.
• Building – continuous integration tools, build status.
• Testing – continuous testing tools that provide quick and timely feedback on business risks.
• Packaging – artifact repository, application pre-deployment staging.
• Releasing – change management, release approvals, release automation.
• Configuring – infrastructure configuration and management, infrastructure as code tools.
• Monitoring – applications performance monitoring, end-user experience.

I have shown you all the tools and techniques you may need plus all the pitfalls you may encounter while setting up DevOps (or Continuous Integration / Delivery / Deployment). I have used Jenkins in the past.

The following table shows the match between DevOps processes and tools:

Please note that I have not mentioned Jenkins as tool above. Jenkins is the tool to invoke Maven on a remote integration server. Locally you do not need Jenkins: you just run the appropriate Maven commands from the command line.

An important aspect that I may not have mentioned before is that a Maven build fails as soon there is an error. The same is true for a Jenkins build that fails if one of its build steps fails.

Conclusion

This was the final article on “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end)”. I hope to have the inspiration, time and support to write a book about it some day…

I hope you have enjoyed it!

All articles in this serie