How to Insert CLOB into Oracle DB via Nodejs?

madkimchi 0
Viewed 21

I have a table that I need to insert a CLOB into. The field is defined as below:

LOB ("DETAILS") STORE AS BASICFILE (
  TABLESPACE "DEV_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE 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)) ;

In node.js, I am building a list that I will send in a function to add it to the DB. My node.js code looks like below:

const fs = require('fs');
var path = require("path");

let con = await getConnection();
let str = fs.readFileSync(path.resolve(__dirname, './sameple-file.txt'), 'utf8');

let dataList = {
  'OTHER_COLUMN': 1234,
  'CLOB_COLUMN': {CLOB_ADDED_HERE}
   };

await addRecord(con, dataList );
await con.commit();
con.close();

What I'm having an issue with is what needs to be added in CLOB_ADDED_HERE for the insert to be successful and ideally with source text coming from a variable, or constant, instead of a file.

clob oracle node.js
1 Answers

When in doubt, read the documentation. See Working with CLOB, NCLOB and BLOB Data. Also check the examples. From the doc:

Given the table:

CREATE TABLE mylobs (id NUMBER, c CLOB, b BLOB);

an INSERT example is:

const fs = require('fs');
const str = fs.readFileSync('example.txt', 'utf8');
. . .

const result = await connection.execute(
  `INSERT INTO mylobs (id, myclobcol) VALUES (:idbv, :cbv)`,
  { idbv: 1, cbv: str }  // type and direction are optional for IN binds
);

console.log('CLOB inserted from example.txt');
. . .
Christopher Jones0
Related