stored proc query has CLOB data type and is not retuned by Oracle node js library

[Rajanusa]

1. Review the user manual

2. Describe the problem
   Cut and paste text showing the command you ran. No screenshots. Use a gist for long screen output and logs: see https://gist.github.com/.

I have written common class with queryList method where I execute stored proc based on parameter passed to it. It works well but when the stored proc has CLOB, it is not returning the out put from this method.

 async queryList<T>(
    sqlStatement: string,
    inputParameters: oracledb.BindParameters,
    returnType: [new () => T],
    outCursor?: string,
    databaseName?: string,
  ): Promise<T[]> {
    let connection: oracledb.Connection;
    try {
      outCursor = outCursor || 'out_cursor';
      const bindParameters = inputParameters;
      bindParameters[outCursor] = {
        dir: oracledb.BIND_OUT,
        type: oracledb.CURSOR,
      };
      connection = await this.getConnection(databaseName);
      const queryResult = await connection.execute(
        this.formatQuery(sqlStatement, bindParameters),
        bindParameters,
        { outFormat: oracledb.OUT_FORMAT_OBJECT, fetchArraySize: 1000000 },
      );
      const cursor = queryResult.outBinds[outCursor];
      const result = await this.consumeStream(cursor.toQueryStream());
      const returnResult = MapUtils.deserialize(returnType, result);
      this.updateLastUtilizedTimestamp(
        databaseName ? databaseName : this.defaultDatabase,
      );
      return (returnResult as unknown) as T[];
    } catch (error) {
      this.logger.error(error, null);
      throw error;
    } finally {
      if (connection) {
        await connection.release();
      }
    }
  }

 private async consumeStream<T>(queryStream: any): Promise<T[]> {
    const result = [];
    const consumerStream = new Promise((resolve, reject) => {
      queryStream.on('data', row => {
        result.push(row);
      });
      queryStream.on('error', error => {
        reject(error);
      });
      queryStream.on('metadata', metadata => {
        // implement logic to handle meta data if required
      });
      queryStream.on('close', resolve);
    });
    await consumerStream;
    return result as T[];
  }

The code execute till consumestream method after that it is not .
In the consumeStream method, instead queryStream.on('data') event it call queryStream.on('close').
Please advise generic solution to specify clob as string.

Since it is generic method, I cannot write static code for one parmeter.
I found this in your documenation
oracledb.fetchAsString = [ oracledb.CLOB ];
When I do this, it is say it is read only property.
I import the library as below.
import * as oracledb from 'oracledb';
Please advise generic solution instead specifying for the particular parameter
3. Include a runnable Node.js script that shows the problem.

4. Run node and show the output of:

"oracledb": "^4.1.0",

5. What is your Oracle Database version?
   Oracle 12G

[cjbj]

It's not clear whether you are calling a PL/SQL procedure, or executing a query. A runnable testcase would have been handy - I remember now why I didn't immediately respond.

If you are calling a stored procedure: For LOBs smaller than 1Gb and that can fit in memory, bind as a STRING.

const sql2 = `begin myproc(:bv); end;`;  // PL/SQL is myproc (p1 out CLOB)
const binds2 = {bv: {type:oracledb.VARCHAR, dir: oracledb.BIND_OUT }};
const result2 = await connection.execute(sql, binds);
const d2 = await result.outBinds.bv.getData();
console.log(d2.length);

Or the asynchronous lob.getData() function could be used. This is also handy when Oracle DB objects contain LOBS:

const sql = `begin myproc(:bv); end;`; // PL/SQL is myproc (p1 out CLOB)
const binds = {bv: {type:oracledb.CLOB, dir: oracledb.BIND_OUT }};
const result = await connection.execute(sql, binds);
const d = await result.outBinds.bv.getData();
console.log(d);  // a string with the LOB data

For queries you could try:

import oracledb from 'oracledb';
oracledb.fetchAsString = [ oracledb.CLOB ];

or

sql = `SELECT TO_CLOB('abc') AS MYCLOB FROM DUAL`;
options = { extendedMetaData: true, fetchInfo: {"MYCLOB": { type: oracledb.STRING } } };