Example 7-13 SQL Error Handling Inside a JavaScript Function

CREATE TABLE log_t (
    id NUMBER GENERATED ALWAYS AS IDENTITY
    CONSTRAINT pk_log_t PRIMARY KEY,
    err VARCHAR2(255),
    msg VARCHAR2(255)
);

CREATE OR REPLACE PACKAGE logging_pkg as
  PROCEDURE log_err(p_msg VARCHAR2, p_err VARCHAR2);
END logging_pkg;
/

CREATE OR REPLACE PACKAGE BODY logging_pkg AS
  PROCEDURE log_err(p_msg VARCHAR2, p_err VARCHAR2)
  AS
    PRAGMA autonomous_transaction;
  BEGIN
    INSERT INTO log_t (
        err,
        msg
    ) VALUES (
        p_err,
        p_msg
    );
    COMMIT;
  END log_err;
END logging_pkg;
/

CREATE OR REPLACE MLE MODULE js_err_handle_mod
LANGUAGE JAVASCRIPT AS

/**
 *short demo showing how to use try/catch to catch an error
 *and proceeding normally. In the example, the error is 
 *provoked
*/
export function errorHandlingDemo(){

    try{
        const result = session.execute(
            `INSERT INTO
                surelyThisTableDoesNotExist
            VALUES
                (1)`
        );

    console.log(`there were ${result.rowsAffected} rows inserted`);

    } catch(err) {
        logError('this is some message', err);

        //tell the caller that something went wrong
        return false;
    }

    //further processing

    //return successful completion of the code
    return true;
}

/**
 *log an error using the logging_pkg created at the beginning
 *of this example. Think of it as a package logging errors in 
 *a framework for later analysis.
 *@param msg an accompanying message
 *@param err the error encountered
*/
function logError(msg, err){
    const result = session.execute(
        `BEGIN
            logging_pkg.log_err(
                p_msg => :msg,
                p_err => :err
            );
        END;`,
        {
            msg: {
                val: msg,
                dir: oracledb.BIND_IN
            },
            err: {
                val: err.message,
                dir: oracledb.BIND_IN
            }
        }
    );
}
/

Create a function, js_err_handle_mod_f, using the module js_err_handle_mod as follows:

CREATE OR REPLACE FUNCTION js_err_handle_mod_f
RETURN BOOLEAN
AS MLE MODULE js_err_handle_mod
SIGNATURE 'errorHandlingDemo()';
/

Now you can call the function and use the return value to see whether the processing was successful:

DECLARE
    l_success boolean := false;
BEGIN
    l_success := js_err_handle_mod_f;

    IF l_success THEN
        DBMS_OUTPUT.PUT_LINE('normal, successful completion');
    ELSE
        DBMS_OUTPUT.PUT_LINE('an error has occurred');
    END IF;
END;
/

In this case, the error is caught within the MLE module. The error is recorded by the application, allowing the administrator to assess the situation and take corrective action.

Example 7-14 Error Handling Using JavaScript throw() Command

This example demonstrates the use of the JavaScript throw() command in the catch block. Unlike the screen output shown for js_err_handle_mod in Example 7-13, a calling PL/SQL block will have to catch the error and either treat it accordingly or raise it again.

CREATE OR REPLACE MLE MODULE js_throw_mod
LANGUAGE JAVASCRIPT AS

/**
 *a similar example as Example 7-13, however, rather than
 *processing the error in the JavaScript code, it is re-thrown up the call stack.
 *It is now up to the called to handle the exception. The try/catch block is not 
 *strictly necessary but is used in this example as a cleanup step to remove Global
 *Temporary Tables (GTTs) and other temporary objects that are no longer required.
*/
export function rethrowError(){
    
    try{
        const result = session.execute(
            `INSERT INTO 
                surelyThisTableDoesNotExist
            VALUES
                (1)`
        );

        console.log(`there were ${result.rowsAffected} rows inserted`);

    } catch(err){
        cleanUpBatch();

        throw(err);
    }

    //further processing
}

function cleanUpBatch(){
    //batch cleanup operations
    return;
}
/

Using the following call specification, failing to catch the error will result in an unexpected error, which can propagate up the call stack all the way to the end user.

CREATE OR REPLACE PROCEDURE rethrow_err_proc
AS MLE MODULE js_throw_mod
SIGNATURE 'rethrowError()';
/

BEGIN
    rethrow_err_proc;
END;
/

Result:

BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-04171: at rethrowError (USER1.JS_THROW_MOD:11:24)
ORA-06512: at "USER1.RETHROW_ERROR_PROC", line 1
ORA-06512: at line 2

End users should not see this type of error. Instead, a more user-friendly message should be displayed. Continuing the example, a simple fix is to add an exception block:

BEGIN
    rethrow_err_proc;
EXCEPTION
    WHEN OTHERS THEN
        logging_pkg.log_err(
            'something went wrong',
            sqlerrm
        );
        --this would be shown on the user interface;
        --for the sake of demonstration this workaround
        --is used to show the concept
        DBMS_OUTPUT.PUT_LINE(
            'ERROR: the process encountered an unexpected error'
        );
        DBMS_OUTPUT.PUT_LINE(
            'please inform the administrator referring to application error 1234'
        );
END;
/

Result:

ERROR: the process encountered an unexpected error
please inform the administrator referring to application error 1234

PL/SQL procedure successfully completed.