Programming Languages & Frameworks

by John Hendrix

This is the second and final part of a two-part series on using Node.js, node-oracledb, sails-oracle-sp, and the Sails.js model-view-controller (MVC) framework to expose PL/SQL stored procedures as REST APIs and WebSockets.

Table of Contents
Introduction
Setting Up the System
Taking the Example Application for a Spin
Oracle Database Exceptions
Separation of Concerns
Conclusion
About the Author

Introduction

The first part of this series discussed sails-oracle-sp's theory of operation in detail. This part walks through setting up a sample implementation that is composed of two parts: a RESTful web service that concurrently presents WebSocket counterparts to the REST APIs and an Angular.js single-page application as the user interface. The web service uses Oracle Database's sample HR schema's EMPLOYEES and DEPARTMENTS training tables.

This example is a working system that demonstrates

• Presenting the employee/department data to users
• Creating, updating, and deleting employee/department data
• Presenting through the UI in real time—_without polling the database_—changes that are made to the employee/department tables
• Catching exceptions raised by the stored procedures and presenting the user with error notifications

Setting Up the System

This demo depends on having Oracle Database and access to the HR schema, Sails.js, Node.js, and so on. This section describes the steps for preparing a Linux system for this demo.

Install Oracle VM VirtualBox

Download and install Oracle VM VirtualBox on your host system.

Download the Oracle Database 12_c_ Virtual Machine

An Oracle Database 12_c_ virtual machine (VM) is provided in an Open Virtual Appliance (OVA) file. An OVA file is essentially an archive that includes a disk image and other supporting files that are suitable to be imported into Oracle VM VirtualBox as a VM. This VM is based on Oracle Linux 7 and has Oracle Database 12_c_ Release 1 Enterprise Edition installed.

Download the OVA here. Launch it by double-clicking it and then import it into Oracle VM VirtualBox.

Once you've imported the VM and launched it, you need to configure it.

Enable the VM's bidirectional clipboard. To do this, in the VM's menu, select Devices->shared clipboard->bidirectional.

Set the VM's memory for 3 GB. In the VM's menu, select Machine->settings-> system.

Install the Oracle VM VirtualBox Guest Additions. Listing 1 shows useful information pertaining to this VM.

Database Information:
Oracle SID : cdb1
Pluggable DB : orcl
ALL PASSWORDS ARE : oracle
The Linux username and password are oracle.

Listing 1: Oracle Database instance information for this VM

Install Node.js

Install Node.js by performing the following the steps to download Node.js, update the npm package management system, and install Bower. (Bower is a package manager specialized for client-side dependencies.)

First, run the commands shown in Listing 2:

$ sudo su -

# yum update -y

[massive yum chatter redacted]

# mkdir Downloads

# cd Downloads

# wget https://nodejs.org/dist/v4.3.2/node-v4.3.2-linux-x64.tar.gz

# tar xzf node-v4.3.2-linux-x64.tar.gz

# cd node-v4.3.2-linux-x64

Listing 2: Downloading node.js

Install Node.js in the /usr/local directory. Then use following command to copy the files to appropriate subdirectories under /usr/local:

# for dir in bin include lib share; do cp -par ${dir}/* /usr/local/${dir}/; done

Listing 3: Copying files to subdirectories

To verify the installed version of Node.js, issue the following command. The exact installed version might vary.

# node --version

v4.3.2

Listing 4: Verifying the installed version of Node.js

Installing Node.js provides npm. npm is frequently updated and Node.js might not provide the most recent version of npm. To obtain the latest stable version, issue the following command:

# npm install npm -g

Listing 5: Installing npm

To verify the installed version of npm, issue the following command. The installed version might vary from what is shown here.

# npm --version

3.8.3

Listing 6: Verifying the installed version of npm

To install Bower, issue the following command:

# npm install -g bower

Listing 7: Installing Bower

Install the Oracle Database Instant Client's Basic and SDK RPMs

