Debugging Deployed JavaScript

Database Error Reporting

Oracle Database MLE raises errors in several situations. Depending on the situation, a different method to resolve the error should be applied. This document presents an overview of the different errors and how to make sense of them and fix them.

JavaScript Source Code Errors

Oracle Database MLE stores the JavaScript code as pre-processed sources inside the database (see the deployment documentation). If the deployed script contains compilation errors, Oracle Database MLE raises an ORA-20510 error when trying to use the script. This error occurs before any JavaScript code is actually executed.

This error can have multiple sources. A typical source is an actual error in you JavaScript source code. Fixing this error should resolve the problem. Note that the error location information might be wrong since the deployment tool uses browserify to combine multiple input sources and create a UMD module. Furthermore, Oracle Database MLE wraps the source code in some loading script. Both techniques make line information unusable. The wrapper might also hide the real error. This happens, e.g., in the case of a missing closing bracket.

If your extension does not use our internal SQL driver, it can be used outside of the database, too. In that case, you can run the script on Node.js to get a debugger and some source information from there. Node.js will also define the require function such that you can easily load other modules.

Exceptions at Runtime

When a deployed JavaScript extension throws an error during execution that is not handled inside deployed JavaScript code, Oracle Database MLE terminates execution and raises an ORA-20511 error. There are several ways to get more detailed error messages, but most of them require the developer to change the source code.

Function Not Found

Oracle Database MLE uses the concept of modules to export multiple functions and procedures at once. Oracle Database MLE loads UMD modules and makes available all functions exported in this module. If at invocation time the corresponding method is not found in the UMD module, Oracle Database MLE raises an ORA-20509 error. This error happens after loading the module.

To resolve this problem, make sure the function you are trying to call is exported as required by your JavaScript module style (module.exports for CommonJS, define for AMD; see the deployment documentation for more details).

Runtime Errors

There are different sources of errors in JavaScript code at runtime: A user can explicitly throw an exception, or the execution throws an implicit exception due to a programming error. These JavaScript exceptions can be caught at the JavaScript level but don't have to be caught there.

If an exception is not caught in JavaScript code, Oracle Database MLE will raise an ORA-20511 error and print the error message. Sometimes this is enough to diagnose and fix the error. We next present a couple of suggestions to help developers resolve this bug.

Trace File Stack Traces

The first method to get more information on errors for ORA-20511 is the database trace file. Access to this file is usually limited to database administrators. So this suggestion might not always be possible.

If you have access to the database (the file system the database is running on), you can locate the trace file with the query

SELECT value as TRACE_FILE_PATH FROM v$diag_info WHERE name = 'Default Trace File';

This query returns a path in the filesystem where the trace file is stored. The trace file contains a dump of the JavaScript stack trace of the exception that caused the execution to abort with the ORA-20511 error.

Generating Traces without Trace File Access

If a user does not have access to the trace file on the server, a different method can be used to generate the JavaScript stack trace. The idea is to catch the exception in a wrapper and log the exception stack trace and (optionally) the arguments.

A generic way to achieve exception stack trace logging requires this wrapper.

var console = require('console');

function genwrapper(f) {
  return function () {
    try {
      return f.apply(null, arguments);
    } catch (e) {
      console.log(e.stack);
      console.log("Arguments: " + [].slice.call(arguments));
      throw e;
    }
  };
}

You should include this snippet before you actually export functions to Oracle Database MLE. Then, instead of exporting function foo, export function genwrapper(foo). This will wrap function foo into an anonymous function that will catch all top-level exceptions and print the error stack and the arguments that triggered the error. This allows you to see where the error occurs and to generate a mockup outside of the database to test your JavaScript extension for exactly the arguments that caused the crash.

The wrapper presented above causes execution to end after an exception occurred since it re-throws the exception in the catch block. If you want to prevent this, return null which will be converted into the SQL NULL value (see the conversion documentation). In this case, you would get all errors for all inputs. But since our logging uses the server output buffer as explained in the documentation on stored procedures, output might be truncated if the buffer runs full during execution. Also the buffer is only flushed after query execution terminates.

As an example, let us use the wrapper on this small UDF:

function crashy(arg) {
  if (arg % 2 == 0) {
    throw Error("Cannot handle even numbers");
  }
  return arg + 1;
}

with the TypeScript declaration file

export function crashy(arg : number) : number;

In the default version, use the line

module.exports.crashy = crashy;

in your JavaScript source to export the function. See the deployment tool or the UDF tutorial on how to deploy the script. A simple call to this function via, e.g., SQL*Plus will report the ORA-20511 error as long as you have an even number in your input:

SQL> select crashy.crashy(empno) from emp;
ERROR:
ORA-20511: UDF runtime error [Error: Cannot handle even numbers]



no rows selected

To debug this crash, let us wrap our crashy function in the exception logging wrapper presented above. To do this, we replace the content of our original JavaScript file with

var console = require('console');

function genwrapper(f) {
  return function () {
    try {
      return f.apply(null, arguments);
    } catch (e) {
      console.log(e.stack);
      console.log("Arguments: " + [].slice.call(arguments));
      throw e;
    }
  };
}

function crashy(arg) {
  if (arg % 2 == 0) {
    throw Error("Cannot handle even numbers");
  }
  return arg + 1;
}

module.exports.crashy = genwrapper(crashy);

We don't have to change anything in the TypeScript declaration file since we want to get the same call specifications we got before. If we call the function now from a fresh SQL*Plus session, we get

SQL> set serveroutput on
SQL> select crashy.crashy(empno) from emp;
ERROR:
ORA-20511: UDF runtime error [Error: Cannot handle even numbers]



no rows selected

Error: Cannot handle even numbers
    at crashy (JS/SCOTT/crashy.js/1:1443:15)
    at
Object.<anonymous> (JS/SCOTT/crashy.js/1:1432:22)
    at .js_CRASHY69900328
(wqp:30:11)
Arguments: 7566

Mockup Outside of Oracle Database MLE

Another possibility to test and debug your JavaScript extension outside of the database is to write a mockup that replaces the database. This is only possible if the JavaScript extension does not use database specific extensions like the callout driver.

JavaScript extensions deployed into Oracle Database MLE should be UMD modules. As such, Node.js can load them and you can use the Node.js debugging infrastructure to experiment and debug your function. We will next write a mockup to call JavaScript extensions outside of the database.

We write our code into mockup/mock.js and assume the JavaScript extension under test is located in ../src/myext.js. The mockup then uses require from Node.js to load the extension and get the exported functions. We directly grab the crashy function from the module.

"use strict";
var ext = require('../src/myext.js');
var func = ext.crashy;

Next, we can put some test input. The test input obviously depends on the function or procedure to call. In the case of crashy from above we need one input which should be a number or null.

var input = [ 1, 2 ];

Then, we only need to call func for every item in the input:

```javascript

for (let val of input) { console.log(func(val)); }

Note that technically we don't need to log the result onto the console.  For
stored procedures, this should be a simple call to ``func`` since stored
procedures should not return anything.

To test our extension now, we use the debugger provided by Node.js:

$ node debug mockup/mock.js

We can now step through our program or set breakpoints in specific functions.
If the input contains the error-inducing input value, we will be able to debug
the JavaScript extension in a failing execution and fix the problem.

To summarize, we show the whole mockup code here again:

```javascript
"use strict";
var ext = require('../src/myext.js');
var func = ext.crashy;

var input = [ 1, 2 ];

for (let val of input) {
  console.log(func(val));
}