Error Handling in MLE

Errors encountered during the execution of MLE JavaScript code are reported as database errors.

The database error raised depends on the type of error encountered. For example, syntax errors raise ORA-04160 while runtime errors (e.g., uncaught exceptions) raise ORA-04161. The error message for each database error provides a brief description of the error encountered. Additionally, the DBMS_MLE PL/SQL package provides procedures to query the MLE JavaScript stack trace for the last error encountered in a dynamic MLE execution context or an MLE module in the current session.

The same security checks are made when calling DBMS_MLE.get_ctx_error_stack() as when calling DBMS_MLE.eval(). Thus, you cannot retrieve error stacks for MLE JavaScript code executing in dynamic MLE execution contexts created by other users.

DBMS_MLE provides a similar function, DBMS_MLE.get_error_stack(), to access the MLE JavaScript stack trace for application errors encountered during the execution of MLE modules. The function takes the module name and optionally the environment name as parameters, returning the stack trace for the most recent application error in a call specification based on the given arguments. If the module name or environment name is not a valid identifier, an ORA-04170 error is raised.

With MLE modules, it is only possible to retrieve the error stack for the module contexts associated with the calling user. This restriction avoids potentially leaking sensitive information between users via the error stack. A natural consequence of this restriction is that you cannot retrieve stack traces for errors encountered when executing definer's rights MLE call specifications owned by other users.

Example 9-7 Throwing ORA-04161 Error and Querying the Stack Trace

Executing the following code will throw an ORA-04161 error:

CREATE OR REPLACE MLE MODULE catch_and_print_error_stack
LANGUAGE JAVASCRIPT AS

export function f(){
    g();
}

function g(){
    h();
}

function h(){
    throw Error("An error occurred in h()");
}
/

CREATE OR REPLACE PROCEDURE not_getting_entire_error_stack
AS MLE MODULE catch_and_print_error_stack
SIGNATURE 'f()';
/

BEGIN
    not_getting_entire_error_stack;
END;
/

Result:

BEGIN
*
ERROR at line 1:
ORA-04161: Error: An error occurred in h()
ORA-04171: at h (USER1.CATCHING_AND_PRINTING_ERROR_STACK:10:11)
ORA-06512: at "USER1.NOT_GETTING_THE_ENTIRE_ERROR_STACK", line 1
ORA-06512: at line 2
*/

You can query the stack trace for this error using the procedure DBMS_MLE.get_error_stack():

CREATE OR REPLACE PACKAGE get_entire_error_stack_pkg AS

    PROCEDURE get_entire_error_stack;
 
END get_entire_error_stack_pkg;
/

CREATE OR REPLACE PACKAGE BODY get_entire_error_stack_pkg AS

    PROCEDURE print_stack_trace( p_frames IN DBMS_MLE.error_frames_t ) AS
    BEGIN
        FOR i in 1 .. p_frames.count LOOP
            DBMS_OUTPUT.PUT_LINE( p_frames(i).func || '(' || 
            p_frames(i).source || ':' || p_frames(i).line || ')');
        END LOOP;
    END print_stack_trace;
 
    PROCEDURE do_the_work
    AS MLE MODULE catch_and_print_error_stack
    SIGNATURE 'f()';
 
    PROCEDURE get_entire_error_stack AS
        l_frames DBMS_MLE.error_frames_t;
    BEGIN
        do_the_work;
    EXCEPTION
    WHEN OTHERS THEN
        l_frames := DBMS_MLE.get_error_stack(
            'CATCH_AND_PRINT_ERROR_STACK'
        );
        print_stack_trace(l_frames);
        raise;
    END;
END get_entire_error_stack_pkg;
/

BEGIN
    get_entire_error_stack_pkg.get_entire_error_stack;
END;
/

The preceding code prints out the MLE JavaScript exception stack trace before raising the original error:

h(USER1.CATCH_AND_PRINT_ERROR_STACK:10)
g(USER1.CATCH_AND_PRINT_ERROR_STACK:6)
f(USER1.CATCH_AND_PRINT_ERROR_STACK:2)
BEGIN
*
ERROR at line 1:
ORA-04161: Error: An error occurred in h()
ORA-06512: at "USER1.GET_ENTIRE_ERROR_STACK_PKG", line 25
ORA-04171: at h (USER1.CATCH_AND_PRINT_ERROR_STACK:10:11)
ORA-06512: at "USER1.GET_ENTIRE_ERROR_STACK_PKG", line 11
ORA-06512: at "USER1.GET_ENTIRE_ERROR_STACK_PKG", line 18
ORA-06512: at line 2

Errors in Callouts
Database errors raised during callouts to SQL and PL/SQL via the MLE SQL driver are automatically converted to JavaScript exceptions.
Accessing stdout and stderr from JavaScript
MLE provides functionality to access data written to standard output and error streams from JavaScript code.

Parent topic: Post-Execution Debugging of MLE JavaScript Modules

Errors in Callouts

Database errors raised during callouts to SQL and PL/SQL via the MLE SQL driver are automatically converted to JavaScript exceptions.

For most database errors, JavaScript code can catch and handle these exceptions as usual. However, exceptions resulting from critical database errors cannot be caught. This includes:

Internal database errors (ORA-0600)
Fatal database errors (ORA-0603)
Errors triggered due to resource limits being exceeded (ORA-04036)
User interrupts (ORA-01013)
System errors (ORA-7445)

Exceptions resulting from database errors that are either not caught or are re-signaled cause the original database error to be raised in addition to an MLE runtime error (ORA-04161). You can retrieve the JavaScript stack trace for such exceptions using DBMS_MLE.get_error_stack() just like with other runtime errors.

Parent topic: Error Handling in MLE

