node.jsoracle-databaseplsqlnode-oracledb

Looping through PL/SQL collection using oracledb npm library


I am using oracledb npm library to call a PL/SQL function with OUT parameter defined as a collection.

FUNCTION f_get_messages_for_processing (
  y_message_list        OUT thrgtw_adapter.tc_pcrf_message
) RETURN NUMBER

tc_pcrf_message is defined as follows:

create or replace type thrgtw_adapter.to_pcrf_message is object (
  id_message           number,
  message_type         number,
  msisdn               varchar2(15)
)

create or replace type thrgtw_adapter.tc_pcrf_message is table of thrgtw_adapter.to_pcrf_message

How can I access and loop through messageList in javascript? I am calling PL/SQL function as follows:

const statement = `
  BEGIN
    :ret := thrgtw_adapter.api_pcrf.f_get_messages_for_processing(:messageList);
  END;
`;

const variables = {
  ret: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER},
  messageList: { dir: oracledb.BIND_OUT, type: 'THRGTW_ADAPTER.TC_PCRF_MESSAGE'}
};

return await db.execute(statement, variables);

The problem is that when I write the result to console, I can see the data, I just don't know how to access it, since none of the following things work:

const resultSet = await dbCommands.getMessagesForProcessing();

console.log(resultSet.outBinds.messageList)
console.log(typeof resultSet.outBinds.messageList) // output 'object'
console.log(Object.keys(resultSet.outBinds.messageList)) //output ['_impl']
resultSet.outBinds.messageList.forEach((item) => { // fails TypeError: resultSet.outBinds.messageList.forEach is not a function
  console.log(item)
})

First console log outputs following data, so according to typeof resultSet.outBinds.messageList it's an object, but no curly brackets in output. Object.keys returns an array with one value '_impl'

[THRGTW_ADAPTER.TC_PCRF_MESSAGE] [
  { ID_MESSAGE: 1, MESSAGE_TYPE: 31, MSISDN: '421000000001' },
  { ID_MESSAGE: 2, MESSAGE_TYPE: 32, MSISDN: '421000000002' },
  { ID_MESSAGE: 3, MESSAGE_TYPE: 32, MSISDN: '421000000003' },
  { ID_MESSAGE: 4, MESSAGE_TYPE: 32, MSISDN: '421000000004' }
]

So what can I do to loop through messageList?


Solution

  • Check out this part of the documentation https://node-oracledb.readthedocs.io/en/latest/user_guide/objects.html#objects, specifically chapter 15.2. It mentions the attribute dbObjectAsPojo, which, when set to true, will cause the database to return plain JSON objects (POJO) and not the Proxy(DbObject).