Add DPI error codes to isConnectionError() function for oracle

[vpeltola]

Environment

Knex version: 0.21.19
Database + version: Oracle 19c
OS: Oracle Linux Server release 7.8

@atiertant

Bug

1. Explain what kind of behaviour you are getting and how you think it should do
   When a database connection is lost, the automatic reconnect is no longer working, for example when I'm using knex.raw('select ...', ...) method. Until just recently knex was able to reconnect to the database automatically. The problem started happening after we upgraded to node oracledb 5.2.0 module from the earlier 4.x versions.

The oracledb CHANGELOG.md states that the error codes have changed to "DPI-1080: connection was closed by ORA-%d" and "DPI-1010: not connected". I also observed that when one of these errors is thrown, the "errorNum" field is now just 0, where before it used to have the ORA error number such as 3113.

Quote:

Enhanced dead connection detection. If an Oracle Database error indicates that a connection is no longer usable, the error DPI-1080: connection was closed by ORA-%d is now returned. The %d will be the Oracle error causing the connection to be closed. Using the connection after this will give DPI-1010: not connected. This behavior also applies for oracle.callTimeout errors that result in an unusable connection. (ODPI-C change).

2. Error message

Sample error 1:

[Error: select * from (select "request_id", "request_details", "created_at" from "requests" where "status" = :1 ORDER BY "request_id" asc) where rownum <= :2 - DPI-1080: connection was closed by ORA-3113] {
errorNum: 0,
offset: 0
}

Sample error 2:

Error: insert into "requests" ("created_by", "request_details", "request_object", "request_type", "status", "updated_by") values (:1, :2, :3, :4, :5, :6) returning "request_id" into :7 - DPI-1010: not connected

3. Reduced test code, for example in https://npm.runkit.com/knex or if it needs real
   database connection to MySQL or PostgreSQL, then single file example which initializes
   needed data and demonstrates the problem.

Write a script that queries a table in a loop. While it's running, log in to the DB as an admin user and kill the session. The session is not re-instanteted automatically.

4. Solution that works for me is to update node_modules/knex/lib/dialects/oracle/utils.js isConnectionError(err) function by adding the following 2 lines to the list of error codes:

    'DPI-1010',  // not connected
    'DPI-1080',  // connection was closed by ORA-%d

[vpeltola]

Note this problem only starts when using the oracledb 5.2.0 or higher node module. Downgrading oracledb to 5.1.0 is a solution that works, too, because the new DPI- error codes were only introduced in 5.2.0.

[kibertoad]

@vpeltola Would you be open to submit a PR for this?

[vpeltola]

@kibertoad sure, I can do that.

[vpeltola]

I have created PR #4536

[cjbj]

The PR was merged and @kibertoad released 0.95.8
Yay!