CODEWITHSUNDEEP
node.jsreactjsoracle-database
Dan
Dan

Reputation: 623

BindOut ora-06502: pl/sql: numeric or value error: character string buffer too small in Node.js i

When I run this SP in Oracle Dev,

the error output is 

TABLE_1(:p_txn_seq, :v_rec_seq); ORA-02091: transaction rolled back
ORA-02291: integrity constraint (TABLE_1.COLUMN1_FK1) violated - parent key not found

But in node js, there is a different error appearing from the bind out

ora-06502: pl/sql: numeric or value error: character string buffer too small

Here is the code in node.js

 const sql = `BEGIN
    SP_NAME(
      :INPUT_TXN,
      :INPUT_SEQ_CNT,
      :IGNORE,
      :ROW_ERROR,
      :STATUS);
    END;`;
    const bindVars = {
      INPUT_TXN: transactionSeq, 
      INPUT_SEQ_CNT: count, 
      IGNORE: '', 
      ROW_ERROR: { dir: Database.BIND_OUT, type: Database.STRING, maxSize: 200 }, 
      STATUS: { dir: Database.BIND_OUT, type: Database.STRING, maxSize: 2000 }, 
    }

    let result;
    try {
      result = await this._database.simpleExecute(sql, bindVars);
    } catch (err) {
      return this.throwTxnError(417, "error. txnSeq: " + transactionSeq, err,
        { transactionSeq, sql, bindVars });
    }

The status bind variable contains the STATUS: { dir: Database.BIND_OUT, type: Database.STRING, maxSize: 2000 }

Thank you for your help

Upvotes: 1

Views: 820

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10556

Check the size of the strings being returned and increase maxSize as needed.

This also gives ORA-06502: PL/SQL: numeric or value error: character string buffer too small but if you increase the first maxSize value to 3 then it works OK.

await connection.execute(
  `create or replace procedure SP_NAME(
     INPUT_TXN      IN  NUMBER,
     INPUT_SEQ_CNT  IN  NUMBER,
     IGNORE         IN  VARCHAR2,
     ROW_ERROR      OUT VARCHAR2,
     STATUS         OUT VARCHAR2) as
   begin
     row_error := 'abc';
     status := 'def';
   end;`);

const sql = `BEGIN
SP_NAME(
  :INPUT_TXN,
  :INPUT_SEQ_CNT,
  :IGNORE,
  :ROW_ERROR,
  :STATUS);
END;`;

let transactionSeq = 123;
let count = 456;

const bindVars = {
  INPUT_TXN: transactionSeq,
  INPUT_SEQ_CNT: count,
  IGNORE: '',
  ROW_ERROR: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 2 },
  STATUS: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 20 },
};

result = await connection.execute(sql, bindVars);

Upvotes: 1

Related Questions