Node-oracledb: ORA-12899: value too large for column

Created on 14 Aug 2019  路  8Comments  路  Source: oracle/node-oracledb

Oracle version : Oracle 12c
Node version : 10.x
OS : linux
code : 

sql = `INSERT INTO \"TEST\" VALUES (:RCV_DT,:RCV_YMD,:CENTER_CD, :ID,:NAME,:RACK_TEMP,:RACK_HUM,:ORG_YMD)`;
bind = {
  RCV_DT: 2019-08-14T12:00:17.187Z,
  RCV_YMD: '20190814120017',
  CENTER_CD: 'U',
  ID: 'U3-L01-08',
  NAME: 'msTemperatureMeasurement',
  ORG_YMD: '20190814120017',
  RACK_TEMP: 26,
  RACK_HUM: 0 }
bindDef = {
 RCV_DT : { type:oracledb.DB_TYPE_DATE},
 RCV_YMD : { type:oracledb.DB_TYPE_CHAR,maxSize:14},
 CENTER_CD : { type:oracledb.DB_TYPE_CHAR,maxSize:1},
 ID : { type:oracledb.DB_TYPE_VARCHAR,maxSize:13},
 NAME : { type:oracledb.DB_TYPE_VARCHAR,maxSize:50},
 ORG_YMD : { type:oracledb.DB_TYPE_CHAR,maxSize:14},
 RACK_TEMP : { type:oracledb.DB_TYPE_NUMBER},
 RACK_HUM : { type:oracledb.DB_TYPE_NUMBER}
}
await ora.execute(sql, binds, options, function (err, result) {
   if (err){
       console.log(err)
   } else {
       console.log(result)
  }
});

error : 2019-08-14 12:00:17 | { Error: ORA-12899: value too large for column "SYSTEM"."TEST"."CENTER_CD" (actual: 9, maximum: 1) errorNum: 12899, offset: 73 }

TEST table schema
RCV_DT | DATE
RCV_YMD | CHAR(14)
ORG_YMD | CHAR(14)
CENTER_CD | CHAR(1)
ID | VARCHAR2(13)
NAME | VARCHAR2(50)
RACK_TEMP | NUMBER(8,3)
RACK_HUM | NUMBER(8,3)

I can't figure out. I don't know what is the problem. please help me.!!

question
Source
picture neophylon

Most helpful comment

Try this:

'use strict';

const oracledb = require('oracledb');
const config = require('./dbconfig.js');

let options, result;

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(config);

    try {
      await connection.execute(`DROP TABLE TEST`);
    } catch(e) {
      console.log(e);
    }

    await connection.execute(`CREATE TABLE TEST (
             RCV_DT DATE DEFAULT SYSDATE,
             RCV_YMD CHAR(14 BYTE),
             ORG_YMD CHAR(14 BYTE),
             CENTER_CD CHAR(1 BYTE),
             ID VARCHAR2(13 BYTE),
             NAME VARCHAR2(50 BYTE),
             RACK_TEMP NUMBER(8,3),
             RACK_HUM NUMBER(8,3)
           )`);

    const sql = `INSERT INTO TEST VALUES (
                   :RCV_DT,
                   :RCV_YMD,
                   :ORG_YMD,
                   :CENTER_CD,
                   :ID,
                   :NAME,
                   :RACK_TEMP,
                   :RACK_HUM
                )`;

    const binds = [
      {
        RCV_DT: new Date(),
        RCV_YMD: '20190814120017',
        CENTER_CD: 'U',
        ORG_YMD: '20190814120017',
        ID: 'U3-L01-08',
        NAME: 'msTemperatureMeasurement',
        RACK_TEMP: 26,
        RACK_HUM: 0
      }
    ];

    const bindDef = {
      RCV_DT: { type: oracledb.DATE },
      RCV_YMD: { type: oracledb.STRING, maxSize: 14 },
      ORG_YMD: { type: oracledb.STRING, maxSize: 14 },
      CENTER_CD: { type: oracledb.STRING, maxSize: 1 },
      ID: { type: oracledb.STRING, maxSize: 13 },
      NAME: { type: oracledb.STRING, maxSize: 50 },
      RACK_TEMP: { type: oracledb.NUMBER },
      RACK_HUM: { type: oracledb.NUMBER }
    };

    const options = {
      autoCommit: true,
      bindDefs:bindDef
    };

    let result = await connection.executeMany(sql, binds, options);
    console.log(result.rowsAffected);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();
picture cjbj on 14 Aug 2019
🚀1 ❤1 🎉1 😄1 👍1

All 8 comments

Make it easy for us by giving the actual CREATE TABLE statement.

Do you have a reason to give the maxSize for IN binds? From the doc "For IN binds, maxSize is ignored."

cjbj picture cjbj on 14 Aug 2019

I remove the maxSize option. then retry insert. I have still the same problem
bindDef = {
RCV_DT : { type:oracledb.DB_TYPE_DATE},
RCV_YMD : { type:oracledb.DB_TYPE_CHAR},
CENTER_CD : { type:oracledb.DB_TYPE_CHAR},
ID : { type:oracledb.DB_TYPE_VARCHAR},
NAME : { type:oracledb.DB_TYPE_VARCHAR},
ORG_YMD : { type:oracledb.DB_TYPE_CHAR},
RACK_TEMP : { type:oracledb.DB_TYPE_NUMBER},
RACK_HUM : { type:oracledb.DB_TYPE_NUMBER}
}
I got this error :
{ Error: ORA-12899: value too large for column "SYSTEM"."TEST"."CENTER_CD" (actual: 9, maximum: 1) errorNum: 12899, offset: 73 }