The VM comes with Firefox preinstalled. Point Firefox to the URL shown in Listing 8 and download both the Oracle Instant Client "Basic" and Oracle Instant Client "SDK" RPMs for x86_64 Linux. Firefox will place them into /home/oracle/Downloads. Create /home/oracle/rpms and move them into this newly created directory.

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html#ic_x64_inst

Listing 8: Download location for the Oracle Instant Client RPMs

As root, issue the commands shown in Listing 9 to install these RPMs.

# cd /home/oracle/rpms

# rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

# rpm -ivh oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

Listing 9: Commands for installing the Oracle Instant Client RPMs

Node-oracledb depends on access to the Oracle Instant Client libraries.

For the sake of simplifying the demo installation procedures, the Sails.js instance is run by the oracle user. This is unnatural because the oracle user typically does not run non-Oracle Database applications. This is why the oracle user does not have the LD_LIBRARY_PATH environmental variable defined by default. Consequently we must add the line specified in Listing 10 to .bashrc. Sails.js cannot launch without this modification to the LD_LIBRARY_PATH environmental.

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Listing 10: An environment variable required to run Sails.js as the oracle user

As an aside, in this special case, using LD_LIBRARY_PATH is not strictly necessary because Oracle Instant Client was installed using the rpm utility. The node-oracledb installer recognizes this configuration and links using the rpath linker options. (See node-oracledb's installation manual for rpath information and tips for modifying rpath.) That said, setting LD_LIBRARY_PATH will be necessary when installing from Oracle Instant Client zip files or for users who are linking to ORACLE_HOME libraries.

Install git

Our working example of sails-oracle-sp is in a git repository. You will need to install git to clone this repository to this VM. As root, issue the command shown in Listing 11 to install git.

# yum install git

Listing 11: The command for installing git

Install the sails-oracle-sp Example

Listing 12 shows the steps necessary to download and install the remaining components as the oracle user. These components include Sails.js, sails-oracle-sp, and node-oracledb.

The npm package management system for Node.js facilitates the installation, upgrading, removal, and so on of node modules. It is conceptually similar to Linux package managers such as yum and rpm.

A node module is a composed of a directory hierarchy of files. When npm is installing a node module's package, it downloads the module's package from its repository and copies it to a location in the file system. Every node module typically has dependencies on other node modules. These dependencies are specified in a file named package.json, which is one of the files in the node module's package. In the use case shown in Listing 12, you use git to initially install sails-oracle-sp's node module into a directory. Then you cause npm to automatically install the node modules on which sails-oracle-sp depends by issuing the npm install command.

Afterward, npm will download and install all of the node modules specified in sails-oracle-sp's package.json file. npm then resolves any dependencies required by each newly installed node module by recursively scanning its package.json, downloading any uninstalled node modules, and so on, until the last required node module has been installed.

Bower is installed by npm in the use case shown in Listing 12.

The bower.json file is Bower's counterpart to npm's package.json. A bower.json file is also part of the sails-oracle-sp package. The bower install command shown in Listing 12 processes sails-oracle-sp's bower.json file in a manner similar to the way npm processes the package.json file.

Run the commands shown in Listing 12 to complete the installation of sails-oracle-sp and its dependencies. Here is a description of what the commands and system output mean:

• Line 1: This point begins just after you install git, as shown in Listing 11. You are still logged in as root in the current shell. Now you exit the shell.
• Lines 2 and 3: These lines verify that the following steps will be executed as the oracle user.
• Lines 4 and 5: These commands install sails-oracle-sp's node module and change to its directory.
• Line 6: This command installs the node modules on which sails-oracle-sp depends. This step also installs Bower.
• Line 7: This line installs Angular.js, toastr.js, and the various other bootstrap components on which the example's front end depends. The example UI's pop-up notifications are implemented via toastr.js.

1. # exit

2. $ whoami

3. oracle

4. $ git clone https://github.com/nethoncho/sails-oracledb-sp-example.git

5. $ cd sails-oracledb-sp-example

6. $ npm install

7. $ bower install

Listing 12: Steps for installing the Sails.js example

Now edit $HOME/.bashrc and add the line shown in Listing 13. Later, the sails lift command will fail unless this path is added to the PATH environment variable.

export PATH=$PATH:$HOME/sails-oracledb-sp-example/node_modules/.bin

Listing 13: Path to Sails.js binary

Configure the Sails.js Database Connection

Sails.js database connections are specified in config/connections.js. The connections.js file is Sails.js' counterpart to Oracle Database's tnsnames.ora file. Modify config/connections.js so that it matches Listing 14.

module.exports.connections = {

  oraclehr: {

    adapter: 'sails-oracle-sp',

    user: 'hr',

    password: 'oracle',

    findCustomExceptions: 'RETURN_CODES_R',

    package: 'HR',

    cursorName: 'DETAILS',

    connectString: 'localhost/orcl'

  }

};

Listing 14: The connections.js file's settings that are suitable for the Oracle Database database preinstalled on this VM.

Install the PL/SQL Packages

Issue the commands shown in Listing 15 to install the various PL/SQL packages. The order in which the commands are executed is important. Here is a description of the commands:

• Line 1: Change to the directory that contains the various scripts used to create the demo's stored procedures.
• Line 2: The interface package exposes a stored procedure named return_codes_r() that returns a result set containing exception error codes with their error messages. The technique used by return_codes_r() requires certain objects to be created in the Oracle Database schema. Line 2 creates these objects in the schema.
• Lines 3 and 4: These commands install the hr-child package. One of this article's objectives is showing how the Sails.js/sails-oracle-sp/node-oracledb stack can be added to an already existing production database without disturbing it. The hr-child package represents stored procedures, packages, and so on that are stipulated to be part of the production database that existed prior to adding the stack. In reality, the underlying tables are just the Oracle Database's sample HR schema's EMPLOYEES and DEPARTMENTS tables.
• Lines 5 and 6: These commands install the interface package. Sails-oracle-sp depends on stored procedures with a particular naming convention. The interface package is a set of stored procedures that encapsulate the legacy stored procedures (that is, hr-child's stored procedures) to present a suitable interface to sails-oracle-sp.

1. $ cd ~/sails-oracledb-sp-example/db

2. $ sqlplus hr/oracle@localhost/orcl < create_type_retcodes.sql

3. $ sqlplus hr/oracle@localhost/orcl < create_pkg_hr-child.pls

4. $ sqlplus hr/oracle@localhost/orcl < create_pkgbdy_hr-child.plb

5. $ sqlplus hr/oracle@localhost/orcl < create_pkg_hr.pls

6. $ sqlplus hr/oracle@localhost/orcl < create_pkgbdy

Listing 15: Commands for installing the interface package

Launch Sails.js

Issue the commands shown in Listing 16 to launch Sails.js. Immediately afterward, clients can connect to the REST APIs and WebSocket connections presented by Sails.js. This example uses an Angular.js web page as a client.

$ cd ~/sails-oracledb-sp-example

$ sails lift

Listing 16: Commands for launching Sails.js

Taking the Example Application for a Spin

Sails.js is just middleware that presents REST APIs and a WebSocket interface. A front end is still required for a UI. The example application uses an Angular.js web page as a front end. One advantage of using Angular.js is that applications are just as usable on smartphones as they are on desktop computers in spite of zero extra code written for the purpose of adapting the applications to run on smartphones.

The UI is just a single page Angular.js application with a couple of tabs: a Department tab and an Employees tab. These tabs each present a form that enables users to modify records in Oracle Database's sample HR schema's EMPLOYEES and DEPARTMENTS tables. Figure 1 shows how the Department tab appears. The Employees tab has a similar appearance and behavior.

The back end's CRUD routines can be invoked by either REST APIs or WebSockets. In theory, the front end could be anything capable of invoking REST APIs or sending CRUD commands via WebSockets (for example, a smartphone app, a .NET app, or a home security system). Our example UI uses REST APIs for CRUD operations and uses WebSockets to receive push notifications. I chose to implement the front end as an Angular.js single-page application because then only a browser is required to test the system.

These are this application's most noteworthy features:

• It accommodates in-place editing. Editable "in place" means that the application will not present a modal dialog box for the user to specify changes; instead the user can just enter changes directly into the field. Updating data via in-place editing feels more natural, and the user receives immediate feedback if an attempted database update fails.
• Any data updated by any client is instantly presented on all connected clients in real time.
• It presents Oracle Database exceptions as error messages.
• It provides a separation of concerns: there is no embedded SQL code in the middleware.

Note: This article assumes that users will access the example application via the VM's preinstalled Firefox browser. If you desire to access the example application from outside the VM (for example, via a smartphone), you must place the VM's networking into "bridged mode." Complete the following steps to set the VM to bridge mode. Afterward you can interact with the example application from outside the VM.

1. Navigate to Machine->Settings->Network->Attached to.

2. Select bridged adapter.

3. In any specified URL, replace localhost with the host's IP address.

Test In-Place Editing

In the VM, point the Firefox browser to the URL shown in Listing 17, replacing _localhost_ with the host's IP address. Afterward the browser will present the example application's page.

http://localhost:1337/

Listing 17: Example application's URL

Figure 1 shows the Department tab. Those who are familiar with the HR tables might notice some uncanny similarities.

Figure 1: The Department tab's screen

Editing a given editable field will modify only its corresponding column in the underlying table. Sails.js connects the editable field to a specific "update" stored procedure that is dedicated to modifying the field's counterpart column in the underlying table. These "update" stored procedures are polymorphic, meaning they share the same name and are distinguished by different parameters. Sails-oracle-sp uses PL/SQL's named notation to call the correct polymorphic stored procedure for a given editable field.

The lifecycle of a field update attempt is as follows:

• The user enters revised data into the field and clicks submit to initiate the change.
• Sails.js invokes the stored procedure responsible for updating this field.
• The stored procedure updates the record's column in the underlying table.
• Sails.js automatically reads the entire record back from the database.
• Sails.js pushes the updated record's details to the UI via a WebSocket connection.
• The UI presents the changed record to the user.

If, for whatever reason, the attempt to update a field in the database fails, Sails.js will fetch the previous (unchanged) data to present to the user. If the data presented by the web page remains unchanged, the user will see that the change attempt failed in real time. This functionally is the same as C++'s Strong Exception-Safety Guarantee: an exception results in the system's state being unchanged.

Ideally, of course, the UI should also present an error message from the database to advise the user as to why the update attempt failed. That said, it is more important to show the user unchanged data so that the user does not think that a failed change attempt actually succeeded.

Sometimes the table is not the best level of abstraction. In the case of the DEPARTMENTS table, the department's location ID is a foreign key to a location table. Presenting the raw foreign key value as the department's location makes for a poor user experience. Presenting the department's city is a better user experience than presenting the location's foreign key.

In pursuit of improving the user experience, this example shows a department's location in terms of city name instead of the location's foreign key. I added a stored procedure family for the LOCATIONS table.

Angular.js has an MVC architecture. The Department tab's view's controller has been rigged so that it uses the location's foreign key to obtain the location's city. The controller obtains the location's city via the LOCATIONS table's stored procedure family.

Test Viewing Database Updates in Real Time

The example application's UI is based on Angular.js. Its two-way data binding provides automatic bidirectional updating between the Document Object Model (DOM) and its internal copy of the data—called a _model_—which is presented to the user via the DOM. (Both Sails.js and Angular.js have entities that are called "models." The Sails.js model and the Angular.js model are very different from each other.)

A noteworthy aspect of Sails.js is that it automatically pushes its database updates in real time to all connected clients via WebSockets. Sails.js' WebSocket notifications automatically update the Angular.js model. Sails.js' changes to the Angular.js model automatically trigger Angular.js to update the DOM. This means that all connected clients can continuously present the database's current information without continually polling the database. This automatic coupling of database changes to the Angular.js model and the automatic coupling of changes from the Angular.js model to the DOM constitutes three-way data binding.

To see this feature in action, open another tab in Firefox and point it to the URL shown in Listing 17. At this point ,you should have the application presented on two browser tabs.

Edit a field in one browser tab. Observe the same field in the other browser as you submit your change. As soon as you submit the change, you should see that the field is automatically refreshed with the new value in the other browser tab.

Try another example: open the Departments tab in one browser tab and open the Employees tab in the other browser tab. In the Department tab, change the name of, say, the "RESEARCH" department to "R&D" while observing the department fields in the Employees tab. As soon as you submit the change in the Employees tab, you can see all occurrences of departments named "RESEARCH" automatically updated to "R&D."

There are a number of applications that benefit from presenting volatile data in real time. Examples include stock tickers, telemetry from a remote sensor, alarms, and so on.

Oracle Database Exceptions

Oracle Database exceptions are caught by node-oracledb, converted into JavaScript exceptions by node-oracledb, and are then rethrown. Sails-oracle-sp catches these exceptions and attempts to maps them to error message obtained from the database.

For example, in the simple demonstration system, a stored procedure raises an exception when the user specifies a negative salary. Figure 2 shows the error message the UI presents when this exception is raised.

Figure 2: Error message shown when negative salaries are rejected by the database

For exceptions that are not mapped to a database-provided message, sails-oracle-sp defaults to a generic "API ERROR" message. This prevents the user from being mystified by messages such as "dup_val_on_index." Deciding which exceptions should generate the generic error message is a design decision. You can override the generic error message by providing an explicit error message for any exception for which the generic error message is not suitable.

In this scheme, only the database has any real comprehension regarding exceptions and how to process them. The UI is limited to presenting textual error messages when an exception is raised. The entire technology stack between the database and the UI is simply a pass-through mechanism that is oblivious to nature of the exception being handled.

Sails-oracle-sp caches the application's exception error messages, which it obtains from the database, when Sails.js starts up. Listing 18 shows the stored procedure that returns a result set composed of all exception numbers and their text messages. When sails-oracle-sp receives an exception, it finds the message mapped to the exception's number in its cache and presents it via the REST interface to the UI.

CREATE TYPE retcode_obj_t IS OBJECT(   -- must be database object

                                       id       NUMBER,

                                       message  VARCHAR2(300)

                                   );

CREATE TYPE retcode_nt IS TABLE OF retcode_obj_t;  -- must be database object

ec_success                      CONSTANT NUMBER   := 0;

ec_username_already_used        CONSTANT NUMBER   := 20010;

username_already_used           EXCEPTION;

ec_userid_already_used          CONSTANT NUMBER   := 20011;

userid_already_used             EXCEPTION;

ec_lacks_edit_privs             CONSTANT NUMBER   := 20012;

lacks_edit_privs                EXCEPTION;

ec_undefined_failure            CONSTANT NUMBER   := 20013;

PRAGMA EXCEPTION_INIT(  username_already_used, -20010 );

PRAGMA EXCEPTION_INIT(  userid_already_used,   -20011 );

PRAGMA EXCEPTION_INIT(  lacks_edit_privs,      -20012 );

-- msg() returns app specific exception messages for ORA number

PROCEDURE msg (

                 p_retcode   IN  NUMBER,

                 p_msg       OUT VARCHAR2

              )

IS

   retval VARCHAR2(300) := 'undefined error';

BEGIN

   p_msg :=  retval;

   CASE  p_retcode

     WHEN ec_success               THEN retval := 'operation succeeded';

     WHEN ec_username_already_used THEN retval := 'User name already taken.' ||

                                                  'Please specify an unused username.';

     WHEN ec_userid_already_used   THEN retval := 'User ID already taken. ' ||

                                                  'Please specify an unused ID.';

     WHEN ec_lacks_edit_privs      THEN retval := 'Edit attempt failed. ' ||

                                                  'User lacks edit privileges';

   ELSE

     retval := 'internal error: routine likely encountered' ||

               ' unexpected exception(' || TO_CHAR( p_retcode ) || ')';

   END CASE;

    p_msg := retval;

END;

FUNCTION msg ( p_retcode IN NUMBER) RETURN VARCHAR2

IS

   v_buf VARCHAR2(300);

BEGIN

   msg( p_retcode, v_buf );

   RETURN v_buf;

END;

PROCEDURE return_codes_r( p_details IN OUT SYS_REFCURSOR )

IS

   retcode_details_tab   retcode_nt := retcode_nt();

BEGIN

   -- build up a nested table containing both

   -- return codes: ORAs for all messages and

   -- message text for all messages

   FOR v_retcode IN ec_username_already_used .. ec_undefined_failure

   LOOP

      BEGIN

         retcode_details_tab.EXTEND();

         retcode_details_tab(retcode_details_tab.LAST) 

                   := retcode_obj_t(

                                      v_retcode,

                                      msg ( v_retcode )

                                   );

      END;

   END LOOP;

   -- select the contents of the nested table into the result set

      OPEN p_details FOR

         SELECT *

         FROM TABLE(cast(retcode_details_tab AS retcode_nt));

END;

Listing 18: Example of the stored procedure that returns all exception error messages

Separation of Concerns

Separation of concerns is a design principle that reduces lifecycle maintenance costs by separating a system's concerns into distinct sections. In this example application, the database is concerned with business logic and data persistence. The UI is concerned with presentation logic. The middleware (that is, Sails.js, sails-oracle-sp, and node-oracledb) is solely concerned with interconnecting the database with the UI.

Node-oracledb supports the separation of concerns in node-based applications. Embedded SQL can be eliminated from the middleware because node-oracledb supports stored procedure calls. Consequently, all considerations about table structure, business logic, and so on can be factored into the database; where they belong.

Conclusion

Node-oracledb, sails-oracle-sp, and Sails.js can be used to create middleware that, in exchange for the trivial effort of creating Sails.js models, will present REST APIs and WebSocket interfaces for Oracle Database PL/SQL stored procedures. Because the middleware layer requires little coding, the main development costs are concentrated in the database and the front end—that is, on the actual application—instead of on middleware glue logic.

Note: Authentication is implemented in Sails.js. Authentication requires a minor development effort.

The front end can be any application that can use REST APIs or WebSockets, including UIs such as web pages, smartphone apps, or systems without user interfaces (for example, machine-to-machine apps).

The clean separation of concerns lowers lifecycle maintenance costs by simplifying maintenance.

Presenting Oracle Database as REST APIs provides a means for subsystem testing of Oracle Database with its stored procedures functioning as a system.

Servers can use WebSockets to push data to web applications in real time instead of committing web applications to continuously poll their servers for updates.

Sails-oracle-sp's regimented stored procedure naming scheme constrains the possible stored procedure names. The interface package scheme described in part 1 of this series can adapt a Oracle Database legacy database to Sails.js without disturbing a system that is already in production. And, of course, the Sails.js/sails-oracle-sp/node-oracledb stack is very suitable for new systems.

About the Author

John Hendrix has been a software consultant for 20 years. His primary experience is in embedded programing, computer telephony (prepaid calling systems and SS7) and Oracle Database stored procedure development. Hendrix is a unit testing enthusiast and the cofounder of Nethoncho, LLC, a network planning and monitoring company.