how to add length or precision validation to basic datatype attributes of dbObject.attributes? #1630
Description
- What versions are you using?
node-oracledb version 6.2.0
oracle 11.2
instantClient 19.21
- Describe the problem
when i use getObjectClass function creating dbObjects, one of it's nested objects can not be initialized ,because one of it's attribute(varchar2) should be shorter than 30 bytes but given 120 length data, AND no error was thrown.
- Include a runnable Node.js script that shows the problem.
when object was inited with invalid data then TBL table object is empty ,here is an example script to show the issue,
to run this script ; the user should have all privileges to create types and procedures.
"use strict";
const oracledb = require("oracledb");
// i use rohitbasu77/oracle11g:latest docker image on windows to run this example
const dbConfig = {
user: "fakeone", //use a real one
password: "",
connectionString: "127.0.0.1:41521/xe",
};
// 11g must connected by thick mode ,
oracledb.initOracleClient({ libDir: "D:\\drivers\\instantclient_19_21" });
//must print Running in thick mode
console.log(oracledb.thin ? "Running in thin mode" : "Running in thick mode");
async function run() {
let connection;
try {
// Get a non-pooled connection
connection = await oracledb.getConnection(dbConfig);
console.log("Connection was successful!");
// step 0
// drop first , and ignore db error cause there is not "drop if exists" in 11g
const dropStmts = [
"DROP TYPE TEST_NESTED_OBJ FORCE", // nested object
"DROP TYPE TEST_NESTED_OBJ_TBL FORCE", // table object
"DROP TYPE TEST_NESTED_OBJ_TBL_LINE FORCE", // line object of table object
"DROP TYPE DBMS_OUTPUT_ROW_TYPE FORCE",
];
for (const stmt of dropStmts) {
await connection
.execute(stmt)
.then(() => {
console.log(`[${stmt}] success!`);
})
.catch((error) => console.log([stmt, error.errNum, error.message]));
}
// step1
// create types
const createObjectStmts = [
`CREATE TYPE TEST_NESTED_OBJ_TBL_LINE FORCE AS OBJECT ( LINE_ID NUMBER,LINE_STR VARCHAR2(3))`,
`CREATE TYPE TEST_NESTED_OBJ_TBL FORCE AS TABLE OF TEST_NESTED_OBJ_TBL_LINE`,
`CREATE TYPE TEST_NESTED_OBJ FORCE AS OBJECT (HEADER_ID NUMBER,TBL TEST_NESTED_OBJ_TBL )`,
`CREATE OR REPLACE TYPE DBMS_OUTPUT_ROW_TYPE FORCE AS TABLE OF VARCHAR2(32767)`,
];
for (const stmt of createObjectStmts) {
await connection
.execute(stmt)
.then(() => {
console.log(`[${stmt}] success!`);
})
.catch((error) => {
console.log([stmt, error.errNum, error.message]);
throw new Error("stmt execution error in createObjectStmts");
});
}
// step2
// create procedure to consume data object ,use both p_out parameter and dbms_output to validate p_in parameter
const createProcedureStmt = `
CREATE OR REPLACE PROCEDURE TEST_NEST_OBJECT_PROCEDURE
(
P_IN IN TEST_NESTED_OBJ,
P_OUT IN OUT TEST_NESTED_OBJ --MUST USE IN_OUT TO PASS AN INITED OBJECT
) AS
BEGIN
--START
--PRINT HEAD
DBMS_OUTPUT.PUT_LINE('!PRINT THE P_IN OBJECT!');
DBMS_OUTPUT.PUT_LINE('P_IN.HEADER_ID : ' || P_IN.HEADER_ID);
--PRINT TABLE
DBMS_OUTPUT.PUT_LINE('!PRINT THE TABLE!');
DBMS_OUTPUT.PUT_LINE('P_IN.TBL.COUNT : ' || P_IN.TBL.COUNT);
IF P_IN.TBL.COUNT > 0 THEN
FOR i IN 1 .. P_IN.TBL.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('P_IN.TBL(i).LINE_ID : ' || P_IN.TBL(i).LINE_ID);
DBMS_OUTPUT.PUT_LINE('P_IN.TBL(i).LINE_STR : ' || P_IN.TBL(i).LINE_STR);
END LOOP;
END IF;
P_OUT := P_IN;
RETURN;
END;
`;
await connection
.execute(createProcedureStmt)
.then(() => {
console.log(`createProcedureStmt success!`);
})
.catch((error) => {
console.log("stmt execution error in createProcedureStmt");
throw error;
});
//step3
//create function return dbms_output data as table
const createfetchDbmsOutputFunctionStmt = `
CREATE OR REPLACE FUNCTION FETCH_DBMS_OUTPUT RETURN DBMS_OUTPUT_ROW_TYPE PIPELINED IS
line VARCHAR2(32767);
status INTEGER;
BEGIN LOOP
DBMS_OUTPUT.GET_LINE(line, status);
EXIT WHEN status = 1;
PIPE ROW (line);
END LOOP;
END;
`;
await connection
.execute(createfetchDbmsOutputFunctionStmt)
.then(() => {
console.log(`createfetchDbmsOutputFunctionStmt success!`);
})
.catch((error) => {
console.log(
"stmt execution error in createfetchDbmsOutputFunctionStmt"
);
throw error;
});
//step4
//call procedure and print p_out and dbms_output
console.log("# call procedure and print p_out and dbms_output");
//prepare data
const validData = {
HEADER_ID: 1,
TBL: [
{
LINE_ID: 1,
LINE_STR: "1", //SHORTER THAN 3 BYTES
},
{
LINE_ID: 2,
LINE_STR: "2", //SHORTER THAN 3 BYTES
},
],
};
const invalidData = {
HEADER_ID: 1,
TBL: [
{
LINE_ID: 1,
LINE_STR: "111111111111111111111111111111111111111111111111", //LONGER THAN 3 BYTES
},
{
LINE_ID: 2,
LINE_STR: "2", //SHORTER THAN 3 BYTES
},
],
};
async function callProcedureAndPrint(data) {
console.log("call procedure with data below:");
console.log(JSON.stringify(data));
// -- step 4.1 get class
const pInClass = await connection.getDbObjectClass("TEST_NESTED_OBJ");
const pOutClass = await connection.getDbObjectClass("TEST_NESTED_OBJ");
// --step 4.2 init objects both in and out
const pInObj = new pInClass(data);
const pOutObj = new pOutClass({}); //out obj inited as empty one
//finally we can call procedure;
const callProcedureStmt = `
BEGIN
DBMS_OUTPUT.ENABLE(NULL);
TEST_NEST_OBJECT_PROCEDURE(P_IN=>:pIn,P_OUT=>:pOut);
END;
`;
const { success, objectOut, errorMessage } = await connection
.execute(callProcedureStmt, {
pIn: { val: pInObj, dir: oracledb.BIND_IN },
pOut: { val: pOutObj, dir: oracledb.BIND_INOUT },
})
.then((result) => {
return { success: true, objectOut: result.outBinds.pOut };
})
.catch((err) => {
return { success: false, objectOut: {}, errorMessage: err.message };
});
const dbmsOutputRows = await connection
.execute("SELECT * FROM TABLE(FETCH_DBMS_OUTPUT()) ")
.then((result) => {
return result.rows;
})
.catch((err) =>
console.log("errors in fetch dbmsOutputRows \n" + err.message)
);
if (success) {
console.log("p_out data:");
console.log(JSON.stringify(objectOut));
} else {
console.log("#ERROR");
console.log("ERROR MSG:");
console.log(errorMessage);
}
console.log("dbmsOutputRows:");
console.log(dbmsOutputRows);
}
console.log("\n###########################################");
console.log("# use valid data");
await callProcedureAndPrint(validData);
console.log("\n###########################################");
console.log("# use inValid data");
await callProcedureAndPrint(invalidData);
} catch (err) {
console.error(err);
} finally {
if (connection) {
try {
await connection.close();
} catch (err) {
console.error(err);
}
}
}
}
run();you should get console log results below