Accessing stdout and stderr from JavaScript

MLE provides functionality to access data written to standard output and error streams from JavaScript code.

Within a database session, these streams can be controlled individually for each database user, MLE module, and dynamic MLE context. In each case, a stream can be:

Disabled,
Redirected to DBMS_OUTPUT, or
Redirected to a user provided CLOB

Accessing stdout and stderr for MLE Modules
The DBMS_MLE PL/SQL package provides the procedures set_stdout() and set_stderr() to control the standard output and error streams for each MLE module context.
Accessing stdout and stderr for Dynamic MLE
The procedures DBMS_MLE.set_ctx_stdout() and DBMS_MLE.set_ctx_stderr() are used to redirect stdout and stderr for dynamic MLE contexts.

Parent topic: Error Handling in MLE

Accessing stdout and stderr for MLE Modules

The DBMS_MLE PL/SQL package provides the procedures set_stdout() and set_stderr() to control the standard output and error streams for each MLE module context.

Alternatively, stdout can be redirected to DBMS_OUTPUT using the function DBMS_MLE.set_stdout_to_dbms_output(). The DBMS_MLE package provides an analogous function fore redirection stderr: DBMS_MLE.set_stderr_to_dbms_output().

stdout and stderr can be disabled for a module at any time by calling DBMS_MLE.disable_stdout() and DBMS_MLE.disable_stderr() respectively.

By default, stdout and stderr are redirected to DBMS_OUTPUT.

Note that the CURRENT_USER from an MLE function exported by the given MLE module may change depending on the CURRENT_USER when the function was called and whether the function is invoker's rights or definer's rights. A call to DBMS_MLE.set_stdout() or DBMS_MLE.set_stderr() by a database user, say user1, only redirects the appropriate stream when code in the MLE module executes with the privileges of user1.

In other words, one database user cannot ordinarily control the behavior of stdout and stderr for execution of an MLE module's code on behalf of another user.

All of these procedures take a module name and optionally an environment name as first and second arguments. This identifies the execution context whose output should be redirected. Omitting the environment name targets contexts using the base environment. Additionally, set_stdout and set_stderr take a user-provided CLOB as the last argument, specifying where the output should be written to.

Example 9-8 Redirect stdout to CLOB and DBMS_OUTPUT for MLE Module

Consider the following JavaScript module:

CREATE OR REPLACE MLE MODULE hello_mod 
LANGUAGE JAVASCRIPT AS 
    export function hello() { 
        console.log('Hello, World from MLE!'); 
    }
/

The following call specification makes the exported function hello() available for calling from PL/SQL code.

CREATE OR REPLACE PROCEDURE MLE_HELLO_PROC
AS MLE MODULE hello_mod SIGNATURE 'hello';
/

The code below redirects stdout for the module hello_mod to a CLOB that can be examined later:

SET SERVEROUTPUT ON; 
DECLARE 
    l_output_buffer CLOB; 
BEGIN
    -- create a temporary LOB to hold the output
    DBMS_LOB.CREATETEMPORARY(l_output_buffer, false);

    -- redirect stdout to a CLOB
    DBMS_MLE.SET_STDOUT('HELLO_MOD', l_output_buffer); 
    
    -- run the code
    MLE_HELLO_PROC(); 

    -- retrieve the output buffer
    DBMS_OUTPUT.PUT_LINE(l_output_buffer); 
END; 
/

Executing the above produces the following output:

Hello, World from MLE!

Alternatively, stdout can be redirected to DBMS_OUTPUT using the function DBMS_MLE.SET_STDOUT_TO_DBMS_OUTPUT():

SET SERVEROUTPUT ON;
BEGIN
    DBMS_MLE.SET_STDOUT_TO_DBMS_OUTPUT('HELLO_MOD');
    MLE_HELLO_PROC();
END;
/

This produces the same output as before:

Hello, World from MLE!

Parent topic: Accessing stdout and stderr from JavaScript

Accessing stdout and stderr for Dynamic MLE

The procedures DBMS_MLE.set_ctx_stdout() and DBMS_MLE.set_ctx_stderr() are used to redirect stdout and stderr for dynamic MLE contexts.

The DBMS_MLE package similarly provides the procedures set_ctx_stdout_to_dbms_output() and set_ctx_stderr_to_dbms_output() to redirect stdout and stderr for dynamic MLE contexts to DBMS_OUTPUT.

A call to one of these functions redirects the appropriate stream for all dynamic MLE code executing within the context. However, any calls to MLE functions via the MLE SQL driver use the redirection effect for the MLE module that implement the function.

Example 9-9 Redirect stdout to CLOB and DBMS_OUTPUT for Dynamic MLE

SET SERVEROUTPUT ON;
DECLARE
    l_ctx DBMS_MLE.context_handle_t;
    l_snippet CLOB;
    l_output_buffer CLOB;
BEGIN
    -- allocate the execution context and the output buffer
    l_ctx := DBMS_MLE.create_context();
    DBMS_LOB.CREATETEMPORARY(l_output_buffer, false);

    -- redirect stdout to a CLOB
    DBMS_MLE.SET_CTX_STDOUT(l_ctx, l_output_buffer);

    -- a bit of JavaScript code printing to the console
    l_snippet := 'console.log( "Hello, World from dynamic MLE!" )';
	
    -- execute the code snippet
    DBMS_MLE.eval(l_ctx, 'JAVASCRIPT', l_snippet);

    -- drop the execution context and print the output
    DBMS_MLE.drop_context(l_ctx);
    DBMS_OUTPUT.PUT_LINE(l_output_buffer);
END;
/

This produces the following output:

Hello, World from dynamic MLE!

Parent topic: Accessing stdout and stderr from JavaScript