Node-oracledb: Query streaming CLOB's from a Ref Cursor

[PaulBrookes]

I'm trying to query stream CLOBs from a Ref Cursor that is returned from a PL/SQL procedure, but I'm struggling to get to the actual CLOB data.

When I stream rows from the Cursor, all I see returned are the properties from the Node.js stream (similar to this issue: [https://github.com/oracle/node-oracledb/issues/604]):

Lob {
  _readableState:
   ReadableState {
     objectMode: false,
     highWaterMark: 16384,
     buffer: BufferList { head: null, tail: null, length: 0 },
     length: 0,
     pipes: null,
     pipesCount: 0,
     flowing: null,
     ended: false,
     endEmitted: false,
     reading: false,
     sync: true,
     needReadable: false,
     emittedReadable: false,.....

The difference is that I am using oracledb.CURSOR as a bind out, and not oracledb.CLOB.

Here's a callback function that executes the PL/SQL procedure that returns a Ref Cursor of CLOB JSON messages (along with an ID column):

function Callback(message) {     
    oracledb.getConnection(
    {
        user          : dbConfig.user,
        password      : dbConfig.password,
        connectString : dbConfig.connectString
      },
      function(err, connection) {
        if (err) {
          console.error(err.message);
          return;
        }
        connection.execute(
          `BEGIN si_messaging.get_event_messages(:eventType, :cursor); END;`,
          {
            eventType   :  'DR',
            cursor      :  { type: oracledb.CURSOR, dir : oracledb.BIND_OUT }
          },
          function(err, result) {
            var cursor;
            var queryStream;

            if (err) {
              console.error(err.message);
              doRelease(connection);
              return;
            }

            cursor = result.outBinds.cursor;
            queryStream = cursor.toQueryStream();

            queryStream.on('data', function (row) {
              const messageBuffer = row[0];
              console.log(messageBuffer);
              //const messageBuffer = JSON.stringify(row[0]);
              //sendBufferToRATopic(messageBuffer);           
            });

            queryStream.on('metadata', function (metadata) {
                console.log(metadata);
            });

            queryStream.on('error', function (err) {
              console.error(err.message);
              doRelease(connection);
            });

            queryStream.on('close', function () {
              doRelease(connection);
            });
          }
        );
      }
    );    

    function doRelease(connection) {
        connection.close(
            function(err) {
                if (err) { console.error(err.message); }
            });
    };        
};

The above callback function gets called from a function that subscribes to Continuous Query Notification (CQN) changes on a table.

Metadata for Ref Cursor is:

[ { name: 'MCE_ID',
    fetchType: 2002,
    dbType: 2,
    nullable: true,
    precision: 0,
    scale: -127 },
  { name: 'MESSAGE', fetchType: 2006, dbType: 112, nullable: true } ]

I just can't figure out how to get to the CLOB data. Am I trying to do something that is not possible?

Thanks in advance for any help figuring this out.

---

What is your Node.js version: v8.12.0, x64
What is your node-oracledb version: 3.0.0
What OS (and version) is Node.js executing on: AWS Linux
What is your Oracle client version: 12.1.0.2.0
What is your Oracle Database version: 12.2.0.1.0
What is the LD_LIBRARY_PATH: /home/oassis/product/oracle/12.1/lib

[dmcghan]

Here's an example that's geared toward your specific use case...

Given these objects:

create table t (
  c clob 
);

insert into t (c) values ('clob 1');
insert into t (c) values ('clob 2');

commit;

create or replace procedure get_cursor(
  p_cursor_out out sys_refcursor
)
is

begin

  open p_cursor_out for
  select c
  from t;

end;
/

The following should work:

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

async function runTest() {
  let conn;

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

    const result = await conn.execute(
      'call get_cursor(:cursor)',
      {
        cursor: {
          dir: oracledb.BIND_OUT,
          type: oracledb.CURSOR
        }
      }
    );

    let row;

    while (row = await result.outBinds.cursor.getRow()) {
      const clobContent = await getClobContentFromRow(row);
      console.log(clobContent);
    }

    console.log('Processed all rows, closing conn.');
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

function getClobContentFromRow(row) {
  return new Promise((resolve, reject) => {
    console.log('Processing row.');

    const clobStream = row[0];
    let clobContent = '';

    clobStream.on('data', chunk => {
      clobContent += chunk;
    });

    clobStream.on('end', () => {
      console.log('Got all the CLOB content.');
    });

    clobStream.on('close', () => {
      console.log('CLOB closed.');

      resolve(clobContent);
    });

    clobStream.on('error', reject);
  });
}

[PaulBrookes]

Thanks Dan, I'll give it a whirl.

[cjbj]

@PaulBrookes if you aren't working with huge CLOBs, consider fetching them as Strings:

async function runTest2() {

  oracledb.fetchAsString = [ oracledb.CLOB ];

  let conn;

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

    const result = await conn.execute(
      'call get_cursor(:cursor)',
      {
        cursor: {
          dir: oracledb.BIND_OUT,
          type: oracledb.CURSOR
        }
      }
    );

    let row;

    while (row = await result.outBinds.cursor.getRow()) {
      const clobContent = row[0];
      console.log(clobContent);
    }

    console.log('Processed all rows, closing conn.');
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

It is worth benchmarking to check memory and performance of the two solutions.

[PaulBrookes]

Many thanks for both examples @dmcghan and @cjbj.