3120 lines
108 KiB
JavaScript
3120 lines
108 KiB
JavaScript
/* Copyright (c) 2016, 2023, 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
|
|
* 70. plsqlBindScalar.js
|
|
*
|
|
* DESCRIPTION
|
|
* Testing PL/SQL bind scalars.
|
|
*
|
|
*****************************************************************************/
|
|
'use strict';
|
|
|
|
const oracledb = require('oracledb');
|
|
const assert = require('assert');
|
|
const dbConfig = require('./dbconfig.js');
|
|
const assist = require('./dataTypeAssist.js');
|
|
|
|
describe('70. plsqlBindScalar.js', function() {
|
|
|
|
let connection;
|
|
|
|
before(async function() {
|
|
connection = await oracledb.getConnection(dbConfig);
|
|
await connection.execute("alter session set time_zone='UTC'");
|
|
});
|
|
|
|
after(async function() {
|
|
await connection.close();
|
|
});
|
|
|
|
describe('70.1 PL/SQL bind scalar, dir: BIND_IN and BIND_OUT, type: STRING', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc71(strValue IN VARCHAR2) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN strValue;\n" +
|
|
"END nodb_plsqlbindfunc71;";
|
|
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc71";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "BEGIN :output := nodb_plsqlbindfunc71(:strValue); END;";
|
|
const resultBind = {type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 2000};
|
|
|
|
it('70.1.1 basic case: a simple string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
strValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: "PL/SQL Binding Scalar"}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, "PL/SQL Binding Scalar");
|
|
}); // 70.1.1
|
|
|
|
it('70.1.2 negative: bind in value and type mismatch', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
strValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: 42}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.1.2
|
|
|
|
it('70.1.3 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
strValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.1.3
|
|
|
|
it('70.1.4 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
strValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.1.4
|
|
|
|
it('70.1.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
strValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
});
|
|
|
|
it('70.1.6 tests default dir & type', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
strValue: "PL/SQL Binding Scalar"
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, "PL/SQL Binding Scalar");
|
|
}); // 70.1.6
|
|
|
|
it('70.1.7 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
strValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
});// 70.1.7
|
|
|
|
}); // 70.1
|
|
|
|
describe('70.2 dir: BIND_IN and BIND_OUT, type: NUMBER', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc72(numValue IN NUMBER) RETURN NUMBER \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN numValue;\n" +
|
|
"END nodb_plsqlbindfunc72;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc72";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "BEGIN :output := nodb_plsqlbindfunc72(:numValue); END;";
|
|
const resultBind = {type: oracledb.NUMBER, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.2.1 basic case', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: 755}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 755);
|
|
}); // 70.2.1
|
|
|
|
it('70.2.2 auto detect number type', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: 755
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 755);
|
|
}); // 70.2.2
|
|
|
|
it('70.2.3 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.2.3
|
|
|
|
it('70.2.4 Negative: bind value and type mismatch - val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.2.4
|
|
|
|
it('70.2.5 val: 0', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: 0}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 0);
|
|
}); // 70.2.5
|
|
|
|
it('70.2.6 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.2.6
|
|
|
|
it('70.2.7 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-105:/
|
|
);
|
|
}); // 70.2.7
|
|
|
|
it('70.2.8 val: -1', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
numValue: {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: -1}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, -1);
|
|
}); // 70.2.8
|
|
|
|
}); // 70.2
|
|
|
|
describe('70.3 dir: BIND_IN and BIND_OUT, type: DATE', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc73(dateValue IN DATE) RETURN DATE \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN dateValue;\n" +
|
|
"END nodb_plsqlbindfunc73;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc73";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "BEGIN :output := nodb_plsqlbindfunc73(:dateValue); END;";
|
|
const resultBind = {type: oracledb.DATE, dir: oracledb.BIND_OUT};
|
|
const dt = new Date(2016, 8, 1);
|
|
|
|
it('70.3.1 basic case', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: dt}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, dt);
|
|
}); // 70.3.1
|
|
|
|
it('70.3.2 auto detect Date type', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: dt
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, dt);
|
|
}); // 70.3.2
|
|
|
|
it('70.3.3 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.3.3
|
|
|
|
it('70.3.4 val: empty string, negative - bind value and type mismatch', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.3.4
|
|
|
|
it('70.3.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.3.5
|
|
|
|
it('70.3.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.3.6
|
|
|
|
it('70.3.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date (2016, 1, 30);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
const resultDate = new Date (2016, 2, 1);
|
|
assert.deepStrictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.3.7
|
|
|
|
it('70.3.8 val: 1969-12-31', async function() {
|
|
const date = new Date (1969, 11, 31);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.3.8
|
|
|
|
it('70.3.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date (1970, 0, 1);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.3.9
|
|
|
|
it('70.3.10 val: create Date value using numeric value: new Date(number)', async function() {
|
|
const date = new Date (1476780296673);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
|
|
// Oracle stores only the fractions up to second in a DATE field.
|
|
const dateResult = new Date (1476780296000);
|
|
assert.deepStrictEqual(result.outBinds.output, dateResult);
|
|
}); // 70.3.10
|
|
|
|
it('70.3.11 val: create Date value using numeric value: new Date(7 number)', async function() {
|
|
const date = new Date (2011, 5, 3, 4, 6, 23, 123);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
|
|
// Oracle stores only the fractions up to second in a DATE field.
|
|
const dateResult = new Date (2011, 5, 3, 4, 6, 23, 0);
|
|
assert.deepStrictEqual(result.outBinds.output, dateResult);
|
|
}); // 70.3.11
|
|
|
|
it('70.3.12 val: create Date value using numeric value: 0', async function() {
|
|
// Zero time is 01 January 1970 00:00:00 UTC
|
|
const date = new Date(0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
const dateResult = new Date (Date.UTC(1970, 0, 1));
|
|
assert.deepStrictEqual(result.outBinds.output, dateResult);
|
|
}); // 70.3.12
|
|
|
|
}); // 70.3
|
|
|
|
describe('70.4 dir: BIND_IN and BIND_OUT, type: BUFFER', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc74(bufValue IN RAW) RETURN RAW \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN bufValue;\n" +
|
|
"END nodb_plsqlbindfunc74;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc74";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "BEGIN :output := nodb_plsqlbindfunc74(:bufValue); END;";
|
|
const resultBind = {type: oracledb.BUFFER, dir: oracledb.BIND_OUT};
|
|
const bufsize = 100;
|
|
const bindValue = assist.createBuffer(bufsize);
|
|
|
|
it('70.4.1 basic case', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
bufValue: {type: oracledb.BUFFER, dir: oracledb.BIND_IN, val: bindValue}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, bindValue);
|
|
}); // 70.4.1
|
|
|
|
it('70.4.2 auto detect Buffer type', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
bufValue: bindValue
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, bindValue);
|
|
}); // 70.4.2
|
|
|
|
it('70.4.3 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
bufValue: {type: oracledb.BUFFER, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.4.3
|
|
|
|
it('70.4.4 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
bufValue: {type: oracledb.BUFFER, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.4.4
|
|
|
|
it('70.4.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
bufValue: {type: oracledb.BUFFER, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.4.5
|
|
|
|
it('70.4.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
bufValue: {type: oracledb.BUFFER, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.4.6
|
|
|
|
}); // 70.4
|
|
|
|
describe('70.5 dir: BIND_INOUT, type: STRING', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE PROCEDURE nodb_inoutproc5 (p_inout IN OUT STRING) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := p_inout; \n" +
|
|
"END nodb_inoutproc5;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP PROCEDURE nodb_inoutproc5";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "begin nodb_inoutproc5(p_inout => :p_inout); end;";
|
|
|
|
it('70.5.1 basic case: a simple string', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: "PL/SQL Binding INOUT Scalar"
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, "PL/SQL Binding INOUT Scalar");
|
|
}); // 70.5.1
|
|
|
|
it('70.5.2 tests default type', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
val: "PL/SQL Binding INOUT Scalar"
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, "PL/SQL Binding INOUT Scalar");
|
|
}); // 70.5.2
|
|
|
|
it('70.5.3 negative: bind value and type mismatch', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: 755
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.5.3
|
|
|
|
it('70.5.4 val: null', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: null
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.5.4
|
|
|
|
it('70.5.5 val: empty string', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: ''
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.5.5
|
|
|
|
it('70.5.6 val: undefined', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: undefined
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.5.6
|
|
|
|
it('70.5.7 val: NaN', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: NaN
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.5.7
|
|
|
|
it('70.5.8 NULL IN and NON-NULL out', async function() {
|
|
const proc508 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc508 (p_inout IN OUT STRING) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := 'abc'; \n" +
|
|
"END nodb_inoutproc508;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: null
|
|
}
|
|
};
|
|
const sqlrun508 = "begin nodb_inoutproc508(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc508";
|
|
await connection.execute(proc508);
|
|
const result = await connection.execute(sqlrun508, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, 'abc');
|
|
await connection.execute(sqldrop);
|
|
}); // 70.5.8
|
|
|
|
it('70.5.9 NON-NULL IN and NULL OUT', async function() {
|
|
const proc509 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc509 (p_inout IN OUT STRING) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := null; \n" +
|
|
"END nodb_inoutproc509;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: "abc"
|
|
}
|
|
};
|
|
const sqlrun509 = "begin nodb_inoutproc509(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc509";
|
|
|
|
await connection.execute(proc509);
|
|
const result = await connection.execute(sqlrun509, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.5.9
|
|
|
|
it('70.5.10 n Length IN and 2n OUT', async function() {
|
|
const proc510 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc510 (p_inout IN OUT STRING) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := concat (p_inout, p_inout); \n" +
|
|
"END nodb_inoutproc510;";
|
|
const strVar = "abcdefghijklmnopqrstuvwxyz";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: strVar
|
|
}
|
|
};
|
|
const sqlrun510 = "begin nodb_inoutproc510(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc510";
|
|
await connection.execute(proc510);
|
|
const result = await connection.execute(sqlrun510, bindVar);
|
|
const resutVar = strVar + strVar;
|
|
assert.strictEqual(result.outBinds.p_inout, resutVar);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.5.10
|
|
|
|
it('70.5.11 2n Length IN and n OUT', async function() {
|
|
const proc511 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc511 (p_inout IN OUT STRING) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := substr ( p_inout, 1, Length(p_inout)/2 ); \n" +
|
|
"END nodb_inoutproc511;";
|
|
const strVar = "Pack my bag with five dozen liquor jugs";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: strVar
|
|
}
|
|
};
|
|
const sqlrun511 = "begin nodb_inoutproc511(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc511";
|
|
await connection.execute(proc511);
|
|
const result = await connection.execute(sqlrun511, bindVar);
|
|
const resultVar = "Pack my bag with fiv";
|
|
assert.strictEqual(result.outBinds.p_inout, resultVar);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.5.11
|
|
|
|
}); // 70.5
|
|
|
|
describe('70.6 dir: BIND_INOUT, type: NUMBER', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE PROCEDURE nodb_inoutproc6 (p_inout IN OUT NUMBER) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := p_inout; \n" +
|
|
"END nodb_inoutproc6;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP PROCEDURE nodb_inoutproc6";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "begin nodb_inoutproc6(p_inout => :p_inout); end;";
|
|
|
|
it('70.6.1 basic case', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: 8396
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, 8396);
|
|
}); // 70.6.1
|
|
|
|
it('70.6.2 auto detect number type', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
val: 8396
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, 8396);
|
|
}); // 70.6.2
|
|
|
|
it('70.6.3 negative: bind value and type mismatch - val: empty string', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: ''
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.6.3
|
|
|
|
it('70.6.4 val: null', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: null
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.6.4
|
|
|
|
it('70.6.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: undefined
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.6.5
|
|
|
|
it('70.6.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: NaN
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-105:/
|
|
);
|
|
}); // 70.6.6
|
|
|
|
it('70.6.7 val: 0', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: 0
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, 0);
|
|
}); // 70.6.7
|
|
|
|
it('70.6.8 val: -1', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: -1
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, -1);
|
|
}); // 70.6.8
|
|
|
|
it('70.6.10 NULL IN and NON-NULL out', async function() {
|
|
const proc610 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc610 (p_inout IN OUT NUMBER) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := 3; \n" +
|
|
"END nodb_inoutproc610;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: null
|
|
}
|
|
};
|
|
const sqlrun610 = "begin nodb_inoutproc610(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc610";
|
|
|
|
await connection.execute(proc610);
|
|
const result = await connection.execute(sqlrun610, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, 3);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.6.10
|
|
|
|
it('70.6.11 NON-NULL IN and NULL OUT', async function() {
|
|
const proc611 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc611 (p_inout IN OUT NUMBER) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := null; \n" +
|
|
"END nodb_inoutproc611;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: 3
|
|
}
|
|
};
|
|
const sqlrun611 = "begin nodb_inoutproc611(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc611";
|
|
|
|
await connection.execute(proc611);
|
|
const result = await connection.execute(sqlrun611, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
await connection.execute(sqldrop);
|
|
});// 70.6.11
|
|
|
|
}); // 70.6
|
|
|
|
describe('70.7 dir: BIND_INOUT, type: DATE', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE PROCEDURE nodb_inoutproc7 (p_inout IN OUT DATE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := p_inout; \n" +
|
|
"END nodb_inoutproc7;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP PROCEDURE nodb_inoutproc7";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "begin nodb_inoutproc7(p_inout => :p_inout); end;";
|
|
const daterun = new Date(2016, 7, 5);
|
|
|
|
it('70.7.1 basic case', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: daterun
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, daterun);
|
|
}); // 70.7.1
|
|
|
|
it('70.7.2 auto detect Date type', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
val: daterun
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, daterun);
|
|
}); // 70.7.2
|
|
|
|
it('70.7.3 val: null', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.7.3
|
|
|
|
it('70.7.4 val: empty string, negative - bind value and type mismatch', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: ''
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.7.4
|
|
|
|
it('70.7.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: undefined
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.7.5
|
|
|
|
it('70.7.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: NaN
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.7.6
|
|
|
|
it('70.7.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date (2016, 1, 30);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
const resultDate = new Date (2016, 2, 1);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, resultDate);
|
|
}); // 70.7.7
|
|
|
|
it('70.7.8 val: 1969-12-31', async function() {
|
|
const date = new Date (1969, 11, 31);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.7.8
|
|
|
|
it('70.7.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date (1970, 0, 1);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.7.9
|
|
|
|
it('70.7.10 NULL IN and NON-NULL out', async function() {
|
|
const proc710 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc710 (p_inout IN OUT DATE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := TO_DATE('5-AUG-2016'); \n" +
|
|
"END nodb_inoutproc710;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const sqlrun710 = "begin nodb_inoutproc710(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc710";
|
|
await connection.execute(proc710);
|
|
const result = await connection.execute(sqlrun710, bindVar);
|
|
const date = new Date("2016-08-05 00:00:00");
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.7.10
|
|
|
|
it('70.7.11 NON-NULL IN and NULL OUT', async function() {
|
|
const proc711 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc711 (p_inout IN OUT DATE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := null; \n" +
|
|
"END nodb_inoutproc711;";
|
|
const date = new Date(2011, 0, 12);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const sqlrun711 = "begin nodb_inoutproc711(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc711";
|
|
await connection.execute(proc711);
|
|
const result = await connection.execute(sqlrun711, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.7.11
|
|
|
|
}); // 70.7
|
|
|
|
describe('70.8 dir: BIND_INOUT, type: BUFFER', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE PROCEDURE nodb_inoutproc8 (p_inout IN OUT RAW) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := p_inout; \n" +
|
|
"END nodb_inoutproc8;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP PROCEDURE nodb_inoutproc8";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "begin nodb_inoutproc8(p_inout => :p_inout); end;";
|
|
|
|
const bufsize = 201;
|
|
const bufValue = assist.createBuffer(bufsize);
|
|
|
|
it('70.8.1 basic case', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: bufValue,
|
|
maxSize: 32767 // max allowed value of maxSize in PL/SQL
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, bufValue);
|
|
}); // 70.8.1
|
|
|
|
it('70.8.2 auto detect BUFFER type', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
val: bufValue,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, bufValue);
|
|
}); // 70.8.2
|
|
|
|
it('70.8.3 val: null', async function() {
|
|
const emptybuf = Buffer.alloc (0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: emptybuf,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.8.3
|
|
|
|
it('70.8.4 val: empty string', async function() {
|
|
const emptybuf = Buffer.from ("", "utf-8");
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: emptybuf,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.8.4
|
|
|
|
it('70.8.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: undefined,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.8.5
|
|
|
|
it('70.8.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: NaN,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.8.6
|
|
|
|
}); // 70.8
|
|
|
|
describe('70.9 Query the binded data by SQL', function() {
|
|
|
|
before(async function() {
|
|
const proc1 = "BEGIN \n" +
|
|
" DECLARE \n" +
|
|
" e_table_missing EXCEPTION; \n" +
|
|
" PRAGMA EXCEPTION_INIT(e_table_missing, -00942); \n" +
|
|
" BEGIN \n" +
|
|
" EXECUTE IMMEDIATE('DROP TABLE nodb_plsqlbindtab PURGE'); \n" +
|
|
" EXCEPTION \n" +
|
|
" WHEN e_table_missing \n" +
|
|
" THEN NULL; \n" +
|
|
" END; \n" +
|
|
" EXECUTE IMMEDIATE (' \n" +
|
|
" CREATE TABLE nodb_plsqlbindtab ( \n" +
|
|
" id NUMBER, \n" +
|
|
" str VARCHAR2(4000), \n" +
|
|
" num NUMBER, \n" +
|
|
" dat DATE, \n" +
|
|
" buf RAW(2000) \n" +
|
|
" ) \n" +
|
|
" '); \n" +
|
|
"END; ";
|
|
|
|
const proc2 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc9 ( \n" +
|
|
" p_in IN NUMBER, p_inout1 IN OUT VARCHAR2, \n" +
|
|
" p_inout2 IN OUT NUMBER, p_inout3 IN OUT DATE, p_inout4 IN OUT RAW) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" insert into nodb_plsqlbindtab(id, str, num, dat, buf) values (p_in, p_inout1, p_inout2, p_inout3, p_inout4); \n" +
|
|
"END nodb_inoutproc9;";
|
|
|
|
const proc3 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc10 ( \n" +
|
|
" p_in IN NUMBER, p_str IN VARCHAR2, \n" +
|
|
" p_num IN NUMBER, p_dat IN DATE, p_buf IN RAW) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" insert into nodb_plsqlbindtab(id, str, num, dat, buf) values (p_in, p_str, p_num, p_dat, p_buf); \n" +
|
|
"END nodb_inoutproc10;";
|
|
|
|
await connection.execute(proc1);
|
|
await connection.execute(proc2);
|
|
await connection.execute(proc3);
|
|
});
|
|
|
|
after(async function() {
|
|
await connection.execute("DROP PROCEDURE nodb_inoutproc9");
|
|
await connection.execute("DROP PROCEDURE nodb_inoutproc10");
|
|
await connection.execute("DROP TABLE nodb_plsqlbindtab PURGE");
|
|
});
|
|
|
|
const sqlinout = "begin nodb_inoutproc9(:p_in, :p_inout1, :p_inout2, :p_inout3, :p_inout4); end;";
|
|
const sqlin = "begin nodb_inoutproc10(:p_in, :p_str, :p_num, :p_dat, :p_buf); end;";
|
|
|
|
it('70.9.1 basic case', async function() {
|
|
|
|
const rowid = 1;
|
|
const bufsize = 201;
|
|
const bufValue = assist.createBuffer(bufsize);
|
|
const daterun = new Date(2016, 7, 5);
|
|
|
|
const bindVar = {
|
|
p_in: rowid,
|
|
p_inout1: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: "PL/SQL Binding INOUT Scalar"
|
|
},
|
|
p_inout2: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: 101
|
|
},
|
|
p_inout3: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: daterun
|
|
},
|
|
p_inout4: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: bufValue,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
|
|
let result = await connection.execute(sqlinout, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout1, "PL/SQL Binding INOUT Scalar");
|
|
assert.strictEqual(result.outBinds.p_inout2, 101);
|
|
assert.deepStrictEqual(result.outBinds.p_inout3, daterun);
|
|
assert.deepStrictEqual(result.outBinds.p_inout4, bufValue);
|
|
const sql = "select * from nodb_plsqlbindtab where id = :i";
|
|
const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };
|
|
result = await connection.execute(sql, [rowid], options);
|
|
assert.strictEqual(result.rows[0].STR, "PL/SQL Binding INOUT Scalar");
|
|
assert.strictEqual(result.rows[0].NUM, 101);
|
|
assert.deepStrictEqual(result.rows[0].DAT, daterun);
|
|
assert.deepStrictEqual(result.rows[0].BUF, bufValue);
|
|
}); // 70.9.1
|
|
|
|
it('70.9.2 dir: BIND_INOUT, val: null', async function() {
|
|
|
|
const rowid = 2;
|
|
const emptybuf = Buffer.alloc(0);
|
|
|
|
const bindVar = {
|
|
p_in: rowid,
|
|
p_inout1: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: null
|
|
},
|
|
p_inout2: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: null
|
|
},
|
|
p_inout3: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
},
|
|
p_inout4: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: emptybuf,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
|
|
let result = await connection.execute(sqlinout, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout1, null);
|
|
assert.strictEqual(result.outBinds.p_inout2, null);
|
|
assert.strictEqual(result.outBinds.p_inout3, null);
|
|
assert.strictEqual(result.outBinds.p_inout4, null);
|
|
const sql = "select * from nodb_plsqlbindtab where id = :i";
|
|
const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };
|
|
result = await connection.execute(sql, [rowid], options);
|
|
assert.strictEqual(result.rows[0].STR, null);
|
|
assert.strictEqual(result.rows[0].NUM, null);
|
|
assert.strictEqual(result.rows[0].DAT, null);
|
|
assert.strictEqual(result.rows[0].BUF, null);
|
|
}); // 70.9.2
|
|
|
|
it('70.9.3 dir: BIND_IN, val: null', async function() {
|
|
|
|
const rowid = 3;
|
|
const emptybuf = Buffer.alloc(0);
|
|
|
|
const bindVar = {
|
|
p_in: rowid,
|
|
p_str: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.STRING,
|
|
val: null
|
|
},
|
|
p_num: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.NUMBER,
|
|
val: null
|
|
},
|
|
p_dat: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
},
|
|
p_buf: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.BUFFER,
|
|
val: emptybuf,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
|
|
await connection.execute(sqlin, bindVar);
|
|
const sql = "select * from nodb_plsqlbindtab where id = :i";
|
|
const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };
|
|
const result = await connection.execute(sql, [rowid], options);
|
|
assert.strictEqual(result.rows[0].STR, null);
|
|
assert.strictEqual(result.rows[0].NUM, null);
|
|
assert.strictEqual(result.rows[0].DAT, null);
|
|
assert.strictEqual(result.rows[0].BUF, null);
|
|
}); // 70.9.3
|
|
|
|
it('70.9.4 dir: BIND_INOUT, val: undefined', async function() {
|
|
const rowid = 4;
|
|
const emptybuf = Buffer.alloc(0);
|
|
|
|
const bindVar = {
|
|
p_in: rowid,
|
|
p_inout1: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.STRING,
|
|
val: undefined
|
|
},
|
|
p_inout2: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.NUMBER,
|
|
val: undefined
|
|
},
|
|
p_inout3: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: undefined
|
|
},
|
|
p_inout4: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.BUFFER,
|
|
val: emptybuf,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
|
|
let result = await connection.execute(sqlinout, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout1, null);
|
|
assert.strictEqual(result.outBinds.p_inout2, null);
|
|
assert.strictEqual(result.outBinds.p_inout3, null);
|
|
assert.strictEqual(result.outBinds.p_inout4, null);
|
|
const sql = "select * from nodb_plsqlbindtab where id = :i";
|
|
const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };
|
|
result = await connection.execute(sql, [rowid], options);
|
|
assert.strictEqual(result.rows[0].STR, null);
|
|
assert.strictEqual(result.rows[0].NUM, null);
|
|
assert.strictEqual(result.rows[0].DAT, null);
|
|
assert.strictEqual(result.rows[0].BUF, null);
|
|
}); // 70.9.4
|
|
|
|
it('70.9.5 dir: BIND_IN, val: undefined', async function() {
|
|
|
|
const rowid = 5;
|
|
const emptybuf = Buffer.alloc(0);
|
|
|
|
const bindVar = {
|
|
p_in: rowid,
|
|
p_str: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.STRING,
|
|
val: undefined
|
|
},
|
|
p_num: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.NUMBER,
|
|
val: undefined
|
|
},
|
|
p_dat: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.DATE,
|
|
val: undefined
|
|
},
|
|
p_buf: {
|
|
dir: oracledb.BIND_IN,
|
|
type: oracledb.BUFFER,
|
|
val: emptybuf,
|
|
maxSize: 32767
|
|
}
|
|
};
|
|
|
|
await connection.execute(sqlin, bindVar);
|
|
const sql = "select * from nodb_plsqlbindtab where id = :i";
|
|
const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };
|
|
const result = await connection.execute(sql, [rowid], options);
|
|
assert.strictEqual(result.rows[0].STR, null);
|
|
assert.strictEqual(result.rows[0].NUM, null);
|
|
assert.strictEqual(result.rows[0].DAT, null);
|
|
assert.strictEqual(result.rows[0].BUF, null);
|
|
}); // 70.9.5
|
|
|
|
}); // 70.9
|
|
|
|
describe('70.10 Check the bind-in values in PL/SQL', function() {
|
|
|
|
it('70.10.1 STRING, basic', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue1 (p_in IN OUT VARCHAR2) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in = 'Shenzhen City' THEN \n" +
|
|
" comparison := 'the same'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'different'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.STRING, dir: oracledb.BIND_INOUT, val: 'Shenzhen City'}
|
|
};
|
|
let sql = "begin :output := nodb_checkplsqlvalue1 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'the same');
|
|
assert.strictEqual(result.outBinds.p_in, 'Shenzhen City');
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.STRING, dir: oracledb.BIND_INOUT, val: 'Shenzhen city'}
|
|
};
|
|
sql = "begin :output := nodb_checkplsqlvalue1 (:p_in); end;";
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'different');
|
|
assert.strictEqual(result.outBinds.p_in, 'Shenzhen city');
|
|
await connection.execute("drop function nodb_checkplsqlvalue1");
|
|
}); // 70.10.1
|
|
|
|
it('70.10.2 STRING, null, empty string, undefined', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue2 (p_in IN OUT VARCHAR2) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in IS NULL THEN \n" +
|
|
" comparison := 'correct'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'wrong'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.STRING, dir: oracledb.BIND_INOUT, val: null}
|
|
};
|
|
const sql = "begin :output := nodb_checkplsqlvalue2 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.STRING, dir: oracledb.BIND_INOUT, val: ''}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.STRING, dir: oracledb.BIND_INOUT, val: undefined}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.STRING, dir: oracledb.BIND_INOUT, val: 'foobar'}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'wrong');
|
|
assert.strictEqual(result.outBinds.p_in, 'foobar');
|
|
await connection.execute("drop function nodb_checkplsqlvalue2");
|
|
}); // 70.10.2
|
|
|
|
it('70.10.3 NUMBER, null values', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue3 (p_in IN OUT NUMBER) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in IS NULL THEN \n" +
|
|
" comparison := 'correct'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'wrong'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.NUMBER, dir: oracledb.BIND_INOUT, val: null}
|
|
};
|
|
const sql = "begin :output := nodb_checkplsqlvalue3 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.NUMBER, dir: oracledb.BIND_INOUT, val: undefined}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.NUMBER, dir: oracledb.BIND_INOUT, val: 0}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'wrong');
|
|
assert.strictEqual(result.outBinds.p_in, 0);
|
|
await connection.execute("drop function nodb_checkplsqlvalue3");
|
|
}); // 70.10.3
|
|
|
|
it('70.10.4 DATE, null values', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue4 (p_in IN OUT DATE) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in IS NULL THEN \n" +
|
|
" comparison := 'correct'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'wrong'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: null}
|
|
};
|
|
const sql = "begin :output := nodb_checkplsqlvalue4 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
const today = new Date();
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: { type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: today }
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'wrong');
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: undefined}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
await connection.execute("drop function nodb_checkplsqlvalue4");
|
|
}); // 70.10.4
|
|
|
|
it('70.10.5 BUFFER', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue5 (p_in IN OUT RAW) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in IS NULL THEN \n" +
|
|
" comparison := 'correct'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'wrong'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.BUFFER, dir: oracledb.BIND_INOUT, val: null}
|
|
};
|
|
const sql = "begin :output := nodb_checkplsqlvalue5 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.BUFFER, dir: oracledb.BIND_INOUT, val: Buffer.from('')}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.BUFFER, dir: oracledb.BIND_INOUT, val: undefined}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
const bufsize = 21;
|
|
const bufValue = assist.createBuffer(bufsize);
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.BUFFER, dir: oracledb.BIND_INOUT, val: bufValue}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'wrong');
|
|
assert.deepStrictEqual(result.outBinds.p_in, bufValue);
|
|
await connection.execute("drop function nodb_checkplsqlvalue5");
|
|
}); // 70.10.5
|
|
|
|
it('70.10.6 TIMESTAMP, null values', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue6 (p_in IN OUT TIMESTAMP) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in IS NULL THEN \n" +
|
|
" comparison := 'correct'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'wrong'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: null}
|
|
};
|
|
const sql = "begin :output := nodb_checkplsqlvalue6 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
const today = new Date();
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: { type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: today }
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'wrong');
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: undefined}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
await connection.execute("drop function nodb_checkplsqlvalue6");
|
|
}); // 70.10.6
|
|
|
|
it('70.10.7 TIMESTAMP WITH TIME ZONE, null values', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue7 (p_in IN OUT TIMESTAMP WITH TIME ZONE) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in IS NULL THEN \n" +
|
|
" comparison := 'correct'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'wrong'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: null}
|
|
};
|
|
const sql = "begin :output := nodb_checkplsqlvalue7 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
const today = new Date();
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: { type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: today }
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'wrong');
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: undefined}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
await connection.execute("drop function nodb_checkplsqlvalue7");
|
|
}); // 70.10.7
|
|
|
|
it('70.10.8 TIMESTAMP WITH LOCAL TIME ZONE, null values', async function() {
|
|
const proc = "CREATE OR REPLACE FUNCTION nodb_checkplsqlvalue8 (p_in IN OUT TIMESTAMP WITH LOCAL TIME ZONE) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
" comparison VARCHAR2(20); \n" +
|
|
"BEGIN \n" +
|
|
" IF p_in IS NULL THEN \n" +
|
|
" comparison := 'correct'; \n" +
|
|
" ELSE \n" +
|
|
" comparison := 'wrong'; \n" +
|
|
" END IF; \n" +
|
|
" RETURN comparison; \n" +
|
|
"END;";
|
|
await connection.execute(proc);
|
|
let bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: null}
|
|
};
|
|
const sql = "begin :output := nodb_checkplsqlvalue8 (:p_in); end;";
|
|
let result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
const today = new Date();
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: { type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: today }
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'wrong');
|
|
bindVar = {
|
|
output: { type: oracledb.STRING, dir: oracledb.BIND_OUT },
|
|
p_in: {type: oracledb.DATE, dir: oracledb.BIND_INOUT, val: undefined}
|
|
};
|
|
result = await connection.execute(sql, bindVar);
|
|
assert.strictEqual(result.outBinds.output, 'correct');
|
|
assert.strictEqual(result.outBinds.p_in, null);
|
|
await connection.execute("drop function nodb_checkplsqlvalue8");
|
|
}); // 70.10.8
|
|
|
|
}); // 70.10
|
|
|
|
describe('70.11 dir: BIND_IN and BIND_OUT, type: TIMESTAMP(convert STRING to TIMESTAMP)', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc711(dateValue IN TIMESTAMP) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN TO_CHAR(dateValue,'YYYY-MM-DD HH24:MI:SS.FF');\n" +
|
|
"END nodb_plsqlbindfunc711;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc711";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun_str = "BEGIN :output := nodb_plsqlbindfunc711(TO_TIMESTAMP(:dateValue, 'YYYY-MM-DD HH24:MI:SS.FF')); END;";
|
|
const resultBind = {type: oracledb.STRING, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.11.1 basic case', async function() {
|
|
const date = '1999-12-01 11:00:00.001231000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, date);
|
|
}); // 70.11.1
|
|
|
|
it('70.11.2 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.11.2
|
|
|
|
it('70.11.3 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.11.3
|
|
|
|
it('70.11.4 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.11.4
|
|
|
|
it('70.11.5 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_str, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.11.5
|
|
|
|
it('70.11.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = '2016-02-30 00:00:00.000000000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_str, bindVar),
|
|
/ORA-01839:/
|
|
);
|
|
}); // 70.11.7
|
|
|
|
it('70.11.8 val: 1969-12-31', async function() {
|
|
const date = '1969-12-31 00:00:00.000000000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.11.8
|
|
|
|
it('70.11.9 val: epoch date 1970-1-1', async function() {
|
|
const date = '1970-01-01 00:00:00.000000000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.11.9
|
|
|
|
});//70.11
|
|
|
|
describe('70.12 dir: BIND_IN and BIND_OUT, type: TIMESTAMP(WITH VARCHAR2 RETURN)', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc712(dateValue IN TIMESTAMP) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN TO_CHAR(dateValue,'YYYY-MM-DD HH24:MI:SS.FF');\n" +
|
|
"END nodb_plsqlbindfunc712;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc712";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun_dt = "BEGIN :output := nodb_plsqlbindfunc712(:dateValue); END;";
|
|
const resultBind = {type: oracledb.STRING, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.12.1 basic case', async function() {
|
|
const date = new Date("2016-09-10 14:10:10.123");
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const expectDate = "2016-09-10 14:10:10.123000000";
|
|
assert.deepStrictEqual(result.outBinds.output, expectDate);
|
|
}); // 70.12.1
|
|
|
|
it('70.12.2 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.12.2
|
|
|
|
it('70.12.3 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_dt, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.12.3
|
|
|
|
it('70.12.4 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.12.4
|
|
|
|
it('70.12.5 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_dt, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.12.5
|
|
|
|
it('70.12.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date(2016, 1, 30, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "2016-03-01 00:00:00.000000000";
|
|
assert.deepStrictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.12.7
|
|
|
|
it('70.12.8 val: 1969-12-31', async function() {
|
|
const date = new Date(1969, 11, 31, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1969-12-31 00:00:00.000000000";
|
|
assert.deepStrictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.12.8
|
|
|
|
it('70.12.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date(1970, 0, 1, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1970-01-01 00:00:00.000000000";
|
|
assert.deepStrictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.12.9
|
|
|
|
it('70.12.10 val: create Date value using numeric value: new Date(number)', async function() {
|
|
const date = new Date(1476780296673); //Integer value representing the number of milliseconds since 1 January 1970 00:00:00 UTC
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {
|
|
type: oracledb.DB_TYPE_TIMESTAMP_TZ,
|
|
dir: oracledb.BIND_IN,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "2016-10-18 08:44:56.673000000";
|
|
assert.deepStrictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.12.10
|
|
|
|
it('70.12.11 val: create Date value using numeric value: 0', async function() {
|
|
//Zero time is 01 January 1970 00:00:00 UTC
|
|
const date = new Date(0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {
|
|
type: oracledb.DB_TYPE_TIMESTAMP_TZ,
|
|
dir: oracledb.BIND_IN,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1970-01-01 00:00:00.000000000";
|
|
assert.deepStrictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.12.11
|
|
|
|
});//70.12
|
|
|
|
describe('70.13 dir: BIND_IN and BIND_OUT, type: TIMESTAMP', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc713(dateValue IN TIMESTAMP) RETURN TIMESTAMP \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN dateValue;\n" +
|
|
"END nodb_plsqlbindfunc713;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc713";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "BEGIN :output := nodb_plsqlbindfunc713(:dateValue); END;";
|
|
const resultBind = {type: oracledb.DATE, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.13.1 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.13.1
|
|
|
|
it('70.13.2 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.13.2
|
|
|
|
it('70.13.3 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.13.3
|
|
|
|
});//70.13
|
|
|
|
describe('70.14 dir: BIND_INOUT, type:TIMESTAMP', function() {
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE PROCEDURE nodb_inoutproc714 (p_inout IN OUT TIMESTAMP) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := p_inout; \n" +
|
|
"END nodb_inoutproc714;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP PROCEDURE nodb_inoutproc714";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "begin nodb_inoutproc714(p_inout => :p_inout); end;";
|
|
|
|
it('70.14.1 basic case', async function() {
|
|
const date = new Date(2016, 7, 5, 12, 13, 14, 123);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
});//70.14.1
|
|
|
|
it('70.14.2 auto detect data type', async function() {
|
|
const date = new Date(2016, 7, 5, 12, 13, 14, 123);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.14.2
|
|
|
|
it('70.14.3 val: null', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.14.3
|
|
|
|
it('70.14.4 val: empty string', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: ''
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.14.4
|
|
|
|
it('70.14.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: undefined
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.14.5
|
|
|
|
it('70.14.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: NaN
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.14.6
|
|
|
|
it('70.14.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date (2016, 1, 30, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
const resultDate = new Date (2016, 2, 1, 0, 0, 0, 0);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, resultDate);
|
|
}); // 70.14.7
|
|
|
|
it('70.14.8 val: 1969-12-31', async function() {
|
|
const date = new Date (1969, 11, 31, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.14.8
|
|
|
|
it('70.14.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date (1970, 0, 1, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.14.9
|
|
|
|
it('70.14.10 NULL IN and NON-NULL out', async function() {
|
|
const proc71410 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc71410 (p_inout IN OUT TIMESTAMP) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := TO_TIMESTAMP('1999-12-01 11:00:00.001231000', 'YYYY-MM-DD HH24:MI:SS.FF'); \n" +
|
|
"END nodb_inoutproc71410;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const sqlrun71410 = "begin nodb_inoutproc71410(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc71410";
|
|
await connection.execute(proc71410);
|
|
const result = await connection.execute(sqlrun71410, bindVar);
|
|
assert(result.outBinds.p_inout !== null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.14.10
|
|
|
|
it('70.14.11 NON-NULL IN and NULL OUT', async function() {
|
|
const proc71411 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc71411 (p_inout IN OUT TIMESTAMP) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := null; \n" +
|
|
"END nodb_inoutproc71411;";
|
|
const date = new Date(2011, 0, 12, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const sqlrun71411 = "begin nodb_inoutproc71411(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc71411";
|
|
await connection.execute(proc71411);
|
|
const result = await connection.execute(sqlrun71411, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.14.11
|
|
|
|
});//70.14
|
|
|
|
describe('70.15 dir: BIND_IN and BIND_OUT, type: TIMESTAMP WITH TIME ZONE(convert STRING to TIMESTAMP)', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc715(dateValue IN TIMESTAMP WITH TIME ZONE) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN TO_CHAR(dateValue, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');\n" +
|
|
"END nodb_plsqlbindfunc715;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc715";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun_str = "BEGIN :output := nodb_plsqlbindfunc715(TO_TIMESTAMP_TZ(:dateValue, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')); END;";
|
|
const resultBind = {type: oracledb.STRING, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.15.1 basic case', async function() {
|
|
const date = '1999-12-01 11:00:00.123450000 -08:00';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.15.1
|
|
|
|
it('70.15.2 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.15.2
|
|
|
|
it('70.15.3 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.15.3
|
|
|
|
it('70.15.4 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.15.4
|
|
|
|
it('70.15.5 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_str, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.15.5
|
|
|
|
it('70.15.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = '2016-02-30 00:00:00.000000000 -08:00';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_str, bindVar),
|
|
/ORA-01839:/
|
|
);
|
|
}); // 70.15.7
|
|
|
|
it('70.15.8 val: 1969-12-31', async function() {
|
|
const date = '1969-12-31 00:00:00.000000000 -08:00';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.15.8
|
|
|
|
it('70.15.9 val: epoch date 1970-1-1', async function() {
|
|
const date = '1970-01-01 00:00:00.000000000 -08:00';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.15.9
|
|
|
|
});//70.15
|
|
|
|
describe('70.16 dir: BIND_IN and BIND_OUT, type: TIMESTAMP WITH TIME ZONE(WITH VARCHAR2 RETURN)', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc716(dateValue IN TIMESTAMP WITH TIME ZONE) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN TO_CHAR(dateValue,'YYYY-MM-DD HH24:MI:SS.FF');\n" +
|
|
"END nodb_plsqlbindfunc716;";
|
|
await connection.execute(proc);
|
|
}); // before
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc716";
|
|
await connection.execute(sql);
|
|
}); // after
|
|
|
|
const sqlrun_dt = "BEGIN :output := nodb_plsqlbindfunc716(:dateValue); END;";
|
|
const resultBind = {type: oracledb.STRING, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.16.1 basic case', async function() {
|
|
const date = new Date("2016-09-10 14:10:10.123");
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const expectDate = "2016-09-10 14:10:10.123000000";
|
|
assert.deepStrictEqual(result.outBinds.output, expectDate);
|
|
}); // 70.16.1
|
|
|
|
it('70.16.2 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.16.2
|
|
|
|
it('70.16.3 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_dt, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.16.3
|
|
|
|
it('70.16.4 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.16.4
|
|
|
|
it('70.16.5 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_dt, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.16.5
|
|
|
|
it('70.16.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date(2016, 1, 30, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "2016-03-01 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.16.7
|
|
|
|
it('70.16.8 val: 1969-12-31', async function() {
|
|
const date = new Date(1969, 11, 31, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1969-12-31 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.16.8
|
|
|
|
it('70.16.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date(1970, 0, 1, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1970-01-01 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.16.9
|
|
|
|
it('70.16.10 val: create Date value using numeric value: new Date(number)', async function() {
|
|
const date = new Date(1476780296673);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {
|
|
type: oracledb.DB_TYPE_TIMESTAMP_TZ,
|
|
dir: oracledb.BIND_IN,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "2016-10-18 08:44:56.673000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.16.10
|
|
|
|
it('70.16.11 val: create Date value using numeric value: 0', async function() {
|
|
//Zero time is 01 January 1970 00:00:00 UTC
|
|
const date = new Date(0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {
|
|
type: oracledb.DB_TYPE_TIMESTAMP_TZ,
|
|
dir: oracledb.BIND_IN,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1970-01-01 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.16.11
|
|
|
|
});//70.16
|
|
|
|
describe('70.17 dir: BIND_IN and BIND_OUT, type: TIMESTAMP WITH TIME ZONE', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc717(dateValue IN TIMESTAMP WITH TIME ZONE) RETURN TIMESTAMP WITH TIME ZONE \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN dateValue;\n" +
|
|
"END nodb_plsqlbindfunc717;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc717";
|
|
await connection.execute(sql);
|
|
}); // after
|
|
|
|
const sqlrun = "BEGIN :output := nodb_plsqlbindfunc717(:dateValue); END;";
|
|
const resultBind = {type: oracledb.DATE, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.17.1 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.17.1
|
|
|
|
it('70.17.2 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.17.2
|
|
|
|
it('70.17.3 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.17.3
|
|
|
|
});//70.17
|
|
|
|
describe('70.18 dir: BIND_INOUT, type:TIMESTAMP WITH TIME ZONE', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE PROCEDURE nodb_inoutproc718 (p_inout IN OUT TIMESTAMP WITH TIME ZONE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := p_inout; \n" +
|
|
"END nodb_inoutproc718;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP PROCEDURE nodb_inoutproc718";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "begin nodb_inoutproc718(p_inout => :p_inout); end;";
|
|
|
|
it('70.18.1 basic case', async function() {
|
|
const date = new Date(2016, 7, 5, 12, 13, 14, 123);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
});//70.18.1
|
|
|
|
it('70.18.2 auto detect data type', async function() {
|
|
const date = new Date(2016, 7, 5, 12, 13, 14, 123);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.18.2
|
|
|
|
it('70.18.3 val: null', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.18.3
|
|
|
|
it('70.18.4 val: empty string', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: ''
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.18.4
|
|
|
|
it('70.18.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: undefined
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.18.5
|
|
|
|
it('70.18.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: NaN
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.18.6
|
|
|
|
it('70.18.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date (2016, 1, 30, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
const resultDate = new Date (2016, 2, 1, 0, 0, 0, 0);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, resultDate);
|
|
}); // 70.18.7
|
|
|
|
it('70.18.8 val: 1969-12-31', async function() {
|
|
const date = new Date (1969, 11, 31, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.18.8
|
|
|
|
it('70.18.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date (1970, 0, 1, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.18.9
|
|
|
|
it('70.18.10 NULL IN and NON-NULL out', async function() {
|
|
const proc71810 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc71810 (p_inout IN OUT TIMESTAMP WITH TIME ZONE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := TO_TIMESTAMP_TZ('1999-12-01 11:00:00.001231000', 'YYYY-MM-DD HH24:MI:SS.FF'); \n" +
|
|
"END nodb_inoutproc71810;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const sqlrun71810 = "begin nodb_inoutproc71810(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc71810";
|
|
await connection.execute(proc71810);
|
|
const result = await connection.execute(sqlrun71810, bindVar);
|
|
assert(result.outBinds.p_inout !== null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.18.10
|
|
|
|
it('70.18.11 NON-NULL IN and NULL OUT', async function() {
|
|
const proc71811 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc71811 (p_inout IN OUT TIMESTAMP WITH TIME ZONE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := null; \n" +
|
|
"END nodb_inoutproc71811;";
|
|
const date = new Date(2011, 0, 12, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const sqlrun71811 = "begin nodb_inoutproc71811(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc71811";
|
|
await connection.execute(proc71811);
|
|
const result = await connection.execute(sqlrun71811, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.18.11
|
|
|
|
});//70.18
|
|
|
|
describe('70.19 dir: BIND_IN and BIND_OUT, type: TIMESTAMP WITH LOCAL TIME ZONE(convert STRING to TIMESTAMP)', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc719(dateValue IN TIMESTAMP WITH LOCAL TIME ZONE) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN TO_CHAR(dateValue, 'YYYY-MM-DD HH24:MI:SS.FF');\n" +
|
|
"END nodb_plsqlbindfunc719;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc719";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun_str = "BEGIN :output := nodb_plsqlbindfunc719(TO_TIMESTAMP_TZ(:dateValue, 'YYYY-MM-DD HH24:MI:SS.FF')); END;";
|
|
const resultBind = {type: oracledb.STRING, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.19.1 basic case', async function() {
|
|
const date = '1999-12-01 11:00:00.123450000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.output, date);
|
|
}); // 70.19.1
|
|
|
|
it('70.19.2 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.19.2
|
|
|
|
it('70.19.3 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.19.3
|
|
|
|
it('70.19.4 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.19.4
|
|
|
|
it('70.19.5 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_str, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.19.5
|
|
|
|
it('70.19.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = '2016-02-30 00:00:00.000000000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_str, bindVar),
|
|
/ORA-01839:/
|
|
);
|
|
}); // 70.19.7
|
|
|
|
it('70.19.8 val: 1969-12-31', async function() {
|
|
const date = '1969-12-31 00:00:00.000000000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
const resultDate = result.outBinds.output.toLowerCase();
|
|
assert.deepStrictEqual(resultDate, date);
|
|
}); // 70.19.8
|
|
|
|
it('70.19.9 val: epoch date 1970-1-1', async function() {
|
|
const date = '1970-01-01 00:00:00.000000000';
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.STRING, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_str, bindVar);
|
|
const resultDate = result.outBinds.output.toLowerCase();
|
|
assert.deepStrictEqual(resultDate, date);
|
|
}); // 70.19.9
|
|
|
|
});//70.19
|
|
|
|
describe('70.20 dir: BIND_IN and BIND_OUT, type: TIMESTAMP WITH LOCAL TIME ZONE(WITH VARCHAR2 RETURN)', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc720(dateValue IN TIMESTAMP WITH LOCAL TIME ZONE) RETURN VARCHAR2 \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN TO_CHAR(dateValue,'YYYY-MM-DD HH24:MI:SS.FF');\n" +
|
|
"END nodb_plsqlbindfunc720;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc720";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun_dt = "BEGIN :output := nodb_plsqlbindfunc720(:dateValue); END;";
|
|
const resultBind = {type: oracledb.STRING, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.20.1 basic case', async function() {
|
|
const date = new Date("2016-09-10 14:10:10.123");
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const expectDate = "2016-09-10 14:10:10.123000000";
|
|
assert.strictEqual(result.outBinds.output, expectDate);
|
|
}); // 70.20.1
|
|
|
|
it('70.20.2 val: null', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: null}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.20.2
|
|
|
|
it('70.20.3 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_dt, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.20.3
|
|
|
|
it('70.20.4 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.20.4
|
|
|
|
it('70.20.5 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun_dt, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.20.5
|
|
|
|
it('70.20.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date(2016, 1, 30, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "2016-03-01 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.20.7
|
|
|
|
it('70.20.8 val: 1969-12-31', async function() {
|
|
const date = new Date(1969, 11, 31, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1969-12-31 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.20.8
|
|
|
|
it('70.20.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date(1970, 0, 1, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: date}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1970-01-01 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.20.9
|
|
|
|
it('70.20.10 val: create Date value using numeric value: new Date(number)', async function() {
|
|
const date = new Date(1476780296673);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {
|
|
type: oracledb.DB_TYPE_TIMESTAMP_TZ,
|
|
dir: oracledb.BIND_IN,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "2016-10-18 08:44:56.673000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.20.10
|
|
|
|
it('70.20.11 val: create Date value using numeric value: 0', async function() {
|
|
//Zero time is 01 January 1970 00:00:00 UTC
|
|
const date = new Date(0);
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {
|
|
type: oracledb.DB_TYPE_TIMESTAMP_TZ,
|
|
dir: oracledb.BIND_IN,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun_dt, bindVar);
|
|
const resultDate = "1970-01-01 00:00:00.000000000";
|
|
assert.strictEqual(result.outBinds.output, resultDate);
|
|
}); // 70.20.11
|
|
|
|
});//70.20
|
|
|
|
describe('70.21 dir: BIND_IN and BIND_OUT, type: TIMESTAMP WITH LOCAL TIME ZONE', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE \n" +
|
|
"FUNCTION nodb_plsqlbindfunc721(dateValue IN TIMESTAMP WITH LOCAL TIME ZONE) RETURN TIMESTAMP WITH LOCAL TIME ZONE \n" +
|
|
"IS \n" +
|
|
"BEGIN \n" +
|
|
" RETURN dateValue;\n" +
|
|
"END nodb_plsqlbindfunc721;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP FUNCTION nodb_plsqlbindfunc721";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "BEGIN :output := nodb_plsqlbindfunc721(:dateValue); END;";
|
|
const resultBind = {type: oracledb.DATE, dir: oracledb.BIND_OUT};
|
|
|
|
it('70.21.1 val: empty string', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: ''}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.21.1
|
|
|
|
it('70.21.2 val: undefined', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: undefined}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.output, null);
|
|
}); // 70.21.2
|
|
|
|
it('70.21.3 val: NaN', async function() {
|
|
const bindVar = {
|
|
output: resultBind,
|
|
dateValue: {type: oracledb.DATE, dir: oracledb.BIND_IN, val: NaN}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.21.3
|
|
|
|
});//70.21
|
|
|
|
describe('70.22 dir: BIND_INOUT, type:TIMESTAMP WITH LOCAL TIME ZONE', function() {
|
|
|
|
before(async function() {
|
|
const proc = "CREATE OR REPLACE PROCEDURE nodb_inoutproc722 (p_inout IN OUT TIMESTAMP WITH LOCAL TIME ZONE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := p_inout; \n" +
|
|
"END nodb_inoutproc722;";
|
|
await connection.execute(proc);
|
|
});
|
|
|
|
after(async function() {
|
|
const sql = "DROP PROCEDURE nodb_inoutproc722";
|
|
await connection.execute(sql);
|
|
});
|
|
|
|
const sqlrun = "begin nodb_inoutproc722(p_inout => :p_inout); end;";
|
|
|
|
it('70.22.1 basic case', async function() {
|
|
const date = new Date(2016, 7, 5, 12, 13, 14, 123);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
});//70.22.1
|
|
|
|
it('70.22.2 auto detect data type', async function() {
|
|
const date = new Date(2016, 7, 5, 12, 13, 14, 123);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.22.2
|
|
|
|
it('70.22.3 val: null', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.22.3
|
|
|
|
it('70.22.4 val: empty string', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: ''
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.22.4
|
|
|
|
it('70.22.5 val: undefined', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: undefined
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
}); // 70.22.5
|
|
|
|
it('70.22.6 val: NaN', async function() {
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: NaN
|
|
}
|
|
};
|
|
await assert.rejects(
|
|
async () => await connection.execute(sqlrun, bindVar),
|
|
/NJS-011:/
|
|
);
|
|
}); // 70.22.6
|
|
|
|
it('70.22.7 val: invalid Date Value: Feb 30, 2016', async function() {
|
|
const date = new Date (2016, 1, 30, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
const resultDate = new Date (2016, 2, 1, 0, 0, 0, 0);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, resultDate);
|
|
}); // 70.22.7
|
|
|
|
it('70.22.8 val: 1969-12-31', async function() {
|
|
const date = new Date (1969, 11, 31, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.22.8
|
|
|
|
it('70.22.9 val: epoch date 1970-1-1', async function() {
|
|
const date = new Date (1970, 0, 1, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const result = await connection.execute(sqlrun, bindVar);
|
|
assert.deepStrictEqual(result.outBinds.p_inout, date);
|
|
}); // 70.22.9
|
|
|
|
it('70.22.10 NULL IN and NON-NULL OUT', async function() {
|
|
const proc72210 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc72210 (p_inout IN OUT TIMESTAMP WITH LOCAL TIME ZONE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := TO_TIMESTAMP_TZ('1999-12-01 11:00:00.001231000', 'YYYY-MM-DD HH24:MI:SS.FF'); \n" +
|
|
"END nodb_inoutproc72210;";
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: null
|
|
}
|
|
};
|
|
const sqlrun72210 = "begin nodb_inoutproc72210(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc72210";
|
|
await connection.execute(proc72210);
|
|
const result = await connection.execute(sqlrun72210, bindVar);
|
|
assert(result.outBinds.p_inout !== null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.22.10
|
|
|
|
it('70.22.11 NON-NULL IN and NULL OUT', async function() {
|
|
const proc72211 = "CREATE OR REPLACE PROCEDURE nodb_inoutproc72211 (p_inout IN OUT TIMESTAMP WITH LOCAL TIME ZONE) \n" +
|
|
"AS \n" +
|
|
"BEGIN \n" +
|
|
" p_inout := null; \n" +
|
|
"END nodb_inoutproc72211;";
|
|
const date = new Date(2011, 0, 12, 0, 0, 0, 0);
|
|
const bindVar = {
|
|
p_inout: {
|
|
dir: oracledb.BIND_INOUT,
|
|
type: oracledb.DATE,
|
|
val: date
|
|
}
|
|
};
|
|
const sqlrun72211 = "begin nodb_inoutproc72211(p_inout => :p_inout); end;";
|
|
const sqldrop = "DROP PROCEDURE nodb_inoutproc72211";
|
|
await connection.execute(proc72211);
|
|
const result = await connection.execute(sqlrun72211, bindVar);
|
|
assert.strictEqual(result.outBinds.p_inout, null);
|
|
await connection.execute(sqldrop);
|
|
}); // 70.22.11
|
|
|
|
});//70.22
|
|
|
|
});
|