Create the statement

CREATE TABLE "SYSTEM"."TEST" 
   (    "RCV_DT" DATE DEFAULT SYSDATE, 
    "RCV_YMD" CHAR(14 BYTE), 
    "ORG_YMD" CHAR(14 BYTE), 
    "CENTER_CD" CHAR(1 BYTE), 
    "ID" VARCHAR2(13 BYTE), 
    "NAME" VARCHAR2(50 BYTE), 
    "RACK_TEMP" NUMBER(8,3), 
    "RACK_HUM" NUMBER(8,3)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
neophylon picture neophylon on 14 Aug 2019

Your code isn't using bindDef. Are you confusing execute() usage with executeMany(). Check the doc: https://oracle.github.io/node-oracledb/doc/api.html

cjbj picture cjbj on 14 Aug 2019

This is the entire code. I have used bindDef

let sql = `INSERT INTO \"DCMS.TEST\" VALUES (:RCV_DT,:RCV_YMD,:CENTER_CD, :ID,:NAME,:RACK_TEMP,:RACK_HUM,:ORG_YMD)`;
let binds = {
  RCV_DT: 2019-08-14T12:00:17.187Z,
  RCV_YMD: '20190814120017',
  CENTER_CD: 'U',
  ID: 'U3-L01-08',
  NAME: 'msTemperatureMeasurement',
  ORG_YMD: '20190814120017',
  RACK_TEMP: 26,
  RACK_HUM: 0 
}

let bindDef = {
    RCV_DT : { type:oracledb.DB_TYPE_DATE},
    RCV_YMD : { type:oracledb.DB_TYPE_CHAR},
    CENTER_CD : { type:oracledb.DB_TYPE_CHAR},
    ID : { type:oracledb.DB_TYPE_VARCHAR},
    NAME : { type:oracledb.DB_TYPE_VARCHAR},
    ORG_YMD : { type:oracledb.DB_TYPE_CHAR},
    RACK_TEMP : { type:oracledb.DB_TYPE_NUMBER},
    RACK_HUM : { type:oracledb.DB_TYPE_NUMBER}
}

if( !_.isNil(sql) ){
    var options = {autoCommit: true,bindDefs:_.cloneDeep(bindDef)};
    // ora.executeMany(sql, binds, options, function (err, result) {
    await ora.execute(sql, binds, options, function (err, result) {
        if (err){
            //return cb(err, conn);
            console.log(err);
        }else {
            console.log(result);
        }
    });
}
neophylon picture neophylon on 14 Aug 2019

Try this:

'use strict';

const oracledb = require('oracledb');
const config = require('./dbconfig.js');

let options, result;

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(config);

    try {
      await connection.execute(`DROP TABLE TEST`);
    } catch(e) {
      console.log(e);
    }

    await connection.execute(`CREATE TABLE TEST (
             RCV_DT DATE DEFAULT SYSDATE,
             RCV_YMD CHAR(14 BYTE),
             ORG_YMD CHAR(14 BYTE),
             CENTER_CD CHAR(1 BYTE),
             ID VARCHAR2(13 BYTE),
             NAME VARCHAR2(50 BYTE),
             RACK_TEMP NUMBER(8,3),
             RACK_HUM NUMBER(8,3)
           )`);

    const sql = `INSERT INTO TEST VALUES (
                   :RCV_DT,
                   :RCV_YMD,
                   :ORG_YMD,
                   :CENTER_CD,
                   :ID,
                   :NAME,
                   :RACK_TEMP,
                   :RACK_HUM
                )`;

    const binds = [
      {
        RCV_DT: new Date(),
        RCV_YMD: '20190814120017',
        CENTER_CD: 'U',
        ORG_YMD: '20190814120017',
        ID: 'U3-L01-08',
        NAME: 'msTemperatureMeasurement',
        RACK_TEMP: 26,
        RACK_HUM: 0
      }
    ];

    const bindDef = {
      RCV_DT: { type: oracledb.DATE },
      RCV_YMD: { type: oracledb.STRING, maxSize: 14 },
      ORG_YMD: { type: oracledb.STRING, maxSize: 14 },
      CENTER_CD: { type: oracledb.STRING, maxSize: 1 },
      ID: { type: oracledb.STRING, maxSize: 13 },
      NAME: { type: oracledb.STRING, maxSize: 50 },
      RACK_TEMP: { type: oracledb.NUMBER },
      RACK_HUM: { type: oracledb.NUMBER }
    };

    const options = {
      autoCommit: true,
      bindDefs:bindDef
    };

    let result = await connection.executeMany(sql, binds, options);
    console.log(result.rowsAffected);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();
cjbj picture cjbj on 14 Aug 2019
🚀1 ❤1 🎉1 😄1 👍1

I'm guessing the table's logical column order isn't matching your insert statement. Or perhaps you're inserting into the wrong table altogether.

You provided a create table statement that started with CREATE TABLE "SYSTEM"."TEST", but your code is targeting DCMS.TEST. Are you sure this is right?

In either case, I recommend adding an explicit columns list to your insert statement.

dmcghan picture dmcghan on 14 Aug 2019

Thank you. it's done.

neophylon picture neophylon on 16 Aug 2019

Thanks for letting us know. I'll close this now.

cjbj picture cjbj on 16 Aug 2019
Was this page helpful?
0 / 5 - 0 ratings

Related issues

rstor1 picture rstor1  路  4Comments
satodu picture satodu  路  3Comments
eunier picture eunier  路  4Comments
ChrisHAdams picture ChrisHAdams  路  3Comments
cristian-programmer picture cristian-programmer  路  4Comments