1023 lines
34 KiB
JavaScript
1023 lines
34 KiB
JavaScript
/* Copyright (c) 2025, Oracle and/or its affiliates. */
|
|
|
|
/******************************************************************************
|
|
*
|
|
* This software is dual-licensed to you under the Universal Permissive License
|
|
* (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
|
|
* 2.0 as shown at https://www.apache.org/licenses/LICENSE-2.0. You may choose
|
|
* either license.
|
|
*
|
|
* If you elect to accept the software under the Apache License, Version 2.0,
|
|
* the following applies:
|
|
*
|
|
* Licensed under the Apache License, Version 2.0 (the "License");
|
|
* you may not use this file except in compliance with the License.
|
|
* You may obtain a copy of the License at
|
|
*
|
|
* https://www.apache.org/licenses/LICENSE-2.0
|
|
*
|
|
* Unless required by applicable law or agreed to in writing, software
|
|
* distributed under the License is distributed on an "AS IS" BASIS,
|
|
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
|
* See the License for the specific language governing permissions and
|
|
* limitations under the License.
|
|
*
|
|
* NAME
|
|
* 312. dataTypeIntervalDS.js
|
|
*
|
|
* DESCRIPTION
|
|
* Testing Oracle data type support - IntervalDS (Days-to-Second).
|
|
*
|
|
*****************************************************************************/
|
|
'use strict';
|
|
|
|
const oracledb = require('oracledb');
|
|
const assert = require('assert');
|
|
const dbConfig = require('./dbconfig.js');
|
|
const testsUtil = require('./testsUtil.js');
|
|
|
|
describe('312. dataTypeIntervalDS.js', function() {
|
|
let conn;
|
|
const tableName = 'TestIntervals';
|
|
|
|
before(async function() {
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
|
|
// Create the table
|
|
const sql = `CREATE TABLE ${tableName} (
|
|
IntCol NUMBER(9) NOT NULL,
|
|
IntervalCol INTERVAL DAY TO SECOND NOT NULL,
|
|
NullableCol INTERVAL DAY TO SECOND,
|
|
IntervalPrecisionCol INTERVAL DAY(7) TO SECOND,
|
|
IntervalPrecisionScaleCol INTERVAL DAY(8) TO SECOND(9)
|
|
)`;
|
|
|
|
await conn.execute(testsUtil.sqlCreateTable(tableName, sql));
|
|
|
|
// Insert test data
|
|
const plsql = `
|
|
BEGIN
|
|
FOR i IN 1..10 LOOP
|
|
INSERT INTO ${tableName}
|
|
VALUES (
|
|
i,
|
|
TO_DSINTERVAL(TO_CHAR(i) || ' ' || TO_CHAR(i) || ':' || TO_CHAR(i * 2) || ':' || TO_CHAR(i * 3)),
|
|
CASE
|
|
WHEN MOD(i, 2) = 0 THEN NULL
|
|
ELSE TO_DSINTERVAL(TO_CHAR(i + 5) || ' ' || TO_CHAR(i + 2) || ':' || TO_CHAR(i * 2 + 5) || ':' || TO_CHAR(i * 3 + 5))
|
|
END,
|
|
TO_DSINTERVAL('8 05:15:00'),
|
|
TO_DSINTERVAL('10 12:15:15')
|
|
);
|
|
END LOOP;
|
|
END;
|
|
`;
|
|
await conn.execute(plsql);
|
|
await conn.commit();
|
|
});
|
|
|
|
after(async function() {
|
|
// Drop the table
|
|
await testsUtil.dropTable(conn, tableName);
|
|
await conn.close();
|
|
});
|
|
|
|
it('312.1 - binding in an interval', async function() {
|
|
const value = new oracledb.IntervalDS({ days: 5, hours: 5, minutes: 10, seconds: 15, fseconds: 0 });
|
|
const result = await conn.execute(
|
|
`SELECT * FROM ${tableName} WHERE IntervalCol = :value`,
|
|
{ value: { val: value, type: oracledb.DB_TYPE_INTERVAL_DS } }
|
|
);
|
|
assert.strictEqual(result.rows.length, 1);
|
|
assert.strictEqual(result.rows[0][0], 5);
|
|
}); // 312.1
|
|
|
|
it('312.2 - binding a null value for an interval column', async function() {
|
|
const result = await conn.execute(
|
|
`SELECT * FROM ${tableName} WHERE IntervalCol = :value`,
|
|
{ value: null }
|
|
);
|
|
assert.deepStrictEqual(result.rows, []);
|
|
}); // 312.2
|
|
|
|
it('312.3 - binding out with interval column', async function() {
|
|
const bindVars = {
|
|
value: { dir: oracledb.BIND_OUT, type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
};
|
|
const result = await conn.execute(
|
|
`BEGIN :value := TO_DSINTERVAL('8 09:24:18.123789'); END;`,
|
|
bindVars
|
|
);
|
|
|
|
const expectedObj = new oracledb.IntervalDS({days: 8, hours: 9,
|
|
minutes: 24, seconds: 18, fseconds: 123789000});
|
|
assert(result.outBinds.value instanceof oracledb.IntervalDS);
|
|
assert.deepStrictEqual(result.outBinds.value, expectedObj);
|
|
}); // 312.3
|
|
|
|
it('312.4 - binding out with interval column - no fractional seconds', async function() {
|
|
const bindVars = {
|
|
value: { dir: oracledb.BIND_OUT, type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
};
|
|
const result = await conn.execute(
|
|
`BEGIN :value := TO_DSINTERVAL('8 09:30:00'); END;`,
|
|
bindVars
|
|
);
|
|
|
|
const expectedObj = new oracledb.IntervalDS({days: 8, hours: 9,
|
|
minutes: 30, seconds: 0, fseconds: 0});
|
|
assert(result.outBinds.value instanceof oracledb.IntervalDS);
|
|
assert.deepStrictEqual(result.outBinds.value, expectedObj);
|
|
}); // 312.4
|
|
|
|
it('312.5 - binding in/out with interval arithmetic', async function() {
|
|
const bindVars = {
|
|
value: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: new oracledb.IntervalDS({ days: 5, hours: 2, minutes: 15, seconds: 0 }),
|
|
},
|
|
};
|
|
const result = await conn.execute(
|
|
`BEGIN :value := :value + TO_DSINTERVAL('5 08:30:00'); END;`,
|
|
bindVars
|
|
);
|
|
assert(result.outBinds.value instanceof oracledb.IntervalDS);
|
|
assert.deepStrictEqual(result.outBinds.value,
|
|
new oracledb.IntervalDS({ "days": 10, "hours": 10, "minutes": 45, "seconds": 0 }));
|
|
}); // 312.5
|
|
|
|
it('312.6 - binding in/out with fractional seconds', async function() {
|
|
const bindVars = {
|
|
value: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: new oracledb.IntervalDS({ days: 5, hours: 0, minutes: 0, seconds: 12, fseconds: 123789 })
|
|
},
|
|
};
|
|
const result = await conn.execute(
|
|
`BEGIN :value := :value + TO_DSINTERVAL('5 08:30:00'); END;`,
|
|
bindVars
|
|
);
|
|
assert(result.outBinds.value instanceof oracledb.IntervalDS);
|
|
assert.deepStrictEqual(result.outBinds.value,
|
|
new oracledb.IntervalDS({"days": 10, "hours": 8, "minutes": 30,
|
|
"seconds": 12, "fseconds": 123789}));
|
|
}); // 312.6
|
|
|
|
it('312.7 - column metadata', async function() {
|
|
const result = await conn.execute(`SELECT * FROM ${tableName}`);
|
|
const metadata = result.metaData.map((col) => ({
|
|
name: col.name,
|
|
type: col.dbType,
|
|
}));
|
|
|
|
const expectedMetadata = [
|
|
{ name: 'INTCOL', type: oracledb.DB_TYPE_NUMBER },
|
|
{ name: 'INTERVALCOL', type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
{ name: 'NULLABLECOL', type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
{ name: 'INTERVALPRECISIONCOL', type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
{ name: 'INTERVALPRECISIONSCALECOL', type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
];
|
|
|
|
assert.deepStrictEqual(metadata, expectedMetadata);
|
|
}); // 312.7
|
|
|
|
it('312.8 - fetch all rows', async function() {
|
|
const result = await conn.execute(`SELECT * FROM ${tableName} ORDER BY IntCol`);
|
|
assert.strictEqual(result.rows.length, 10);
|
|
}); // 312.8
|
|
|
|
it('312.9 - fetch data in chunks', async function() {
|
|
const result = await conn.execute(`SELECT * FROM ${tableName} ORDER BY IntCol`);
|
|
const chunk1 = result.rows.slice(0, 3);
|
|
const chunk2 = result.rows.slice(3, 5);
|
|
const chunk3 = result.rows.slice(5, 9);
|
|
const chunk4 = result.rows.slice(9, 10);
|
|
|
|
assert.strictEqual(chunk1.length, 3);
|
|
assert.strictEqual(chunk2.length, 2);
|
|
assert.strictEqual(chunk3.length, 4);
|
|
assert.strictEqual(chunk4.length, 1);
|
|
}); // 312.9
|
|
|
|
it('312.10 - fetch single row', async function() {
|
|
const result = await conn.execute(
|
|
`SELECT * FROM ${tableName} WHERE IntCol IN (3, 4) ORDER BY IntCol`
|
|
);
|
|
assert.strictEqual(result.rows[0][0], 3);
|
|
assert.strictEqual(result.rows[1][0], 4);
|
|
}); // 312.10
|
|
|
|
it('312.11 - bind and fetch a negative interval', async function() {
|
|
const bind = { dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: new oracledb.IntervalDS({ days: -1, hours: 23, minutes: 58,
|
|
seconds: 34, fseconds: 431152000 })
|
|
};
|
|
const result = await conn.execute(`SELECT :1 FROM DUAL`, [bind]);
|
|
assert.deepStrictEqual(result.rows[0][0], bind.val);
|
|
}); // 312.11
|
|
|
|
it('312.12 - fetch interval with maximum fractional seconds', async function() {
|
|
const maxFsInterval = new oracledb.IntervalDS({
|
|
days: 0,
|
|
hours: 0,
|
|
minutes: 0,
|
|
seconds: 0,
|
|
fseconds: 999999999
|
|
});
|
|
|
|
const result = await conn.execute(
|
|
`SELECT :1 FROM DUAL`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: maxFsInterval
|
|
}]
|
|
);
|
|
|
|
assert.deepStrictEqual(result.rows[0][0], maxFsInterval);
|
|
}); // 312.12
|
|
|
|
it('312.13 - fetch interval with maximum days for DAY(7) precision', async function() {
|
|
const result = await conn.execute(
|
|
`SELECT TO_DSINTERVAL('9999999 23:59:59.999999999') FROM DUAL`
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0], new oracledb.IntervalDS({
|
|
days: 9999999,
|
|
hours: 23,
|
|
minutes: 59,
|
|
seconds: 59,
|
|
fseconds: 999999999
|
|
}));
|
|
}); // 312.13
|
|
|
|
it('312.14 - insert interval exceeding day precision (DAY(7))', async function() {
|
|
const value = new oracledb.IntervalDS({ days: 10000000, hours: 0,
|
|
minutes: 0, seconds: 0, fseconds: 0 });
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
`INSERT INTO ${tableName} (IntCol, IntervalCol, NullableCol, IntervalPrecisionCol, IntervalPrecisionScaleCol)
|
|
VALUES (11, TO_DSINTERVAL('1 00:00:00'), NULL, :value, TO_DSINTERVAL('0 00:00:00'))`,
|
|
{ value: { val: value, type: oracledb.DB_TYPE_INTERVAL_DS } }
|
|
),
|
|
/ORA-01873:/ //ORA-01873: the leading precision of the interval is too small
|
|
);
|
|
}); // 312.14
|
|
|
|
it('312.15 - interval components exceeding normal ranges', async function() {
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
`SELECT TO_DSINTERVAL('1 25:70:70.999999999') FROM DUAL`
|
|
),
|
|
/ORA-01850:/ // ORA-01850: hour must be between 0 and 23
|
|
);
|
|
}); // 312.15
|
|
|
|
it('312.16 - select rows where NullableCol is NULL', async function() {
|
|
const result = await conn.execute(
|
|
`SELECT COUNT(*) FROM ${tableName} WHERE NullableCol IS NULL`
|
|
);
|
|
assert.strictEqual(result.rows[0][0], 5);
|
|
}); // 312.16
|
|
|
|
it('312.17 - verify non-NULL NullableCol value for IntCol=1', async function() {
|
|
const result = await conn.execute(
|
|
`SELECT NullableCol FROM ${tableName} WHERE IntCol = 1`
|
|
);
|
|
const expected = new oracledb.IntervalDS({
|
|
days: 6,
|
|
hours: 3,
|
|
minutes: 7,
|
|
seconds: 8,
|
|
fseconds: 0
|
|
});
|
|
assert.deepStrictEqual(result.rows[0][0], expected);
|
|
}); // 312.17
|
|
|
|
it('312.18 - select rows where IntervalCol > 5 days', async function() {
|
|
const value = new oracledb.IntervalDS({ days: 5, hours: 0, minutes: 0, seconds: 0, fseconds: 0 });
|
|
const result = await conn.execute(
|
|
`SELECT IntCol FROM ${tableName} WHERE IntervalCol > :value`,
|
|
{ value: { val: value, type: oracledb.DB_TYPE_INTERVAL_DS } }
|
|
);
|
|
assert.deepStrictEqual(result.rows, [[5], [6], [7], [8], [9], [10]]);
|
|
}); // 312.18
|
|
|
|
it('312.19 - interval arithmetic in SQL', async function() {
|
|
const result = await conn.execute(`
|
|
SELECT TO_DSINTERVAL('1 02:30:00') + TO_DSINTERVAL('3 12:45:15.123') FROM DUAL
|
|
`);
|
|
const expected = new oracledb.IntervalDS({
|
|
days: 4,
|
|
hours: 15,
|
|
minutes: 15,
|
|
seconds: 15,
|
|
fseconds: 123000000
|
|
});
|
|
assert.deepStrictEqual(result.rows[0][0], expected);
|
|
}); // 312.19
|
|
|
|
it('312.20 - select maximum IntervalCol from table', async function() {
|
|
const result = await conn.execute(
|
|
`SELECT MAX(IntervalCol) FROM ${tableName}`
|
|
);
|
|
const expected = new oracledb.IntervalDS({
|
|
days: 10,
|
|
hours: 10,
|
|
minutes: 20,
|
|
seconds: 30,
|
|
fseconds: 0
|
|
});
|
|
assert.deepStrictEqual(result.rows[0][0], expected);
|
|
}); // 312.20
|
|
|
|
it('312.21 - test interval with null and undefined values', async function() {
|
|
// Multi-row insert is supported only from 23ai DB
|
|
if (conn.oracleServerVersion < 2306000000)
|
|
this.skip();
|
|
|
|
const sql = `CREATE TABLE IntervalDSNullTest (
|
|
Col1 INTERVAL DAY TO SECOND,
|
|
Col2 INTERVAL DAY TO SECOND
|
|
)`;
|
|
await conn.execute(testsUtil.sqlCreateTable('IntervalDSNullTest', sql));
|
|
|
|
await conn.execute(`
|
|
INSERT INTO IntervalDSNullTest VALUES
|
|
(TO_DSINTERVAL('5 06:30:45'), NULL),
|
|
(NULL, TO_DSINTERVAL('2 12:30:15'))
|
|
`);
|
|
|
|
const result = await conn.execute(
|
|
`SELECT * FROM IntervalDSNullTest WHERE Col2 IS NULL OR Col1 IS NULL`
|
|
);
|
|
assert.strictEqual(result.rows.length, 2);
|
|
await testsUtil.dropTable(conn, 'IntervalDSNullTest');
|
|
}); // 312.21
|
|
|
|
it('312.22 - SELECT INTERVAL with seconds only', async function() {
|
|
const result = await conn.execute("SELECT INTERVAL '900' SECOND(3) FROM DUAL");
|
|
assert.deepStrictEqual(result.rows[0][0],
|
|
new oracledb.IntervalDS({ days: 0, hours: 0, minutes: 15, seconds: 0, fseconds: 0 }));
|
|
}); // 312.22
|
|
|
|
it('312.23 - SELECT INTERVAL with fractional seconds overflow', async function() {
|
|
const result = await conn.execute(
|
|
"SELECT TO_DSINTERVAL('0 00:00:01.999999999') FROM DUAL"
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0],
|
|
new oracledb.IntervalDS({ seconds: 1, fseconds: 999999999 }));
|
|
}); // 312.23
|
|
|
|
it('312.24 - invalid interval format', async function() {
|
|
await assert.rejects(
|
|
async () => await conn.execute("SELECT TO_DSINTERVAL('INVALID') FROM DUAL"),
|
|
/ORA-01867:/ // ORA-01867: the interval is invalid
|
|
);
|
|
}); // 312.24
|
|
|
|
it('312.25 - invalid attribute in bind value', async function() {
|
|
const invalidInterval = { day: 5, hours: 10 }; // Misspelled 'days'
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
`SELECT :1 FROM DUAL`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: invalidInterval
|
|
}]
|
|
),
|
|
/NJS-011:/ // NJS-011: encountered bind value and type mismatch
|
|
);
|
|
}); // 312.25
|
|
|
|
it('312.26 - negative precision for interval', async function() {
|
|
await assert.rejects(
|
|
async () => await conn.execute(`CREATE TABLE InvalidDSTest (
|
|
Col INTERVAL DAY(-1) TO SECOND
|
|
)`),
|
|
/ORA-30088:/ // ORA-30088: datetime/interval precision is out of range
|
|
);
|
|
}); // 312.26
|
|
|
|
it('312.27 - non-numeric input for interval', function() {
|
|
assert.throws(
|
|
() => new oracledb.IntervalDS({ days: 'five', seconds: 'ten' }),
|
|
/NJS-007: invalid value for "days" in parameter 1/
|
|
);
|
|
}); // 312.27
|
|
|
|
it('312.28 - interval arithmetic with invalid types', async function() {
|
|
await assert.rejects(
|
|
async () => await conn.execute(`
|
|
BEGIN
|
|
DECLARE
|
|
v_interval INTERVAL DAY TO SECOND := TO_DSINTERVAL('5 06:30:00');
|
|
v_result INTERVAL DAY TO SECOND;
|
|
BEGIN
|
|
v_result := v_interval + 'INVALID';
|
|
END;
|
|
END;`
|
|
),
|
|
/ORA-01867:/ // ORA-01867: the interval is invalid
|
|
);
|
|
|
|
const bindVars = {
|
|
invalidValue: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_VARCHAR,
|
|
val: 'INVALID'
|
|
}
|
|
};
|
|
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
`BEGIN
|
|
DECLARE
|
|
v_interval INTERVAL DAY TO SECOND := TO_DSINTERVAL('5 06:30:00');
|
|
v_result INTERVAL DAY TO SECOND;
|
|
BEGIN
|
|
v_result := v_interval + :invalidValue;
|
|
END;
|
|
END;`,
|
|
bindVars),
|
|
/ORA-01867:/ // ORA-01867: the interval is invalid
|
|
);
|
|
}); // 312.28
|
|
|
|
it('312.29 - day and seconds precision', async function() {
|
|
const createTableSQL = `CREATE TABLE IntervalWithPrecision (
|
|
Col INTERVAL DAY(3) TO SECOND(3)
|
|
)`;
|
|
await conn.execute(testsUtil.sqlCreateTable('IntervalWithPrecision', createTableSQL));
|
|
|
|
const invalidInterval = new oracledb.IntervalDS({
|
|
days: 1000, // Exceeds DAY PRECISION
|
|
hours: 0,
|
|
minutes: 0,
|
|
seconds: 1,
|
|
fseconds: 999999999 // fseconds value is truncated
|
|
});
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
`INSERT INTO IntervalWithPrecision VALUES (:1)`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: invalidInterval
|
|
}]
|
|
),
|
|
/ORA-01873:/ //ORA-01873: the leading precision of the interval is too small
|
|
);
|
|
|
|
let result, roundedInterval;
|
|
|
|
roundedInterval = new oracledb.IntervalDS({
|
|
days: 100,
|
|
seconds: 1,
|
|
fseconds: 999455555 // fseconds value is rounded off to 3 decimal places
|
|
}); // '100 00:00:01.999455555' is rounded off to '100 00:00:01.999'
|
|
|
|
result = await conn.execute(
|
|
`INSERT INTO IntervalWithPrecision VALUES (:1)`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: roundedInterval
|
|
}]
|
|
);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
result = await conn.execute('SELECT * FROM IntervalWithPrecision');
|
|
assert.strictEqual(result.rows[0][0].fseconds, 999000000);
|
|
|
|
roundedInterval = new oracledb.IntervalDS({
|
|
days: 100,
|
|
seconds: 1,
|
|
fseconds: 999999999 // fseconds value is rounded off to 3 decimal places
|
|
}); // '100 00:00:01.999999999' is rounded off to '100 00:00:02.000'
|
|
|
|
result = await conn.execute(
|
|
`INSERT INTO IntervalWithPrecision VALUES (:1)`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: roundedInterval
|
|
}]
|
|
);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
result = await conn.execute('SELECT * FROM IntervalWithPrecision');
|
|
assert.strictEqual(result.rows[1][0].seconds, 2);
|
|
assert.strictEqual(result.rows[1][0].fseconds, 0);
|
|
await testsUtil.dropTable(conn, 'IntervalWithPrecision');
|
|
}); // 312.29
|
|
|
|
it('312.30 - interval comparison with invalid format', async function() {
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
`SELECT * FROM ${tableName} WHERE IntervalCol = TO_DSINTERVAL(:1)`,
|
|
['INVALID-FORMAT']
|
|
),
|
|
/ORA-01867:/ // ORA-01867: the interval is invalid
|
|
);
|
|
}); // 312.30
|
|
|
|
it('312.31 - interval with null attributes in bind', function() {
|
|
const nullIntervalObj = { days: null, hours: null, minutes: null,
|
|
seconds: null, fseconds: null };
|
|
assert.throws(
|
|
() => new oracledb.IntervalDS(nullIntervalObj),
|
|
/NJS-007: invalid value for "days" in parameter 1/
|
|
);
|
|
}); // 312.31
|
|
|
|
it('312.32 - maximum allowed interval value', async function() {
|
|
const result = await conn.execute(
|
|
"SELECT TO_DSINTERVAL('9999999 23:59:59.999999999') FROM DUAL"
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0], new oracledb.IntervalDS({
|
|
days: 9999999,
|
|
hours: 23,
|
|
minutes: 59,
|
|
seconds: 59,
|
|
fseconds: 999999999
|
|
}));
|
|
}); // 312.32
|
|
|
|
it('312.33 - interval with all components at maximum', async function() {
|
|
const bindVal = new oracledb.IntervalDS({
|
|
days: 9999999,
|
|
hours: 23,
|
|
minutes: 59,
|
|
seconds: 59,
|
|
fseconds: 999999999
|
|
});
|
|
const result = await conn.execute(
|
|
'SELECT :1 FROM DUAL',
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: bindVal
|
|
}]
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0], bindVal);
|
|
}); // 312.33
|
|
|
|
it('312.34 - bind and fetch interval with zero values', async function() {
|
|
// Test zero intervals
|
|
const zeroInterval = new oracledb.IntervalDS();
|
|
let result;
|
|
|
|
// Bind in a complete zero interval
|
|
result = await conn.execute(
|
|
`SELECT :1 FROM DUAL`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: zeroInterval
|
|
}]
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0], zeroInterval);
|
|
|
|
// Create an IntervalDS object with an empty JS object passed in!
|
|
const zeroIntervalWithEmptyObj = new oracledb.IntervalDS({});
|
|
|
|
// Bind in a complete zero interval
|
|
result = await conn.execute(
|
|
`SELECT :1 FROM DUAL`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: zeroIntervalWithEmptyObj
|
|
}]
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0], zeroInterval);
|
|
|
|
// Test partial zeros with fractional seconds
|
|
const zeroWithFraction = new oracledb.IntervalDS({
|
|
fseconds: 500000000 // 0.5 seconds
|
|
});
|
|
result = await conn.execute(
|
|
`SELECT :1 FROM DUAL`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: zeroWithFraction
|
|
}]
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0], zeroWithFraction);
|
|
|
|
// Test zero interval in table operations
|
|
await conn.execute(
|
|
`INSERT INTO ${tableName} VALUES (
|
|
11,
|
|
TO_DSINTERVAL('0 00:00:00'),
|
|
TO_DSINTERVAL('0 00:00:00.000'),
|
|
TO_DSINTERVAL('0 00:00:00'),
|
|
TO_DSINTERVAL('0 00:00:00')
|
|
)`
|
|
);
|
|
|
|
result = await conn.execute(
|
|
`SELECT IntervalCol FROM ${tableName} WHERE IntCol = 11`
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0], zeroInterval);
|
|
}); // 312.34
|
|
|
|
it('312.35 - interval with negative fractional seconds', async function() {
|
|
const createTableSQL = `CREATE TABLE IntervalTbl (
|
|
Col INTERVAL DAY TO SECOND
|
|
)`;
|
|
await conn.execute(testsUtil.sqlCreateTable('IntervalTbl', createTableSQL));
|
|
|
|
const bindVal = new oracledb.IntervalDS({
|
|
days: 2,
|
|
hours: 3,
|
|
minutes: 15,
|
|
seconds: 45,
|
|
fseconds: -500000000 // Negative 0.5 seconds
|
|
});
|
|
|
|
let result;
|
|
result = await conn.execute(
|
|
`INSERT INTO IntervalTbl VALUES (:1)`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: bindVal
|
|
}]
|
|
);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
result = await conn.execute('SELECT * FROM IntervalTbl');
|
|
assert.strictEqual(result.rows[0][0].seconds, 45);
|
|
assert.strictEqual(result.rows[0][0].fseconds, -500000000);
|
|
await testsUtil.dropTable(conn, 'IntervalTbl');
|
|
}); // 312.35
|
|
|
|
it('312.36 - interval multiplication in SQL', async function() {
|
|
const result = await conn.execute(`
|
|
SELECT 2 * TO_DSINTERVAL('1 12:30:00') FROM DUAL
|
|
`);
|
|
const expected = new oracledb.IntervalDS({
|
|
days: 3,
|
|
hours: 1,
|
|
minutes: 0,
|
|
seconds: 0,
|
|
fseconds: 0
|
|
});
|
|
assert.deepStrictEqual(result.rows[0][0], expected);
|
|
}); // 312.36
|
|
|
|
it('312.37 - interval subtraction in SQL', async function() {
|
|
const result = await conn.execute(`
|
|
SELECT TO_DSINTERVAL('3 06:45:30') - TO_DSINTERVAL('1 12:15:15') FROM DUAL
|
|
`);
|
|
const expected = new oracledb.IntervalDS({
|
|
days: 1,
|
|
hours: 18,
|
|
minutes: 30,
|
|
seconds: 15,
|
|
fseconds: 0
|
|
});
|
|
assert.deepStrictEqual(result.rows[0][0], expected);
|
|
}); // 312.37
|
|
|
|
it('312.38 - SQL functions with intervals', async function() {
|
|
const bindVal = {
|
|
days: -2,
|
|
hours: 3,
|
|
minutes: 15,
|
|
seconds: 45,
|
|
fseconds: 0
|
|
};
|
|
|
|
// ABS() on interval
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
`SELECT ABS(TO_DSINTERVAL(:1)) FROM DUAL`,
|
|
[{ dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: new oracledb.IntervalDS(bindVal) }
|
|
]
|
|
),
|
|
/ORA-00932:/ /* ORA-00932: expression is of data type INTERVAL DAY TO SECOND,
|
|
which is incompatible with expected data type NUMBER
|
|
*/
|
|
);
|
|
}); // 312.38
|
|
|
|
it('312.39 - bulk inserts with intervals', async function() {
|
|
const sql = `INSERT INTO ${tableName} (IntCol, IntervalCol, NullableCol,
|
|
IntervalPrecisionCol, IntervalPrecisionScaleCol) VALUES (:1, :2, :3, :4, :5)`;
|
|
|
|
const binds = [
|
|
[12,
|
|
new oracledb.IntervalDS({ days: 1, hours: 1, minutes: 1, seconds: 1,
|
|
fseconds: 0 }), null, null, null
|
|
],
|
|
[13,
|
|
new oracledb.IntervalDS({ days: 2, hours: 2, minutes: 2, seconds: 2,
|
|
fseconds: 200000000 }), null, null, null
|
|
],
|
|
[14,
|
|
new oracledb.IntervalDS({ days: 3, hours: 33, minutes: 3, seconds: 3,
|
|
fseconds: 300000000 }), null, null, null
|
|
]
|
|
];
|
|
|
|
const options = { autoCommit: true, bindDefs: [
|
|
{ type: oracledb.DB_TYPE_NUMBER },
|
|
{ type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
{ type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
{ type: oracledb.DB_TYPE_INTERVAL_DS },
|
|
{ type: oracledb.DB_TYPE_INTERVAL_DS }
|
|
]};
|
|
|
|
let result;
|
|
result = await conn.executeMany(sql, binds, options);
|
|
assert.strictEqual(result.rowsAffected, 3);
|
|
|
|
result = await conn.execute(
|
|
`SELECT * FROM ${tableName} WHERE IntCol IN (12, 13, 14)`
|
|
);
|
|
assert.deepStrictEqual(result.rows.map((r) => r[0]), [binds[0][0], binds[1][0], binds[2][0]]);
|
|
assert.deepStrictEqual(result.rows.map((r) => r[1]), [binds[0][1], binds[1][1], binds[2][1]]);
|
|
assert.deepStrictEqual(result.rows.map((r) => r[2]), [null, null, null]);
|
|
assert.deepStrictEqual(result.rows.map((r) => r[3]), [null, null, null]);
|
|
assert.deepStrictEqual(result.rows.map((r) => r[4]), [null, null, null]);
|
|
}); // 312.39
|
|
|
|
it('312.40 - rounding fractional seconds during arithmetic', async function() {
|
|
const result = await conn.execute(`
|
|
SELECT TO_DSINTERVAL('0 00:00:01.999999999') + TO_DSINTERVAL('0 00:00:00.000000001') FROM DUAL
|
|
`);
|
|
assert.deepStrictEqual(result.rows[0][0], new oracledb.IntervalDS({
|
|
days: 0,
|
|
hours: 0,
|
|
minutes: 0,
|
|
seconds: 2,
|
|
fseconds: 0
|
|
}));
|
|
}); // 312.40
|
|
|
|
it('312.41 - Negative - bind a POJO instead of an IntervalDS object', async function() {
|
|
const invalidIntervalDSObj = { das: 10, years: 4, months: 5,
|
|
seconds: '1', fseconds: '2a' };
|
|
await assert.rejects(
|
|
async () => await conn.execute(
|
|
'SELECT :1 FROM DUAL',
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: invalidIntervalDSObj
|
|
}]
|
|
),
|
|
/NJS-011:/ // NJS-011: encountered bind value and type mismatch
|
|
);
|
|
}); // 312.41
|
|
|
|
it('312.42 - some invalid attribute values in bind', function() {
|
|
let invalidIntervalObj;
|
|
invalidIntervalObj = { days: 3, hours: 4, months: '5', seconds: 1,
|
|
fseconds: '2a' };
|
|
assert.throws(
|
|
() => new oracledb.IntervalDS(invalidIntervalObj),
|
|
/NJS-007: invalid value for "fseconds" in parameter 1/
|
|
);
|
|
|
|
invalidIntervalObj = { days: 3, hours: 4, minutes: 5.2, seconds: 1,
|
|
fseconds: '2a' };
|
|
assert.throws(
|
|
() => new oracledb.IntervalDS(invalidIntervalObj),
|
|
/NJS-007: invalid value for "minutes" in parameter 1/
|
|
);
|
|
}); // 312.42
|
|
|
|
it('312.43 - ignore additional invalid attribute in bind value', async function() {
|
|
const customInterval = { days: 5, hours: 12, minutes: 23, seconds: 34,
|
|
fseconds: 100, years: 40 };
|
|
const result = await conn.execute('SELECT :1 FROM dual',
|
|
[
|
|
{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: new oracledb.IntervalDS(customInterval),
|
|
},
|
|
]
|
|
);
|
|
assert.strictEqual(result.rows[0][0].days, customInterval.days);
|
|
assert.strictEqual(result.rows[0][0].hours, customInterval.hours);
|
|
assert.strictEqual(result.rows[0][0].minutes, customInterval.minutes);
|
|
assert.strictEqual(result.rows[0][0].seconds, customInterval.seconds);
|
|
assert.strictEqual(result.rows[0][0].fseconds, customInterval.fseconds);
|
|
assert.strictEqual(result.rows[0][0].years, undefined);
|
|
}); // 312.43
|
|
|
|
it('312.44 - ignore invalid attribute in bind value', async function() {
|
|
const createTableSQL = `CREATE TABLE IntervalTbl (
|
|
Col INTERVAL DAY TO SECOND
|
|
)`;
|
|
await conn.execute(testsUtil.sqlCreateTable('IntervalTbl', createTableSQL));
|
|
|
|
const bindVal = {
|
|
days: 2,
|
|
hours: 3,
|
|
minutes: 15,
|
|
seconds: 45,
|
|
weeks: 5
|
|
};
|
|
|
|
let result;
|
|
result = await conn.execute(
|
|
`INSERT INTO IntervalTbl VALUES (:1)`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: new oracledb.IntervalDS(bindVal)
|
|
}]
|
|
);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
result = await conn.execute('SELECT * FROM IntervalTbl');
|
|
assert.strictEqual(result.rows[0][0].days, bindVal.days);
|
|
assert.strictEqual(result.rows[0][0].seconds, bindVal.seconds);
|
|
assert.strictEqual(result.rows[0][0].hours, bindVal.hours);
|
|
assert.strictEqual(result.rows[0][0].minutes, bindVal.minutes);
|
|
assert.strictEqual(result.rows[0][0].fseconds, 0);
|
|
assert.strictEqual(result.rows[0][0].weeks, undefined);
|
|
await testsUtil.dropTable(conn, 'IntervalTbl');
|
|
}); // 312.44
|
|
|
|
it('312.45 - insert and fetch interval with only days and seconds', async function() {
|
|
const createTableSQL = `CREATE TABLE IntervalTbl (
|
|
Col INTERVAL DAY TO SECOND
|
|
)`;
|
|
await conn.execute(testsUtil.sqlCreateTable('IntervalTbl', createTableSQL));
|
|
|
|
const bindVal = {
|
|
days: 2,
|
|
seconds: 40,
|
|
};
|
|
|
|
let result;
|
|
result = await conn.execute(
|
|
`INSERT INTO IntervalTbl VALUES (:1)`,
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: new oracledb.IntervalDS(bindVal)
|
|
}]
|
|
);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
result = await conn.execute('SELECT * FROM IntervalTbl');
|
|
assert.strictEqual(result.rows[0][0].days, bindVal.days);
|
|
assert.strictEqual(result.rows[0][0].seconds, bindVal.seconds);
|
|
assert.strictEqual(result.rows[0][0].hours, 0);
|
|
assert.strictEqual(result.rows[0][0].minutes, 0);
|
|
assert.strictEqual(result.rows[0][0].fseconds, 0);
|
|
await testsUtil.dropTable(conn, 'IntervalTbl');
|
|
}); // 312.45
|
|
|
|
|
|
it('312.46 - bind interval with only hours and minutes', async function() {
|
|
const bindVal = new oracledb.IntervalDS({ hours: 2, minutes: 15 });
|
|
const result = await conn.execute(
|
|
`SELECT :1 FROM DUAL`,
|
|
[{ dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_INTERVAL_DS, val: bindVal }]
|
|
);
|
|
assert(result.rows[0][0] instanceof oracledb.IntervalDS);
|
|
assert.deepStrictEqual(result.rows[0][0], bindVal);
|
|
assert.strictEqual(result.rows[0][0].days, 0);
|
|
assert.strictEqual(result.rows[0][0].hours, bindVal.hours);
|
|
assert.strictEqual(result.rows[0][0].minutes, bindVal.minutes);
|
|
assert.strictEqual(result.rows[0][0].seconds, 0);
|
|
assert.strictEqual(result.rows[0][0].fseconds, 0);
|
|
}); // 312.46
|
|
|
|
it('312.47 - constructor with partial attributes', function() {
|
|
// Test partial initialization (e.g., days only, hours only)
|
|
const intervalDaysOnly = new oracledb.IntervalDS({ days: 3 });
|
|
assert.strictEqual(intervalDaysOnly.days, 3);
|
|
assert.strictEqual(intervalDaysOnly.hours, 0);
|
|
assert.strictEqual(intervalDaysOnly.minutes, 0);
|
|
assert.strictEqual(intervalDaysOnly.seconds, 0);
|
|
assert.strictEqual(intervalDaysOnly.fseconds, 0);
|
|
|
|
const intervalHoursOnly = new oracledb.IntervalDS({ hours: 8 });
|
|
assert.strictEqual(intervalHoursOnly.days, 0);
|
|
assert.strictEqual(intervalHoursOnly.hours, 8);
|
|
}); // 312.47
|
|
|
|
it('312.48 - time component normalization during binding', async function() {
|
|
const interval = new oracledb.IntervalDS({ hours: 25, minutes: 70, seconds: 70, fseconds: 1000000000 });
|
|
const result = await conn.execute(
|
|
'SELECT :1 FROM dual',
|
|
[{ dir: oracledb.BIND_IN, type: oracledb.DB_TYPE_INTERVAL_DS, val: interval }]
|
|
);
|
|
assert.deepStrictEqual(result.rows[0][0],
|
|
new oracledb.IntervalDS({ days: 0, fseconds: 1000000000, hours: 25, minutes: 70, seconds: 70 }));
|
|
}); // 312.48
|
|
|
|
it('312.49 - undefined/null in constructor attributes', function() {
|
|
// Undefined should default to 0
|
|
const intervalUndefined = new oracledb.IntervalDS({ days: undefined, hours: 5 });
|
|
assert.strictEqual(intervalUndefined.days, 0);
|
|
assert.strictEqual(intervalUndefined.hours, 5);
|
|
|
|
// Null should throw validation error
|
|
assert.throws(() => {
|
|
new oracledb.IntervalDS({ days: null, hours: 3 });
|
|
}, /NJS-007:/);
|
|
}); // 312.49
|
|
|
|
it('312.50 - PL/SQL function returning IntervalDS', async function() {
|
|
// Create PL/SQL function
|
|
await conn.execute(`
|
|
CREATE OR REPLACE FUNCTION GetDSInterval RETURN INTERVAL DAY TO SECOND IS
|
|
BEGIN
|
|
RETURN TO_DSINTERVAL('5 06:30:45.123456789');
|
|
END;
|
|
`);
|
|
|
|
// Call function and verify result
|
|
const result = await conn.execute(
|
|
`BEGIN :out := GetDSInterval(); END;`,
|
|
{ out: { dir: oracledb.BIND_OUT, type: oracledb.DB_TYPE_INTERVAL_DS } }
|
|
);
|
|
assert.deepStrictEqual(result.outBinds.out,
|
|
new oracledb.IntervalDS({ days: 5, hours: 6, minutes: 30, seconds: 45, fseconds: 123457000 }));
|
|
|
|
await conn.execute(`DROP FUNCTION GetDSInterval`);
|
|
}); // 312.50
|
|
|
|
it('312.51 - reject non-IntervalDS binds', async function() {
|
|
// Test plain object instead of IntervalDS instance
|
|
await assert.rejects(
|
|
conn.execute(
|
|
'SELECT :1 FROM dual',
|
|
[{
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DB_TYPE_INTERVAL_DS,
|
|
val: { days: 2, hours: 3 } // Not an IntervalDS instance
|
|
}]
|
|
),
|
|
/NJS-011:/
|
|
);
|
|
}); // 312.51
|
|
|
|
it('312.52 - string values in constructor attributes', function() {
|
|
assert.throws(() => {
|
|
new oracledb.IntervalDS({ days: '5', hours: '6' });
|
|
}, /NJS-007:/);
|
|
}); // 312.52
|
|
|
|
it('312.53 - interval sorting in queries', async function() {
|
|
// Test ORDER BY with IntervalDS column
|
|
await conn.execute(`
|
|
CREATE TABLE IntervalDSSortTest (
|
|
Id NUMBER,
|
|
IntervalCol INTERVAL DAY TO SECOND
|
|
)
|
|
`);
|
|
|
|
await conn.executeMany(`
|
|
INSERT INTO IntervalDSSortTest VALUES (:1, TO_DSINTERVAL(:2))
|
|
`, [
|
|
[1, '1 06:00:00'],
|
|
[2, '0 12:30:00'],
|
|
[3, '2 00:00:00']
|
|
]);
|
|
|
|
const result = await conn.execute(`
|
|
SELECT Id FROM IntervalDSSortTest ORDER BY IntervalCol
|
|
`);
|
|
assert.deepStrictEqual(
|
|
result.rows.map(row => row[0]),
|
|
[2, 1, 3] // Expected order: 12h30m, 1d6h, 2d
|
|
);
|
|
|
|
await testsUtil.dropTable(conn, 'IntervalDSSortTest');
|
|
}); // 312.53
|
|
|
|
it('312.54 - interval arithmetic with multiple operations', async function() {
|
|
// Test chained arithmetic operations
|
|
const result = await conn.execute(`
|
|
BEGIN
|
|
DECLARE
|
|
v_interval INTERVAL DAY TO SECOND := TO_DSINTERVAL('5 06:30:45');
|
|
BEGIN
|
|
v_interval := v_interval + TO_DSINTERVAL('2 12:15:30');
|
|
v_interval := v_interval - TO_DSINTERVAL('1 00:00:00');
|
|
:result := v_interval;
|
|
END;
|
|
END;
|
|
`, {
|
|
result: { dir: oracledb.BIND_OUT, type: oracledb.DB_TYPE_INTERVAL_DS }
|
|
});
|
|
|
|
assert.deepStrictEqual(
|
|
result.outBinds.result,
|
|
new oracledb.IntervalDS({ days: 6, hours: 18, minutes: 46, seconds: 15 })
|
|
);
|
|
}); // 312.54
|
|
});
|