node-oracledb

11. Working with CLOB and BLOB Data

The Lob Class in node-oracledb implements the
Node.js Stream interface to provide
read and write access to CLOB and BLOB database columns and PL/SQL
bind parameters. Note, currently only BIND_OUT is supported for LOB bind
parameters.

It is the application's responsibility to make sure the connection is
not released while a Lob operation such as pipe() is in progress.

Readable Lobs

Being a Stream object, a Lob being read from the database has two
modes of operation: "flowing mode" and "paused mode". In flowing mode
data is piped to another stream, or events are posted as data is read.
In paused mode the application must explicitly call read() to get
data.

The read(size) unit is in characters for CLOBs and in bytes for BLOBs.

When reading a LOB from the database, make sure to fetch all data.
Resources are only released at completion of the read or if there is a
LOB error.

A Readable Lob object starts out in paused mode. If a 'data' event
handler is added, or the Lob is piped to a Writeable stream, then the
Lob switches to flowing mode.

For unpiped Readable Lobs operating in flowing mode where the Lob is
read through event handlers, the Lob object can be switched to paused
mode by calling pause(). Once the Lob is in paused mode, it stops
emitting data events.

Similarly, a Readable Lob operating in the paused mode can be switched
to flowing mode by calling resume(). It will then start emitting
'data' events again.

Writeable Lobs

Lobs are written to the database with pipe(). Alternatively the
write() method can be called successively, with the last piece being
written by the end() method. The end() method must be called
because it frees resources. If the Lob is being piped into, then the
write() and end() methods are automatically called.

Writeable Lobs also have events, see the
Node.js Stream documentation.

Examples

There are runnable LOB examples in the GitHub
examples
directory.

The following code is based on example
clobinsert1.js.
It shows inserting text into an Oracle Database CLOB column using
flowing mode. It first inserts an EMPTY_CLOB() and uses a
RETURNING INTO clause to pass the new LOB locator to node-oracledb,
where it is available as an instance of the Lob Class. A
Stream pipe() call loads data into the LOB. The data is committed
when the 'end' event fires, indicating the load has completed.
Autocommit is disabled when getting the locator because a transaction
must be open until the LOB data is inserted. In this example the
input data stream is simply a file on disk. Both the Lob stream and
input data stream have 'error' events to handle unexpected issues:

var oracledb = require('oracledb');
. . .
connection.execute(
  "INSERT INTO mylobs (id, c) VALUES (:id, EMPTY_CLOB()) RETURNING c INTO :lobbv",
  { id: 1, lobbv: {type: oracledb.CLOB, dir: oracledb.BIND_OUT} },
  { autoCommit: false },  // a transaction needs to span the INSERT and pipe()
  function(err, result)
  {
    if (err) { console.error(err.message); return; }
    if (result.rowsAffected != 1 || result.outBinds.lobbv.length != 1) {
      console.error('Error getting a LOB locator');
      return;
    }
    var lob = result.outBinds.lobbv[0];
    lob.on('error', function(err) { console.error(err); });
    lob.on('finish',
      function()
      {
        connection.commit(
          function(err)
          {
            if (err)
              console.error(err.message);
            else
              console.log("Text inserted successfully.");
            connection.close(function(err) {
              if (err) console.error(err.message);
            });
          });
      });
    console.log('Reading from ' + inFileName);
    var inStream = fs.createReadStream(inFileName);
    inStream.on('error',
      function(err)
      {
        console.error(err);
        connection.close(function(err) {
          if (err) console.error(err.message);
        });
      });
    inStream.pipe(lob);  // copies the text to the CLOB
  });
  ```
The `EMPTY_CLOB()` / `RETURNING INTO` sequence is used because
node-oracledb currently does not support temporary LOBs and `BIND_IN`
for LOBs.  A similar sequence is used to update LOBs.
The following example shows selecting a CLOB using flowing mode and
writing it to a file.  It is similar to the example
[`clobstream1.js`](https://github.com/oracle/node-oracledb/tree/master/examples/clobstream1.js).
The returned column value is a Lob stream which is piped to an opened
file stream.
By default the Lob stream is a Node.js buffer - which is useful for
BLOB data.  Since this example uses a CLOB, the `setEncoding()` call
is used to indicate data should be a string.  Both the Lob stream and
output data stream have 'error' events to handle unexpected issues:
```javascript
var oracledb = require('oracledb');
. . .
connection.execute(
  "SELECT c FROM mylobs WHERE id = :id",
  { id: 1 },
  function(err, result)
  {
    if (err) { console.error(err.message); return; }
    if (result.rows.length === 0) { console.log("No results"); return; }
    var lob = result.rows[0][0];
    if (lob === null) { console.log("CLOB was NULL"); return; }
    lob.setEncoding('utf8');  // we want text, not binary output
    lob.on('error', function(err) { console.error(err); });
    lob.on('close', function() {
      connection.close(function(err) { if (err) console.error(err.message); });
    });
    console.log('Writing to ' + outFileName);
    var outStream = fs.createWriteStream(outFileName);
    outStream.on('error', function(err) { console.error(err); });
    lob.pipe(outStream);
  });