oracle
Numeric overflow incorrectly handled in thick mode
-
What versions are you using?
database version: Oracle 19c Enterprise edition version 19.21.0.0.0
process.platform: 'linux' process.version: 'v20.11.0' process.arch: 'x64' require('oracledb').versionString: '6.4.0' require('oracledb').oracleClientVersionString: undefined
-
Is it an error or a hang or a crash? No
-
What error(s) or behavior you are seeing?
Do not get expected ORA-01426 errors in thick mode. Do see NJS-115 errors in thin mode. CREATE TABLE no_example (id NUMBER, float64_1 NUMBER, float64_2 NUMBER)
Running in thick mode
binds: [ 101, 1e+25, -1e+25 ] ,result: 1
binds: [ 102, 1.7976931348623157e+308, -1.7976931348623157e+308 ] ,result: 1
binds: [ 103, -1.7976931348623157e+308, 1.7976931348623157e+308 ] ,result: 1
Query results:
[
[ 101, 9.999999999999999e+24, -9.999999999999999e+24 ],
[ 102, 1.0000000000000001e+126, 0 ],
[ 103, 0, 1.0000000000000001e+126 ]
]
Running in thin mode
binds: [ 101, 1e+25, -1e+25 ] ,result: 1
binds: [ 102, 1.7976931348623157e+308, -1.7976931348623157e+308 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
binds: [ 103, -1.7976931348623157e+308, 1.7976931348623157e+308 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
Query results:
[ [ 101, 1e+25, -1e+25 ] ]
- Include a runnable Node.js script that shows the problem.
// based on examples/example.js
const oracledb = require('oracledb');
// Optionally run in node-oracledb Thick mode
if (process.env.NODE_ORACLEDB_DRIVER_MODE === 'thick') {
oracledb.initOracleClient(); // enable node-oracledb Thick mode
}
console.log(oracledb.thin ? 'Running in thin mode' : 'Running in thick mode');
async function run() {
var self = this;
let connection;
try {
let sql, binds, options, result;
connection = await oracledb.getConnection({
user: process.env.NODE_ORACLEDB_USER,
password: process.env.NODE_ORACLEDB_PASSWORD,
connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING,
});
const stmts = [
`DROP TABLE no_example`,
`CREATE TABLE no_example (id NUMBER, float64_1 NUMBER, float64_2 NUMBER)`
];
for (const s of stmts) {
try {
await connection.execute(s);
} catch (e) {
if (e.errorNum != 942) {
console.error(e);
}
}
}
sql = `INSERT INTO no_example VALUES (:1, :2, :3)`;
binds = [
[101, 1.0e25, -1.0e25 ],
[102, Number.MAX_VALUE, -Number.MAX_VALUE ],
[103, -Number.MAX_VALUE, Number.MAX_VALUE ]
];
for (const b of binds) {
try {
result = await connection.execute(sql, b, {autoCommit: true});
console.log("binds:", b, " ,result:", result.rowsAffected);
} catch (e) {
console.log("binds:", b, " ,result:", e.toString());
}
}
sql = `SELECT * FROM no_example`;
result = await connection.execute(sql, {}, {});
console.log("Query results: ");
console.log(result.rows);
} catch (err) {
console.error(err);
} finally {
if (connection) {
try {
await connection.close();
} catch (err) {
console.error(err);
}
}
}
}
run();
@mrfitz42 Thank you for reporting this issue. Can you let us know if you had observed this issue in the pre-Thin mode version of node-oracledb (version 5.5 or earlier).
Yes, version 5.0.0 exhibited the same behavior.
If I examine the table contents with SQL Developer, thick mode values for the bad rows are:
102 Infinity -Infinity
103 -Infinity Infinity
Thin mode exhibits odd behavior at the Oracle numeric limit:
Running in thin mode
binds: [ 101, 1e+126, -1e+126 ] ,result: 1
binds: [ 102, 1.7976931348623157e+308, -1.7976931348623157e+308 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
binds: [ 103, -1.7976931348623157e+308, 1.7976931348623157e+308 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
Query results:
[ [ 101, -9.9e+125, 9.9101e+126 ] ]
Even stranger, SQL Developer then shows the float64 columns as (null).
Thanks @mrfitz42. We had identified a similar issue internally and it will be fixed in the upcoming release.
This did fix thick mode, thank you. It now results in two error codes: NJS-115 and DPI-1044.
Running in thick mode
binds: [ 101, 1e+126, -1e+126 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number
DPI-1044: value cannot be represented as an Oracle number
Query results:
[]
Thin mode still has an issue with values just past the Oracle limits:
Running in thin mode
binds: [ 101, 1e+126, -1e+126 ] ,result: 1
Query results:
[ [ 101, -9.9e+125, 9.9101e+126 ] ]
@mrfitz42 Here is the patch for the Thin mode fix:
diff --git a/lib/impl/datahandlers/buffer.js b/lib/impl/datahandlers/buffer.js
index xxxxxx yyyyy
--- a/lib/impl/datahandlers/buffer.js
+++ b/lib/impl/datahandlers/buffer.js
@@ -843,8 +843,8 @@ class BaseBuffer {
}
// throw exception if number cannot be represented as an Oracle Number
- if (value.length > constants.NUMBER_MAX_DIGITS || exponent > 126 ||
- exponent < -129) {
+ if (value.length > constants.NUMBER_MAX_DIGITS || exponent >= 126 ||
+ exponent <= -131) {
errors.throwErr(errors.ERR_ORACLE_NUMBER_NO_REPR);
}
@mrfitz42 We have fixed the Thin mode issue in 6.5.1. Please verify and thank you for contributing!
Fixed in thin mode too. Thank you. ` Running in thin mode binds: [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ,result: 1 binds: [ 101, 1e+126, -1e+126 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number Query results: [ [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ]
Running in thick mode binds: [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ,result: 1 binds: [ 101, 1e+126, -1e+126 ] ,result: Error: NJS-115: value cannot be represented as an Oracle Database number DPI-1044: value cannot be represented as an Oracle number Query results: [ [ 100, 9.99999999999999e+125, -9.99999999999999e+125 ] ] `