Node-oracledb: Service Returns Empty Result

[zhangjinzhou]

I am working on a service which selects data from database. It calls a stored procedure in oracle database. The service is supposed to return a json with the selected record. It should look like this:
{"features":[{"properties":{"ROUTE_ID":"MAIN","FM":100,"TM":115,"BD":"01-JAN-12","ED":"01-JAN-13","RESULT_TYPE":6,"LRS_SHOULDER_WIDTH_1_ID":1,"LRS_SPEED_2_ID":1,"LRS_ROUTE_PARTS_3_ID":1,"WIDTH_1":6,"ROUTE_ID_2":"MAIN","SPEED_2":45,"RNUM":1}}, .......... {"properties":{"ROUTE_ID":"MAIN","FM":80,"TM":85,"BD":"01-JAN-08","ED":"01-JAN-09","RESULT_TYPE":6,"LRS_SHOULDER_WIDTH_1_ID":7,"LRS_SPEED_2_ID":1,"LRS_ROUTE_PARTS_3_ID":1,"WIDTH_1":6,"ROUTE_ID_2":"MAIN","SPEED_2":45,"RNUM":9}}]}

However, what I got is an empty json like this:
"po_results_json": "{\"features\":[]}"

When I set the BINDS_OUT type as CLOB, it returns metadata:

"po_results_json": {
    "_readableState": {
      "objectMode": false,
      "highWaterMark": 16384,
      "buffer": {
        "head": null,
        "tail": null,
        "length": 0
.............
      },
      "length": 0,
      "pipes": null,
      "pipesCount": 0,
      "flowing": null,
    },
  ............
  }

Here are the codes associate with the problem.
Simplified Stored Procedure definition:

PROCEDURE gsj_report_sel(
            p_process_id IN LB_GSJ_PROCESSES.LGP_ID%TYPE,
            p_report_name IN LB_GSJ_REPORTS.LGR_NAME%TYPE,
            p_filter IN CLOB := '<ST_FILTER><ROUTE/><F_MEAS/><T_MEAS/><B_DATE/><E_DATE/><OTHER/></ST_FILTER>',
            p_page_no IN INTEGER := 1,
            po_results_json OUT CLOB,
            p_page_size IN INTEGER := 250,
            pio_rowcnt IN OUT NUMBER   -- set to -1 if rowcount is required
        ) ; 

Here is how do I call it:

function runQuerySP(query, bindPram, callback, errCallback) {
    oracledb.getConnection(config, (err, connection) => {
        if (err) {
            errCallback(err);
            return;
        }
        connection.execute(query, bindPram, (err, result) => {
            if (err) {
                errCallback(err);
                return;
            }
            callback(result.outBinds);
        });
    });
}

    gsjReportSel(input: GSJReportSel): Promise<void> {
        let query = "BEGIN gistic.lb.gsj_report_sel(:p_process_id, :p_report_name, :p_filter, :p_page_no, :po_results_json, :p_page_size, :pio_rowcnt); END;";
        let bindPram = {
            p_process_id: {
                val: input.p_process_id,
                dir: oracledb.BIND_IN,
                type: oracledb.NUMBER
            },
            p_report_name: {
                val: input.p_report_name,
                dir: oracledb.BIND_IN,
                type: oracledb.STRING
            },
            p_filter: {
                val: "<ST_FILTER><ROUTE/><F_MEAS/><T_MEAS/><B_DATE/><E_DATE/><OTHER/></ST_FILTER>",
                dir: oracledb.BIND_IN,
                type: oracledb.STRING
            },
            p_page_no: {
                val: 1,
                dir: oracledb.BIND_IN,
                type: oracledb.INTEGER
            },
            po_results_json: {
                dir: oracledb.BIND_OUT,
                type: oracledb.STRING
            },
            p_page_size: {
                val: 10,
                dir: oracledb.BIND_IN,
                type: oracledb.INTEGER
            },
            pio_rowcnt: {
                type: oracledb.NUMBER,
                dir: oracledb.BIND_IN,
                val: -1
            }
        };
        return new Promise((resolve, reject) => {
            runQuerySP(query, bindPram, resolve, reject);
        });
    }

I am using node 6.9.4 and oracle instant client 12.1.

Thanks in advance.

--Jinzhou

[dmcghan]

@zhangjinzhou

What version of the driver are you using?

However, what I got is an empty json like this

The fact that you're getting a CLOB with some keys and values is good, that indicates the procedure is being called correctly. The empty array value seems to point to a query in the process not fetching any rows.

While the cause could be related to a wide variety of things, it's likely that one of the input parameters isn't going in correctly. Do you have access to the procedure? If so, can you verify that values are going in correctly? Often this is done by logging the input parameters to a table. Logger is a good tool for this.

When I set the BINDS_OUT type as CLOB, it returns metadata

In that case, it returns a stream, but with the current version of the driver that's only needed for large LOBs...

I noticed the bind direction for pio_rowcnt is oracledb.BIND_IN. I think it should be oracledb.BIND_INOUT, no?

Also, two binds are using a bind type of oracledb.INTEGER, which isn't a valid bind type.

Fix those issues and then retest. If that doesn't work, try logging the input values to ensure they are going in as you expect. Let us know what you find and we'll go from there.

[cjbj]

[@dmcghan I deleted a dup reply of yours]

[zhangjinzhou]

Hi @dmcghan I used logger and found one parameter not assigned properly. I changed it and was able two see the correct response in logger! However, when I test it in my API it shows I must use CLOB as Bind_OUT type because the size of response is too big. I changed the type to CLOB but it only returns metadata of the CLOB. How can I grab the actural CLOB content?

[dmcghan]

@zhangjinzhou Glad to hear you got logger working!

However, when I test it in my API it shows I must use CLOB as Bind_OUT type because the size of response is too big.

You indicated you were using the 12.1 client. According to this, you should be able to fetch CLOBs up to 1 GB (minus 2 bytes). Is your CLOB really that large?

I changed the type to CLOB but it only returns metadata of the CLOB.

Again, you're not seeing "metadata". You're logging the properties of a Node.js stream. Here's an example of streaming a CLOB that's returned from a procedure call.

Assuming the following database table and procedure:

create table t (
  c clob 
);

insert into t (c) values ('the clob');

commit;

create or replace procedure get_clob(
  p_clob_out out clob
)
is

begin

  select c
  into p_clob_out
  from t;

end;
/

The following could be used to stream the CLOB out:

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

oracledb.getConnection(config, function(err, conn) {
  if (err) {
    throw err;
  }

  conn.execute(
   `begin
      get_clob(:clob);
    end;`,
    {
      clob: {
        dir: oracledb.BIND_OUT,
        type: oracledb.CLOB
      }
    },
    function(err, result) {
      if (err) {
        throw err;
      }

      processResult(result, conn);
    }
  );
});

function processResult(result, conn) {
  var clobStream = result.outBinds.clob;
  var clobContent = '';

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

  clobStream.on('end', function() {
    console.log('Got all the clob content! Final text is:', clobContent);
  });

  clobStream.on('close', function() {
    conn.close(function(err) {
      if (err) {
        throw err;
      }
    });
  });

  clobStream.on('error', function(err) {
    throw err;
  });
}

[zhangjinzhou]

@dmcghan problem solved! Thank you so much!

[cjbj]

For future readers, there are also LOB examples in https://github.com/oracle/node-oracledb/tree/master/examples