how to add length or precision validation to basic datatype attributes of dbObject.attributes?

[YMS-QC]

1. What versions are you using?

node-oracledb version 6.2.0
oracle 11.2
instantClient 19.21

3. 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.

4. 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

[cjbj]

Please:

Include a runnable Node.js script that shows the problem.

[YMS-QC]

ok ASAP

[YMS-QC]

@cjbj runnable Node.js script that shows the problem is uploaded,plz take a look .

[cjbj]

If we understand correctly what you are asking, our current thoughts are that this is an Oracle Client bug. Ideally we think you should get an ORA error when you try to use data that is too long. We'll report it to the OCI team so they can make this happen.

For Thin mode (which I know you personally can't use), we will look at seeing if we can add a similar check.

[YMS-QC]

thank you ,I am trying to use json schema to validata data before using procedure now

[sharadraju]

@YMS-QC We have added a patch here that will throw an error, when you use attributes values in dbObject that are too long.

This will be officially incorporated in the 6.4 release

[YMS-QC]

thank you very much !!!

[sharadraju]

This has been fixed as part of node-oracledb 6.4