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?
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).