1626 lines
54 KiB
JavaScript
1626 lines
54 KiB
JavaScript
/* Copyright (c) 2019, 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
|
|
* 290. dbObject20.js
|
|
*
|
|
* DESCRIPTION
|
|
* Test the Oracle data type Object on NCHAR, NVARCHAR and RAW.
|
|
*
|
|
*****************************************************************************/
|
|
'use strict';
|
|
|
|
const oracledb = require('oracledb');
|
|
const assert = require('assert');
|
|
const dbConfig = require('./dbconfig.js');
|
|
const testsUtil = require('./testsUtil.js');
|
|
const assist = require('./dataTypeAssist.js');
|
|
|
|
describe('290. dbObject20.js', () => {
|
|
let conn;
|
|
const TABLE = 'NODB_TAB_OBJ';
|
|
|
|
const proc1 =
|
|
`create or replace procedure nodb_getDataCursor1(p_cur out sys_refcursor) is
|
|
begin
|
|
open p_cur for
|
|
SELECT
|
|
* FROM
|
|
${TABLE}
|
|
WHERE num >= 108;
|
|
end; `;
|
|
|
|
const proc2 =
|
|
`create or replace procedure nodb_getDataCursor2(p_cur out sys_refcursor) is
|
|
begin
|
|
open p_cur for
|
|
SELECT
|
|
* FROM
|
|
${TABLE}
|
|
WHERE num >= 300;
|
|
end; `;
|
|
|
|
const proc3 =
|
|
`create or replace procedure nodb_getDataCursor3(
|
|
p_cur1 out sys_refcursor,
|
|
p_cur2 out sys_refcursor
|
|
) is
|
|
begin
|
|
nodb_getDataCursor1(p_cur1);
|
|
nodb_getDataCursor2(p_cur2);
|
|
end;`;
|
|
|
|
describe('290.1 db Object tests with NCHAR datatype', () => {
|
|
const TYPE = 'NODB_TYP_OBJ_1';
|
|
before(async () => {
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
let sql =
|
|
`CREATE OR REPLACE TYPE ${TYPE} AS OBJECT (
|
|
id NUMBER,
|
|
name NCHAR(30)
|
|
);`;
|
|
|
|
await testsUtil.createType(conn, TYPE, sql);
|
|
|
|
sql =
|
|
`CREATE TABLE ${TABLE} (
|
|
num NUMBER,
|
|
person ${TYPE}
|
|
)`;
|
|
await testsUtil.createTable(conn, TABLE, sql);
|
|
|
|
await conn.execute(proc1);
|
|
await conn.execute(proc2);
|
|
await conn.execute(proc3);
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
await testsUtil.dropTable(conn, TABLE);
|
|
await testsUtil.dropType(conn, TYPE);
|
|
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor3');
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor2');
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor1');
|
|
|
|
await conn.close();
|
|
}); // after()
|
|
|
|
it('290.1.1 insert an object with numeric/string values in NCHAR datatype', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 201,
|
|
NAME: 'ABC'
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 101;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'].trim(), objData.NAME);
|
|
}); // 290.1.1
|
|
|
|
it('290.1.2 insert an object with null string values', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 203,
|
|
NAME: null
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 103;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'], null);
|
|
}); // 290.1.2
|
|
|
|
it('290.1.3 insert an object with undefined string values', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 205,
|
|
NAME: undefined
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 105;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'], null);
|
|
}); // 290.1.3
|
|
|
|
it('290.1.4 insert an empty object - no attributes', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = { };
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 106;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.ifError(result.rows[0][1]['ID']);
|
|
assert.ifError(result.rows[0][1]['NAME']);
|
|
}); // 290.1.4
|
|
|
|
it('290.1.5 insert data via binding by object', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
const objData = {
|
|
ID: 207,
|
|
NAME: 'ABC'
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 107;
|
|
|
|
let result = await conn.execute(sql, { a: seq, b: testObj });
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql, [], { outFormat: oracledb.OBJECT });
|
|
|
|
assert.strictEqual(result.rows[0].NUM, seq);
|
|
assert.strictEqual(result.rows[0].PERSON['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0].PERSON.NAME.trim(), objData.NAME);
|
|
}); // 290.1.5
|
|
|
|
it('290.1.6 insert multiple rows using executeMany() with inferred data type', async () => {
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
let initialID = 208;
|
|
const initialSeq = 108;
|
|
|
|
const objDataArray = [
|
|
{
|
|
ID: initialID,
|
|
NAME: 'ABC'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'LMN'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'XYZ'
|
|
}
|
|
];
|
|
const bindArray = [];
|
|
let seq, objDataObj;
|
|
for (let i = 0; i < objDataArray.length; i++) {
|
|
seq = initialSeq + i;
|
|
objDataObj = new objClass(objDataArray[i]);
|
|
bindArray[i] = { a: seq, b: objDataObj };
|
|
}
|
|
|
|
const options = { autoCommit: true };
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
|
|
let result = await conn.executeMany(sql, bindArray, options);
|
|
assert.strictEqual(result.rowsAffected, objDataArray.length);
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num >= ${initialSeq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
for (let j = 0; j < objDataArray.length; j++) {
|
|
assert.strictEqual(result.rows[j][0], (initialSeq + j));
|
|
assert.strictEqual(result.rows[j][1]['ID'], objDataArray[j].ID);
|
|
assert.strictEqual(result.rows[j][1].NAME.trim(), objDataArray[j].NAME);
|
|
}
|
|
}); // 290.1.6
|
|
|
|
it('290.1.7 insert multiple rows using executeMany() with explicit data type', async () => {
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
let initialID = 3000;
|
|
const initialSeq = 300;
|
|
|
|
const objDataArray = [
|
|
{
|
|
ID: initialID,
|
|
NAME: 'ABC'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'LMN'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'XYZ'
|
|
}
|
|
];
|
|
const bindArray = [];
|
|
let seq, objDataObj;
|
|
for (let i = 0; i < objDataArray.length; i++) {
|
|
seq = initialSeq + i;
|
|
objDataObj = new objClass(objDataArray[i]);
|
|
bindArray[i] = { a: seq, b: objDataObj };
|
|
}
|
|
|
|
const options = {
|
|
autoCommit: true,
|
|
bindDefs: { a: { type: oracledb.NUMBER}, b: { type: objClass } }
|
|
};
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
|
|
let result = await conn.executeMany(sql, bindArray, options);
|
|
assert.strictEqual(result.rowsAffected, objDataArray.length);
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num >= ${initialSeq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
for (let j = 0; j < objDataArray.length; j++) {
|
|
assert.strictEqual(result.rows[j][0], (initialSeq + j));
|
|
assert.strictEqual(result.rows[j][1]['ID'], objDataArray[j].ID);
|
|
assert.strictEqual(result.rows[j][1].NAME.trim(), objDataArray[j].NAME);
|
|
}
|
|
}); // 290.1.7
|
|
|
|
it('290.1.8 call procedure with 2 OUT binds of DbObject', async function() {
|
|
|
|
const result = await conn.execute(
|
|
`BEGIN nodb_getDataCursor3(p_cur1 => :p_cur1,
|
|
p_cur2 => :p_cur2); end;`,
|
|
{
|
|
p_cur1: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT},
|
|
p_cur2: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
|
|
}
|
|
);
|
|
|
|
let resultSet = await result.outBinds.p_cur1.getRows();
|
|
assert.equal(resultSet.length, 6);
|
|
result.outBinds.p_cur1.close();
|
|
|
|
resultSet = await result.outBinds.p_cur2.getRows();
|
|
assert.equal(resultSet.length, 3);
|
|
result.outBinds.p_cur2.close();
|
|
}); // 290.1.8
|
|
|
|
it('290.1.9 copy an object with numeric/string values in NCHAR datatype', async () => {
|
|
const objData = {
|
|
ID: 201,
|
|
NAME: 'ABC'
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
|
|
const newObj = testObj.copy();
|
|
assert.strictEqual(newObj.ID, testObj.ID);
|
|
assert.strictEqual(newObj.NAME, testObj.NAME);
|
|
|
|
newObj.ID = 202;
|
|
assert.strictEqual(newObj.ID, 202);
|
|
assert.strictEqual(testObj.ID, 201);
|
|
}); // 290.1.9
|
|
});
|
|
|
|
describe('290.2 db Object tests with NVARCHAR2 datatype', () => {
|
|
const TYPE = 'NODB_TYP_OBJ_2';
|
|
before(async () => {
|
|
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
let sql =
|
|
`CREATE OR REPLACE TYPE ${TYPE} AS OBJECT (
|
|
id NUMBER,
|
|
name NVARCHAR2(30)
|
|
);`;
|
|
|
|
await testsUtil.createType(conn, TYPE, sql);
|
|
|
|
sql =
|
|
`CREATE TABLE ${TABLE} (
|
|
num NUMBER,
|
|
person ${TYPE}
|
|
)`;
|
|
await testsUtil.createTable(conn, TABLE, sql);
|
|
|
|
await conn.execute(proc1);
|
|
await conn.execute(proc2);
|
|
await conn.execute(proc3);
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
await testsUtil.dropTable(conn, TABLE);
|
|
await testsUtil.dropType(conn, TYPE);
|
|
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor3');
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor2');
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor1');
|
|
await conn.close();
|
|
}); // after()
|
|
|
|
it('290.2.1 insert an object with numeric/string values in NVARCHAR2 datatype', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 201,
|
|
NAME: 'ABC'
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 101;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'], objData.NAME);
|
|
}); // 290.2.1
|
|
|
|
it('290.2.2 insert an object with null string values', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 203,
|
|
NAME: null
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 103;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'], null);
|
|
}); // 290.2.2
|
|
|
|
it('290.2.3 insert an object with undefined string values', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 205,
|
|
NAME: undefined
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 105;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'], null);
|
|
}); // 290.2.3
|
|
|
|
it('290.2.4 insert an empty object - no attributes', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = { };
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 106;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.ifError(result.rows[0][1]['ID']);
|
|
assert.ifError(result.rows[0][1]['NAME']);
|
|
}); // 290.2.4
|
|
|
|
it('290.2.5 insert data via binding by object', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
const objData = {
|
|
ID: 207,
|
|
NAME: 'ABC'
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 107;
|
|
|
|
let result = await conn.execute(sql, { a: seq, b: testObj });
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql, [], { outFormat: oracledb.OBJECT });
|
|
|
|
assert.strictEqual(result.rows[0].NUM, seq);
|
|
assert.strictEqual(result.rows[0].PERSON['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0].PERSON.NAME, objData.NAME);
|
|
}); // 290.2.5
|
|
|
|
it('290.2.6 insert multiple rows using executeMany() with inferred data type', async () => {
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
let initialID = 208;
|
|
const initialSeq = 108;
|
|
|
|
const objDataArray = [
|
|
{
|
|
ID: initialID,
|
|
NAME: 'ABC'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'LMN'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'XYZ'
|
|
}
|
|
];
|
|
const bindArray = [];
|
|
let seq, objDataObj;
|
|
for (let i = 0; i < objDataArray.length; i++) {
|
|
seq = initialSeq + i;
|
|
objDataObj = new objClass(objDataArray[i]);
|
|
bindArray[i] = { a: seq, b: objDataObj };
|
|
}
|
|
|
|
const options = { autoCommit: true };
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
|
|
let result = await conn.executeMany(sql, bindArray, options);
|
|
assert.strictEqual(result.rowsAffected, objDataArray.length);
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num >= ${initialSeq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
for (let j = 0; j < objDataArray.length; j++) {
|
|
assert.strictEqual(result.rows[j][0], (initialSeq + j));
|
|
assert.strictEqual(result.rows[j][1]['ID'], objDataArray[j].ID);
|
|
assert.strictEqual(result.rows[j][1].NAME, objDataArray[j].NAME);
|
|
}
|
|
}); // 290.2.6
|
|
|
|
it('290.2.7 insert multiple rows using executeMany() with explicit data type', async () => {
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
let initialID = 3000;
|
|
const initialSeq = 300;
|
|
|
|
const objDataArray = [
|
|
{
|
|
ID: initialID,
|
|
NAME: 'ABC'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'LMN'
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: 'XYZ'
|
|
}
|
|
];
|
|
const bindArray = [];
|
|
let seq, objDataObj;
|
|
for (let i = 0; i < objDataArray.length; i++) {
|
|
seq = initialSeq + i;
|
|
objDataObj = new objClass(objDataArray[i]);
|
|
bindArray[i] = { a: seq, b: objDataObj };
|
|
}
|
|
|
|
const options = {
|
|
autoCommit: true,
|
|
bindDefs: { a: { type: oracledb.NUMBER}, b: { type: objClass } }
|
|
};
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
|
|
let result = await conn.executeMany(sql, bindArray, options);
|
|
assert.strictEqual(result.rowsAffected, objDataArray.length);
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num >= ${initialSeq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
for (let j = 0; j < objDataArray.length; j++) {
|
|
assert.strictEqual(result.rows[j][0], (initialSeq + j));
|
|
assert.strictEqual(result.rows[j][1]['ID'], objDataArray[j].ID);
|
|
assert.strictEqual(result.rows[j][1].NAME, objDataArray[j].NAME);
|
|
}
|
|
}); // 290.2.7
|
|
|
|
it('290.2.8 call procedure with 2 OUT binds of DbObject', async function() {
|
|
|
|
const result = await conn.execute(
|
|
`BEGIN nodb_getDataCursor3(p_cur1 => :p_cur1,
|
|
p_cur2 => :p_cur2); end;`,
|
|
{
|
|
p_cur1: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT},
|
|
p_cur2: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
|
|
}
|
|
);
|
|
|
|
let resultSet = await result.outBinds.p_cur1.getRows();
|
|
assert.equal(resultSet.length, 6);
|
|
result.outBinds.p_cur1.close();
|
|
|
|
resultSet = await result.outBinds.p_cur2.getRows();
|
|
assert.equal(resultSet.length, 3);
|
|
result.outBinds.p_cur2.close();
|
|
}); // 290.2.8
|
|
});
|
|
|
|
describe('290.3 db Object tests with RAW datatype', () => {
|
|
const TYPE = 'NODB_TYP_OBJ_3';
|
|
before(async () => {
|
|
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
let sql =
|
|
`CREATE OR REPLACE TYPE ${TYPE} AS OBJECT (
|
|
id NUMBER,
|
|
name RAW(30)
|
|
);`;
|
|
|
|
await testsUtil.createType(conn, TYPE, sql);
|
|
|
|
sql =
|
|
`CREATE TABLE ${TABLE} (
|
|
num NUMBER,
|
|
person ${TYPE}
|
|
)`;
|
|
|
|
await testsUtil.createTable(conn, TABLE, sql);
|
|
|
|
await conn.execute(proc1);
|
|
await conn.execute(proc2);
|
|
await conn.execute(proc3);
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
await testsUtil.dropTable(conn, TABLE);
|
|
await testsUtil.dropType(conn, TYPE);
|
|
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor3');
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor2');
|
|
await testsUtil.dropSource(conn, 'PROCEDURE', 'nodb_getDataCursor1');
|
|
await conn.close();
|
|
}); // after()
|
|
|
|
it('290.3.1 insert an object with numeric/string values in RAW datatype', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 201,
|
|
NAME: Buffer.from('ABC')
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 101;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.deepStrictEqual(result.rows[0][1]['NAME'], objData.NAME);
|
|
}); // 290.3.1
|
|
|
|
it('290.3.2 insert an object with null string values', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 203,
|
|
NAME: null
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 103;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'], null);
|
|
}); // 290.3.2
|
|
|
|
it('290.3.3 insert an object with undefined string values', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 205,
|
|
NAME: undefined
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 105;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.strictEqual(result.rows[0][1]['NAME'], null);
|
|
}); // 290.3.3
|
|
|
|
it('290.3.4 insert an empty object - no attributes', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = { };
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 106;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.ifError(result.rows[0][1]['ID']);
|
|
assert.ifError(result.rows[0][1]['NAME']);
|
|
}); // 290.3.4
|
|
|
|
it('290.3.5 insert data via binding by object', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
const objData = {
|
|
ID: 207,
|
|
NAME: Buffer.from('ABC')
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 107;
|
|
|
|
let result = await conn.execute(sql, { a: seq, b: testObj });
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql, [], { outFormat: oracledb.OBJECT });
|
|
|
|
assert.strictEqual(result.rows[0].NUM, seq);
|
|
assert.strictEqual(result.rows[0].PERSON['ID'], objData.ID);
|
|
assert.deepStrictEqual(result.rows[0].PERSON.NAME, objData.NAME);
|
|
}); // 290.3.5
|
|
|
|
it('290.3.6 insert multiple rows using executeMany() with inferred data type', async () => {
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
let initialID = 208;
|
|
const initialSeq = 108;
|
|
|
|
const objDataArray = [
|
|
{
|
|
ID: initialID,
|
|
NAME: Buffer.from('ABC')
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: Buffer.from('LMN')
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: Buffer.from('XYZ')
|
|
}
|
|
];
|
|
const bindArray = [];
|
|
let seq, objDataObj;
|
|
for (let i = 0; i < objDataArray.length; i++) {
|
|
seq = initialSeq + i;
|
|
objDataObj = new objClass(objDataArray[i]);
|
|
bindArray[i] = { a: seq, b: objDataObj };
|
|
}
|
|
|
|
const options = { autoCommit: true };
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
|
|
let result = await conn.executeMany(sql, bindArray, options);
|
|
assert.strictEqual(result.rowsAffected, objDataArray.length);
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num >= ${initialSeq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
for (let j = 0; j < objDataArray.length; j++) {
|
|
assert.strictEqual(result.rows[j][0], (initialSeq + j));
|
|
assert.strictEqual(result.rows[j][1]['ID'], objDataArray[j].ID);
|
|
assert.deepStrictEqual(result.rows[j][1].NAME, objDataArray[j].NAME);
|
|
}
|
|
}); // 290.3.6
|
|
|
|
it('290.3.7 insert multiple rows using executeMany() with explicit data type', async () => {
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
let initialID = 3000;
|
|
const initialSeq = 300;
|
|
|
|
const objDataArray = [
|
|
{
|
|
ID: initialID,
|
|
NAME: Buffer.from('ABC')
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: Buffer.from('LMN')
|
|
},
|
|
{
|
|
ID: initialID++,
|
|
NAME: Buffer.from('XYZ')
|
|
}
|
|
];
|
|
const bindArray = [];
|
|
let seq, objDataObj;
|
|
for (let i = 0; i < objDataArray.length; i++) {
|
|
seq = initialSeq + i;
|
|
objDataObj = new objClass(objDataArray[i]);
|
|
bindArray[i] = { a: seq, b: objDataObj };
|
|
}
|
|
|
|
const options = {
|
|
autoCommit: true,
|
|
bindDefs: { a: { type: oracledb.NUMBER}, b: { type: objClass } }
|
|
};
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:a, :b)`;
|
|
|
|
let result = await conn.executeMany(sql, bindArray, options);
|
|
assert.strictEqual(result.rowsAffected, objDataArray.length);
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num >= ${initialSeq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
for (let j = 0; j < objDataArray.length; j++) {
|
|
assert.strictEqual(result.rows[j][0], (initialSeq + j));
|
|
assert.strictEqual(result.rows[j][1]['ID'], objDataArray[j].ID);
|
|
assert.deepStrictEqual(result.rows[j][1].NAME, objDataArray[j].NAME);
|
|
}
|
|
}); // 290.3.7
|
|
|
|
it('290.3.8 call procedure with 2 OUT binds of DbObject', async function() {
|
|
|
|
const result = await conn.execute(
|
|
`BEGIN nodb_getDataCursor3(p_cur1 => :p_cur1,
|
|
p_cur2 => :p_cur2); end;`,
|
|
{
|
|
p_cur1: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT},
|
|
p_cur2: {type: oracledb.CURSOR, dir: oracledb.BIND_OUT}
|
|
}
|
|
);
|
|
|
|
let resultSet = await result.outBinds.p_cur1.getRows();
|
|
assert.equal(resultSet.length, 6);
|
|
result.outBinds.p_cur1.close();
|
|
|
|
resultSet = await result.outBinds.p_cur2.getRows();
|
|
assert.equal(resultSet.length, 3);
|
|
result.outBinds.p_cur2.close();
|
|
}); // 290.3.8
|
|
|
|
it('290.3.9 insert an object with buffer value with size 10', async () => {
|
|
let sql = `INSERT INTO ${TABLE} VALUES (:1, :2)`;
|
|
const objData = {
|
|
ID: 210,
|
|
NAME: assist.createBuffer(10)
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
const testObj = new objClass(objData);
|
|
const seq = 210;
|
|
|
|
let result = await conn.execute(sql, [seq, testObj]);
|
|
assert.strictEqual(result.rowsAffected, 1);
|
|
await conn.commit();
|
|
|
|
sql = `SELECT * FROM ${TABLE} WHERE num = ${seq}`;
|
|
result = await conn.execute(sql);
|
|
|
|
assert.strictEqual(result.rows[0][0], seq);
|
|
assert.strictEqual(result.rows[0][1]['ID'], objData.ID);
|
|
assert.deepStrictEqual(result.rows[0][1]['NAME'], objData.NAME);
|
|
}); // 290.3.9
|
|
|
|
it('290.3.10 insert an object with buffer value with size 100', async () => {
|
|
const objData = {
|
|
ID: 211,
|
|
NAME: assist.createBuffer(100)
|
|
};
|
|
const objClass = await conn.getDbObjectClass(TYPE);
|
|
assert.throws(
|
|
() => new objClass(objData),
|
|
/NJS-142:/
|
|
);
|
|
}); // 290.3.10
|
|
});
|
|
|
|
describe(`290.4 db Object tests with Invalid values to attributes`, () => {
|
|
let conn;
|
|
const TYPE1 = 'NODB_TEST_INV_OBJ_TBL_LINE';
|
|
const TYPE2 = 'NODB_TEST_INV_OBJ_TBL';
|
|
const TYPE3 = 'NODB_TEST_INV_OBJ';
|
|
const TYPE4 = 'NODB_TEST_INV_OBJ_TBL_VARCHAR';
|
|
|
|
// GH issue https://github.com/oracle/node-oracledb/issues/1646
|
|
const TYPE5 = 'NODB_TEST_VARCHAR_CHAR';
|
|
const TYPE6 = 'NODB_TEST_VARCHAR_BYTE';
|
|
|
|
const TYPE7 = 'NODB_TEST_TBL_TBL'; // 2 levels of nesting
|
|
|
|
const maxVarCharLen = 4;
|
|
const maxVarNCharLen = 4;
|
|
const maxVarRawLen = 10;
|
|
const maxVarChar2CharLen = 100;
|
|
const maxVarChar2ByteLen = 100;
|
|
|
|
before(async () => {
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
|
|
const sqlStmts = [
|
|
{
|
|
type: TYPE1, sql: `CREATE TYPE ${TYPE1} FORCE AS OBJECT (LINE_ID NUMBER,
|
|
LINE_STR VARCHAR2(${maxVarCharLen}), LINE_FIXED_CHAR CHAR(${maxVarCharLen}),
|
|
LINE_NSTR NVARCHAR2(${maxVarNCharLen}), LINE_FIXED_NSTR NCHAR(${maxVarNCharLen}),
|
|
LINE_ID2 FLOAT, rawAttr RAW(${maxVarRawLen}))`
|
|
},
|
|
{
|
|
type: TYPE2, sql: `CREATE TYPE ${TYPE2} FORCE AS TABLE OF ${TYPE1}`
|
|
},
|
|
{ type: TYPE3, sql: `CREATE TYPE ${TYPE3} FORCE AS OBJECT (HEADER_ID NUMBER(5,2), TBL ${TYPE2})` },
|
|
{
|
|
type: TYPE4, sql: `CREATE OR REPLACE TYPE ${TYPE4} IS TABLE OF VARCHAR2 (${maxVarCharLen})`
|
|
},
|
|
{
|
|
type: TYPE5, sql: `CREATE OR REPLACE TYPE ${TYPE5} IS TABLE OF VARCHAR2 (${maxVarChar2CharLen} CHAR)`
|
|
},
|
|
{
|
|
type: TYPE6, sql: `CREATE OR REPLACE TYPE ${TYPE6} IS TABLE OF VARCHAR2 (${maxVarChar2ByteLen} BYTE)`
|
|
},
|
|
{
|
|
type: TYPE7, sql: `CREATE OR REPLACE TYPE ${TYPE7} IS TABLE OF ${TYPE2}`
|
|
}
|
|
];
|
|
|
|
for (const { type, sql } of sqlStmts) {
|
|
await testsUtil.createType(conn, type, sql);
|
|
}
|
|
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
if (conn) {
|
|
await testsUtil.dropType(conn, TYPE7);
|
|
await testsUtil.dropType(conn, TYPE6);
|
|
await testsUtil.dropType(conn, TYPE5);
|
|
await testsUtil.dropType(conn, TYPE4);
|
|
await testsUtil.dropType(conn, TYPE3);
|
|
await testsUtil.dropType(conn, TYPE2);
|
|
await testsUtil.dropType(conn, TYPE1);
|
|
await conn.close();
|
|
}
|
|
}); // after()
|
|
|
|
it('290.4.1 Invalid Values for nested property with collection', async () => {
|
|
//prepare data.
|
|
const expectedTBLEntries = [
|
|
{
|
|
LINE_ID: 9999.231412342, // precision and scale of attributes inside object are ignored
|
|
LINE_STR: "1".repeat(maxVarCharLen),
|
|
LINE_FIXED_CHAR: "1".repeat(maxVarCharLen),
|
|
LINE_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_FIXED_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_ID2: -8,
|
|
RAWATTR: Buffer.from("A".repeat(maxVarRawLen))
|
|
},
|
|
{
|
|
LINE_ID: -8,
|
|
LINE_STR: "1".repeat(maxVarCharLen),
|
|
LINE_FIXED_CHAR: "1".repeat(maxVarCharLen),
|
|
LINE_NSTR: "Ő".repeat(maxVarNCharLen),
|
|
LINE_FIXED_NSTR: "Ő".repeat(maxVarNCharLen),
|
|
LINE_ID2: -1234,
|
|
RAWATTR: Buffer.from("B".repeat(maxVarRawLen))
|
|
}
|
|
];
|
|
const data = {
|
|
HEADER_ID: 1,
|
|
TBL: expectedTBLEntries
|
|
};
|
|
const pInClass = await conn.getDbObjectClass(TYPE3);
|
|
const pOutClass = await conn.getDbObjectClass(TYPE3);
|
|
const pInObj = new pInClass(data);
|
|
const pOutObj = new pOutClass({}); //out obj inited as empty one
|
|
|
|
const pInObjCopy = pInObj.copy();
|
|
assert.strictEqual(JSON.stringify(pInObj), JSON.stringify(pInObjCopy));
|
|
assert.strictEqual(pInObjCopy.HEADER_ID, pInObj.HEADER_ID);
|
|
assert.strictEqual(pInObjCopy.TBL.length, pInObj.TBL.length);
|
|
|
|
// Delete the 2nd element in the collection of the copied object.
|
|
// Ensure that original object data is untouched.
|
|
pInObjCopy.TBL.deleteElement(1);
|
|
// The length doesn't change when deletions occur.
|
|
// So, we use the map size here.
|
|
assert.strictEqual(pInObjCopy.TBL.toMap().size, 1);
|
|
assert.strictEqual(pInObj.TBL.length, 2);
|
|
|
|
pInObjCopy.TBL[0].LINE_ID = 1000;
|
|
assert.strictEqual(pInObjCopy.TBL[0].LINE_ID, 1000);
|
|
assert.strictEqual(pInObj.TBL[0].LINE_ID, 9999.231412342);
|
|
|
|
// create Procedure.
|
|
const PROC = 'nodb_proc_test2029041';
|
|
const createProc = `
|
|
CREATE OR REPLACE PROCEDURE ${PROC}
|
|
(a IN ${TYPE3}, b IN OUT ${TYPE3}) AS
|
|
BEGIN
|
|
b := a;
|
|
END;
|
|
`;
|
|
let result = await conn.execute(createProc);
|
|
|
|
// Call procedure.
|
|
const plsql = `BEGIN ${PROC} (:pIn, :pOut); END;`;
|
|
const bindVar = {
|
|
pIn: { val: pInObj, dir: oracledb.BIND_IN },
|
|
pOut: { val: pOutObj, dir: oracledb.BIND_INOUT },
|
|
};
|
|
result = await conn.execute(plsql, bindVar);
|
|
|
|
// Verify the result.
|
|
assert.strictEqual(JSON.stringify(data), JSON.stringify(result.outBinds.pOut));
|
|
|
|
// Assign large value greater than maxVarCharLen.
|
|
// Verify proper error message is thrown in different binding methods.
|
|
data.TBL[0].LINE_STR = "1".repeat(maxVarCharLen + 1);
|
|
// Method 1.
|
|
assert.throws(
|
|
() => new pInClass(data),
|
|
/NJS-142:/
|
|
);
|
|
|
|
// Method2
|
|
const pInObj2 = new pInClass();
|
|
assert.throws(
|
|
() => pInObj2.TBL = data.TBL,
|
|
/NJS-142:/
|
|
);
|
|
|
|
// Method3
|
|
const bindVar2 = {
|
|
pIn: { val: data, dir: oracledb.BIND_IN, type: pInClass},
|
|
pOut: { val: pOutObj, dir: oracledb.BIND_INOUT },
|
|
};
|
|
await assert.rejects(
|
|
async () => await conn.execute(plsql, bindVar2),
|
|
/NJS-142:/
|
|
);
|
|
|
|
}); // 290.4.1
|
|
|
|
it('290.4.2 Invalid Values for different datatypes ', async () => {
|
|
const pInClass = await conn.getDbObjectClass(TYPE1);
|
|
const obj = new pInClass();
|
|
const inputs = [
|
|
{ attrName: 'LINE_NSTR', invalidMaxVal: "A".repeat(maxVarNCharLen * 2) + "A"},
|
|
{ attrName: 'LINE_FIXED_NSTR', invalidMaxVal: "A".repeat(maxVarNCharLen * 2) + "A"},
|
|
{ attrName: 'LINE_STR', invalidMaxVal: "A".repeat(maxVarCharLen) + "A"},
|
|
{ attrName: 'LINE_FIXED_CHAR', invalidMaxVal: "A".repeat(maxVarCharLen) + "A"},
|
|
{ attrName: 'RAWATTR', invalidMaxVal: Buffer.from("A".repeat(maxVarRawLen + 1))}
|
|
];
|
|
for (const { attrName, invalidMaxVal } of inputs) {
|
|
assert.throws(
|
|
() => obj[attrName] = invalidMaxVal,
|
|
/NJS-142:/
|
|
);
|
|
}
|
|
});
|
|
|
|
it('290.4.3 Invalid Values for collection ', async () => {
|
|
const pInClass = await conn.getDbObjectClass(TYPE4);
|
|
const obj = new pInClass();
|
|
|
|
// Initial append.
|
|
assert.throws(
|
|
() => obj.append("1".repeat(maxVarCharLen + 1)),
|
|
/NJS-143:/
|
|
);
|
|
|
|
// Add some data.
|
|
const data = [
|
|
"1".repeat(maxVarCharLen),
|
|
"2".repeat(maxVarCharLen),
|
|
"3".repeat(maxVarCharLen)
|
|
];
|
|
const obj2 = new pInClass(data);
|
|
|
|
// Check updating by index.
|
|
assert.throws(
|
|
() => obj2[2] = "1".repeat(maxVarCharLen + 1),
|
|
/NJS-143:/
|
|
);
|
|
|
|
// append again.
|
|
assert.throws(
|
|
() => obj.append("1".repeat(maxVarCharLen + 1)),
|
|
/NJS-143:/
|
|
);
|
|
});
|
|
|
|
it('290.4.4 Verify table of VARCHAR2 with CHAR/BYTE specifier', async () => {
|
|
// Verify table of VARCHAR2 CHAR.
|
|
const dataChar = [
|
|
"1".repeat(maxVarChar2CharLen),
|
|
"A".repeat(maxVarChar2CharLen)
|
|
];
|
|
const charset = await testsUtil.getDBCharSet(conn);
|
|
const isUTF8Charset = (charset === "AL32UTF8");
|
|
if (isUTF8Charset) {
|
|
// push multi byte characters test...
|
|
dataChar.push("Ő".repeat(maxVarChar2CharLen));
|
|
dataChar.push("𠜎".repeat(maxVarChar2CharLen));
|
|
}
|
|
|
|
let pInClass = await conn.getDbObjectClass(TYPE5);
|
|
let pOutClass = await conn.getDbObjectClass(TYPE5);
|
|
let pInObj = new pInClass(dataChar);
|
|
let pOutObj = new pOutClass();
|
|
|
|
// create Procedure.
|
|
let PROC = 'nodb_proc_test2029044';
|
|
let createProc = `
|
|
CREATE OR REPLACE PROCEDURE ${PROC}
|
|
(a IN ${TYPE5}, b IN OUT ${TYPE5}) AS
|
|
BEGIN
|
|
b := a;
|
|
END;
|
|
`;
|
|
let result = await conn.execute(createProc);
|
|
|
|
// Call procedure.
|
|
let plsql = `BEGIN ${PROC} (:pIn, :pOut); END;`;
|
|
let bindVar = {
|
|
pIn: { val: pInObj, dir: oracledb.BIND_IN },
|
|
pOut: { val: pOutObj, dir: oracledb.BIND_INOUT },
|
|
};
|
|
result = await conn.execute(plsql, bindVar);
|
|
|
|
// Verify the result.
|
|
assert.strictEqual(JSON.stringify(dataChar), JSON.stringify(result.outBinds.pOut));
|
|
|
|
// Verify the collection is iteratable.
|
|
const generatedArray = [];
|
|
for (const ele of result.outBinds.pOut) {
|
|
generatedArray.push(ele);
|
|
}
|
|
assert.deepStrictEqual(generatedArray, dataChar);
|
|
|
|
if (isUTF8Charset) {
|
|
// Check more than max characters allowed (utf-8 DB charset) but with-in max bytes.
|
|
dataChar.push("Ő".repeat(maxVarChar2CharLen + 1));
|
|
new pInClass(dataChar);
|
|
}
|
|
|
|
// Throw an error if number of characters exceed max bytes storage.
|
|
if (isUTF8Charset) {
|
|
dataChar.push("𠜎".repeat(maxVarChar2CharLen + 1));
|
|
} else {
|
|
dataChar.push("A".repeat(maxVarChar2CharLen + 1));
|
|
}
|
|
assert.throws(
|
|
() => new pInClass(dataChar),
|
|
/NJS-143:/
|
|
);
|
|
|
|
// Verify table of VARCHAR2 BYTE
|
|
const dataBytes = [
|
|
"1".repeat(maxVarChar2ByteLen),
|
|
"A".repeat(maxVarChar2ByteLen)
|
|
];
|
|
|
|
// create Procedure.
|
|
PROC = 'nodb_proc_test2029044_byte';
|
|
createProc = `
|
|
CREATE OR REPLACE PROCEDURE ${PROC}
|
|
(a IN ${TYPE6}, b IN OUT ${TYPE6}) AS
|
|
BEGIN
|
|
b := a;
|
|
END;
|
|
`;
|
|
result = await conn.execute(createProc);
|
|
pInClass = await conn.getDbObjectClass(TYPE6);
|
|
pOutClass = await conn.getDbObjectClass(TYPE6);
|
|
pInObj = new pInClass(dataBytes);
|
|
pOutObj = new pOutClass();
|
|
|
|
// Call procedure.
|
|
plsql = `BEGIN ${PROC} (:pIn, :pOut); END;`;
|
|
bindVar = {
|
|
pIn: { val: pInObj, dir: oracledb.BIND_IN },
|
|
pOut: { val: pOutObj, dir: oracledb.BIND_INOUT },
|
|
};
|
|
result = await conn.execute(plsql, bindVar);
|
|
|
|
// Verify the result.
|
|
assert.strictEqual(JSON.stringify(dataBytes), JSON.stringify(result.outBinds.pOut));
|
|
|
|
// Verify data larger than max size.
|
|
dataBytes.push("A".repeat(maxVarChar2ByteLen + 1));
|
|
assert.throws(
|
|
() => new pInClass(dataBytes),
|
|
/NJS-143:/
|
|
);
|
|
}); // 290.4.4
|
|
|
|
it('290.4.5 Multiple levels of nesting with collections', async function() {
|
|
// Multiple levels of nesting work in Thin mode only for now.
|
|
if (!oracledb.thin) this.skip();
|
|
|
|
//prepare data.
|
|
const expectedSubTBL1Entries = [
|
|
{
|
|
LINE_ID: 9999.231412342, // precision and scale of attributes inside object are ignored
|
|
LINE_STR: "1".repeat(maxVarCharLen),
|
|
LINE_FIXED_CHAR: "1".repeat(maxVarCharLen),
|
|
LINE_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_FIXED_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_ID2: -8,
|
|
RAWATTR: Buffer.from("A".repeat(maxVarRawLen))
|
|
},
|
|
{
|
|
LINE_ID: -8,
|
|
LINE_STR: "1".repeat(maxVarCharLen),
|
|
LINE_FIXED_CHAR: "1".repeat(maxVarCharLen),
|
|
LINE_NSTR: "Ő".repeat(maxVarNCharLen),
|
|
LINE_FIXED_NSTR: "Ő".repeat(maxVarNCharLen),
|
|
LINE_ID2: -1234,
|
|
RAWATTR: Buffer.from("B".repeat(maxVarRawLen))
|
|
}
|
|
];
|
|
|
|
const expectedSubTBL2Entries = [
|
|
{
|
|
LINE_ID: 900.2314142, // precision and scale of attributes inside object are ignored
|
|
LINE_STR: "2".repeat(maxVarCharLen),
|
|
LINE_FIXED_CHAR: "2".repeat(maxVarCharLen),
|
|
LINE_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_FIXED_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_ID2: -14,
|
|
RAWATTR: Buffer.from("B".repeat(maxVarRawLen))
|
|
},
|
|
{
|
|
LINE_ID: -6,
|
|
LINE_STR: "3".repeat(maxVarCharLen),
|
|
LINE_FIXED_CHAR: "3".repeat(maxVarCharLen),
|
|
LINE_NSTR: "Ő".repeat(maxVarNCharLen),
|
|
LINE_FIXED_NSTR: "Ő".repeat(maxVarNCharLen),
|
|
LINE_ID2: -134,
|
|
RAWATTR: Buffer.from("C".repeat(maxVarRawLen))
|
|
}
|
|
];
|
|
const data = [
|
|
expectedSubTBL1Entries,
|
|
expectedSubTBL2Entries
|
|
];
|
|
const pInClass = await conn.getDbObjectClass(TYPE7);
|
|
const pInObj = new pInClass(data);
|
|
|
|
const pInObjCopy = pInObj.copy();
|
|
assert.strictEqual(JSON.stringify(pInObj), JSON.stringify(pInObjCopy));
|
|
assert.strictEqual(pInObjCopy.length, pInObj.length);
|
|
|
|
// Delete the 2nd element in the copied object.
|
|
// Ensure that original object data is untouched.
|
|
pInObjCopy.deleteElement(1);
|
|
// The length doesn't change when deletions occur.
|
|
// So, we use the map size here.
|
|
assert.strictEqual(pInObjCopy.toMap().size, 1);
|
|
assert.strictEqual(pInObj.length, 2);
|
|
|
|
pInObjCopy[0][0].LINE_ID = 1000;
|
|
assert.strictEqual(pInObjCopy[0][0].LINE_ID, 1000);
|
|
assert.strictEqual(pInObj[0][0].LINE_ID, 9999.231412342);
|
|
}); // 290.4.5
|
|
|
|
});
|
|
|
|
describe('290.5 Associative Arrays fetch', function() {
|
|
let conn;
|
|
const PKG1 = 'NODB_PKG_OBJ_1_PLS_INTEGER';
|
|
const TYPE1 = 'NODB_TYP_OBJ_1_PLS_ARR_INTEGER';
|
|
|
|
before(async () => {
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
let sql =
|
|
`CREATE OR REPLACE PACKAGE ${PKG1} AUTHID DEFINER AS
|
|
TYPE ${TYPE1} IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
|
|
FUNCTION F1 RETURN ${TYPE1};
|
|
END;`;
|
|
await conn.execute(sql);
|
|
|
|
sql = `CREATE OR REPLACE PACKAGE BODY ${PKG1} AS
|
|
FUNCTION F1 RETURN ${TYPE1} IS
|
|
R ${TYPE1};
|
|
BEGIN
|
|
R(5):=55;
|
|
R(2):=22;
|
|
R(10):=120;
|
|
RETURN R;
|
|
END ;
|
|
END ${PKG1} ;`;
|
|
await conn.execute(sql);
|
|
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
if (conn) {
|
|
await testsUtil.dropType(conn, TYPE1);
|
|
const sql = `DROP package ${PKG1}`;
|
|
await conn.execute(sql);
|
|
await conn.close();
|
|
}
|
|
}); // after()
|
|
|
|
it('290.5.1 verify associative array outbinds', async () => {
|
|
// verify pls array of integers
|
|
const inDataobj = {5: 55, 2: 22, 10: 120};
|
|
const result = await conn.execute(
|
|
`BEGIN
|
|
:ret := ${PKG1}.f1;
|
|
END;`,
|
|
{
|
|
ret: {
|
|
dir: oracledb.BIND_OUT,
|
|
type: `${PKG1}.${TYPE1}`
|
|
}
|
|
});
|
|
const res = result.outBinds.ret;
|
|
const outMap = res.toMap();
|
|
assert.deepStrictEqual(JSON.stringify(Object.fromEntries(outMap)), JSON.stringify(inDataobj));
|
|
assert.equal(res.length, 3);
|
|
|
|
// Check if you are able to access the indexes, keys and values across
|
|
// associative arrays in proper order
|
|
const firstIndex = res.getFirstIndex();
|
|
assert.strictEqual(firstIndex, 2);
|
|
assert.strictEqual(res.getPrevIndex(firstIndex), undefined);
|
|
const nextIndex = res.getNextIndex(firstIndex);
|
|
assert.strictEqual(nextIndex, 5);
|
|
assert.strictEqual(res.getPrevIndex(nextIndex), firstIndex);
|
|
const lastIndex = res.getLastIndex();
|
|
assert.strictEqual(lastIndex, 10);
|
|
assert.strictEqual(res.getNextIndex(lastIndex), undefined);
|
|
|
|
// Ensure the order of the keys are not changed!
|
|
assert.strictEqual(res.getNextIndex(firstIndex), 5);
|
|
|
|
// Use an index that does not exist. The previous and the next index
|
|
// should still be returned
|
|
assert.strictEqual(res.getPrevIndex(4), 2);
|
|
assert.strictEqual(res.getPrevIndex(1), undefined);
|
|
assert.strictEqual(res.getNextIndex(7), 10);
|
|
assert.strictEqual(res.getNextIndex(11), undefined);
|
|
|
|
// Check a few other properties
|
|
assert.strictEqual(res.hasElement(2), true);
|
|
assert.strictEqual(res.hasElement(12), false);
|
|
assert.deepStrictEqual(res.getKeys(), Object.keys(inDataobj).map(Number));
|
|
assert.deepStrictEqual(res.getValues(), Object.values(inDataobj).map(Number));
|
|
res.deleteElement(2);
|
|
assert.strictEqual(res.hasElement(2), false);
|
|
});
|
|
});
|
|
|
|
(oracledb.thin ? describe : describe.skip)(`290.6 db Object tests with XML Value type`, () => {
|
|
let conn;
|
|
const TYPE1 = 'NODB_TEST_XMLTYPE';
|
|
const maxVarCharLen = 40;
|
|
|
|
before(async () => {
|
|
const sql = `CREATE TYPE ${TYPE1} FORCE AS OBJECT ( ID NUMBER,
|
|
XMLDATA sys.xmltype, NAME VARCHAR2(${maxVarCharLen}))`;
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
await testsUtil.createType(conn, TYPE1, sql);
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
if (conn) {
|
|
await testsUtil.dropType(conn, TYPE1);
|
|
await conn.close();
|
|
}
|
|
}); // after()
|
|
|
|
it('290.6.1 Verify XML value and metaData inside object', async () => {
|
|
const testXMLData =
|
|
'<Warehouse>\n ' +
|
|
'<WarehouseId>1</WarehouseId>\n ' +
|
|
'<WarehouseName>Melbourne, Australia</WarehouseName>\n ' +
|
|
'<Building>Owned</Building>\n ' +
|
|
'<Area>2020</Area>\n ' +
|
|
'<Docks>1</Docks>\n ' +
|
|
'<DockType>Rear load</DockType>\n ' +
|
|
'<WaterAccess>false</WaterAccess>\n ' +
|
|
'<RailAccess>N</RailAccess>\n ' +
|
|
'<Parking>Garage</Parking>\n ' +
|
|
'<VClearance>20</VClearance>\n' +
|
|
'</Warehouse>\n';
|
|
const numVal = 234;
|
|
const charVal = 'JOHN';
|
|
const expectedData = { "ID": numVal, "XMLDATA": testXMLData, "NAME": charVal };
|
|
const sql = `select ${TYPE1}(${expectedData.ID}, sys.xmltype('${expectedData.XMLDATA}'),
|
|
'${expectedData.NAME}') from dual`;
|
|
|
|
const result = await conn.execute(sql);
|
|
assert.strictEqual(JSON.stringify(result.rows[0][0]), JSON.stringify(expectedData));
|
|
|
|
// Validate metadata.
|
|
const xmlObjClass = result.metaData[0];
|
|
const pInObj = new xmlObjClass.dbTypeClass();
|
|
assert.strictEqual(pInObj.attributes.XMLDATA.type, oracledb.DB_TYPE_XMLTYPE);
|
|
assert.strictEqual(pInObj.attributes.ID.type, oracledb.DB_TYPE_NUMBER);
|
|
assert.strictEqual(pInObj.attributes.NAME.type, oracledb.DB_TYPE_VARCHAR);
|
|
});
|
|
});
|
|
|
|
describe('290.7 Check cursor close behaviour', function() {
|
|
let pool, sysDBAConn, sid;
|
|
const TYPE1 = 'NODB_TYP_NODB_MASTER_ID_ARR';
|
|
|
|
before(async function() {
|
|
if (!dbConfig.test.DBA_PRIVILEGE) this.skip();
|
|
|
|
const dbaConfig = {
|
|
user: dbConfig.test.DBA_user,
|
|
password: dbConfig.test.DBA_password,
|
|
connectionString: dbConfig.connectString,
|
|
privilege: oracledb.SYSDBA
|
|
};
|
|
sysDBAConn = await oracledb.getConnection(dbaConfig);
|
|
pool = await oracledb.createPool({
|
|
user: dbConfig.user,
|
|
password: dbConfig.password,
|
|
connectString: dbConfig.connectString,
|
|
poolMin: 1});
|
|
const conn = await pool.getConnection();
|
|
const sql =
|
|
`CREATE OR REPLACE TYPE ${TYPE1} IS TABLE OF NUMBER`;
|
|
await testsUtil.createType(conn, TYPE1, sql);
|
|
sid = await testsUtil.getSid(conn);
|
|
await conn.close();
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
if (sysDBAConn) {
|
|
await sysDBAConn.close();
|
|
}
|
|
if (pool) {
|
|
const conn = await pool.getConnection();
|
|
await testsUtil.dropType(conn, TYPE1);
|
|
await conn.close();
|
|
await pool.close(0);
|
|
}
|
|
}); // after()
|
|
|
|
// https://github.com/oracle/node-oracledb/issues/1694
|
|
it('290.7.1 create and delete DB object instance in a loop should not cause cursor leak ', async () => {
|
|
const iterations = 100;
|
|
const openCount = await testsUtil.getOpenCursorCount(sysDBAConn, sid);
|
|
for (let i = 0; i < iterations; i++) {
|
|
const connection = await pool.getConnection();
|
|
await connection.getDbObjectClass(TYPE1);
|
|
await connection.close();
|
|
}
|
|
const newOpenCount = await testsUtil.getOpenCursorCount(sysDBAConn, sid);
|
|
|
|
// ensure cursors are not linearly opened as iterations causing leak.
|
|
assert(newOpenCount - openCount < 5);
|
|
});
|
|
});
|
|
|
|
describe(`290.8 db Object tests with fetchType Handler `, () => {
|
|
let conn;
|
|
const TYPE1 = 'NODB_TEST_FETCH_TYPE';
|
|
const TYPE2 = 'NODB_TEST_FETCH_TYPE2';
|
|
const TYPE3 = 'NODB_TEST_FETCH_TYPE3'; // with LOB property
|
|
const maxVarCharLen = 4;
|
|
const maxVarNCharLen = 4;
|
|
const maxVarRawLen = 10;
|
|
const myDbObjectFetchTypeHandler = function(metadata) {
|
|
if (metadata.type === oracledb.DB_TYPE_NUMBER) {
|
|
return {
|
|
converter: (val) => {
|
|
return String(val);
|
|
}
|
|
};
|
|
}
|
|
if (metadata.type === oracledb.CLOB) {
|
|
return {
|
|
converter: (val) => {
|
|
if (!val) {
|
|
return val;
|
|
}
|
|
return val.getData();
|
|
}
|
|
};
|
|
}
|
|
};
|
|
|
|
before(async () => {
|
|
conn = await oracledb.getConnection(dbConfig);
|
|
|
|
const sqlStmts = [
|
|
{
|
|
type: TYPE1, sql: `CREATE TYPE ${TYPE1} FORCE AS OBJECT (LINE_ID NUMBER,
|
|
LINE_STR VARCHAR2(${maxVarCharLen}), LINE_FIXED_CHAR CHAR(${maxVarCharLen}),
|
|
LINE_NSTR NVARCHAR2(${maxVarNCharLen}), LINE_FIXED_NSTR NCHAR(${maxVarNCharLen}),
|
|
LINE_ID2 FLOAT, rawAttr RAW(${maxVarRawLen}), ENTRY DATE)`
|
|
},
|
|
{
|
|
type: TYPE2, sql: `CREATE TYPE ${TYPE2} FORCE AS OBJECT (LINE_ID NUMBER,
|
|
LINE_STR VARCHAR2(${maxVarCharLen}), LINE_FIXED_CHAR CHAR(${maxVarCharLen}),
|
|
LINE_NSTR NVARCHAR2(${maxVarNCharLen}), LINE_FIXED_NSTR NCHAR(${maxVarNCharLen}),
|
|
LINE_ID2 FLOAT, rawAttr RAW(${maxVarRawLen}), ENTRY DATE)`
|
|
},
|
|
{
|
|
type: TYPE3, sql: `CREATE TYPE ${TYPE3} FORCE AS OBJECT (LINE_ID
|
|
NUMBER, description CLOB)`
|
|
}
|
|
];
|
|
|
|
for (const { type, sql } of sqlStmts) {
|
|
await testsUtil.createType(conn, type, sql);
|
|
}
|
|
|
|
}); // before()
|
|
|
|
after(async () => {
|
|
if (conn) {
|
|
await testsUtil.dropType(conn, TYPE1);
|
|
await testsUtil.dropType(conn, TYPE2);
|
|
await testsUtil.dropType(conn, TYPE3);
|
|
await conn.close();
|
|
}
|
|
}); // after()
|
|
|
|
afterEach(() => {
|
|
oracledb.dbObjectTypeHandler = undefined;
|
|
}); // after()
|
|
|
|
it('290.8.1 Fetch Number property as string ', async () => {
|
|
//prepare data.
|
|
const date1 = new Date(1986, 8, 18);
|
|
const lineId = 9999.231412342;
|
|
const lineId2 = -8;
|
|
const expectedData = {
|
|
LINE_ID: lineId, // precision and scale of attributes inside object are ignored .
|
|
LINE_STR: "1".repeat(maxVarCharLen),
|
|
LINE_FIXED_CHAR: "1".repeat(maxVarCharLen),
|
|
LINE_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_FIXED_NSTR: "Э".repeat(maxVarNCharLen),
|
|
LINE_ID2: lineId2,
|
|
RAWATTR: Buffer.from("A".repeat(maxVarRawLen)),
|
|
ENTRY: date1
|
|
};
|
|
// global setting also applies to nested types in this TYPE1.
|
|
oracledb.dbObjectTypeHandler = myDbObjectFetchTypeHandler;
|
|
const pInClass = await conn.getDbObjectClass(TYPE1);
|
|
const pInObj = new pInClass(expectedData);
|
|
const pOutObj = new pInClass({}); //out obj inited as empty one
|
|
|
|
// create Procedure.
|
|
const PROC = 'nodb_proc_test2029041';
|
|
const createProc = `
|
|
CREATE OR REPLACE PROCEDURE ${PROC}
|
|
(a IN ${TYPE1}, b IN OUT ${TYPE1}) AS
|
|
BEGIN
|
|
b := a;
|
|
END;
|
|
`;
|
|
let result = await conn.execute(createProc);
|
|
|
|
// Call procedure.
|
|
let plsql = `BEGIN ${PROC} (:pIn, :pOut); END;`;
|
|
let bindVar = {
|
|
pIn: { val: pInObj, dir: oracledb.BIND_IN },
|
|
pOut: { val: pOutObj, dir: oracledb.BIND_INOUT },
|
|
};
|
|
result = await conn.execute(plsql, bindVar);
|
|
// convert number and float expected data as string.
|
|
expectedData.LINE_ID = String(expectedData.LINE_ID);
|
|
expectedData.LINE_ID2 = String(expectedData.LINE_ID2);
|
|
// Verify the result.
|
|
assert.strictEqual(JSON.stringify(expectedData), JSON.stringify(result.outBinds.pOut));
|
|
|
|
// dbObjectTypeHandler changes are not reflected for types already
|
|
// created.
|
|
oracledb.dbObjectTypeHandler = undefined;
|
|
assert.strictEqual(JSON.stringify(expectedData), JSON.stringify(result.outBinds.pOut));
|
|
|
|
// Reset dbObjectTypeHandler to default which is reflected for TYPE2.
|
|
oracledb.dbObjectTypeHandler = undefined;
|
|
expectedData.LINE_ID = lineId;
|
|
expectedData.LINE_ID2 = lineId2;
|
|
const pInClass2 = await conn.getDbObjectClass(TYPE2);
|
|
const pInObj2 = new pInClass2(expectedData);
|
|
const pOutObj2 = new pInClass2({}); //out obj inited as empty one
|
|
|
|
// create Procedure.
|
|
const PROC2 = 'nodb_proc_test2029042';
|
|
const createProc2 = `
|
|
CREATE OR REPLACE PROCEDURE ${PROC2}
|
|
(a IN ${TYPE2}, b IN OUT ${TYPE2}) AS
|
|
BEGIN
|
|
b := a;
|
|
END;
|
|
`;
|
|
result = await conn.execute(createProc2);
|
|
|
|
// Call procedure.
|
|
plsql = `BEGIN ${PROC2} (:pIn, :pOut); END;`;
|
|
bindVar = {
|
|
pIn: { val: pInObj2, dir: oracledb.BIND_IN },
|
|
pOut: { val: pOutObj2, dir: oracledb.BIND_INOUT },
|
|
};
|
|
result = await conn.execute(plsql, bindVar);
|
|
// Verify the result and no typehandler or converter is used.
|
|
assert.strictEqual(JSON.stringify(expectedData), JSON.stringify(result.outBinds.pOut));
|
|
|
|
if (!oracledb.thin) {
|
|
// converters for LOB are not supported.
|
|
oracledb.dbObjectTypeHandler = myDbObjectFetchTypeHandler;
|
|
await assert.rejects(
|
|
async () => await conn.getDbObjectClass(TYPE3),
|
|
/NJS-089:/
|
|
);
|
|
}
|
|
|
|
}); // 290.8.1
|
|
|
|
});
|
|
});
|