Returning a collection from PL/SQL using node-oracledb

850148a8-3ea2-40f9-b4bf-558d2cc1f568

I've been exploring oracledb and I'm not sure how I should return a collection.  For example, I have the following call:

        connection.execute(
          'DECLARE ' +
          '  TYPE subscription_t IS RECORD ( ' +
          '    subscriptionId   INT, ' +
          '    eventTypeId      INT); ' +
          '  TYPE subscriptions_t IS TABLE OF subscription_t; ' +
          '  l_subscriptions subscriptions_t; ' +
          '  l_subscription subscription_t; ' +
          'BEGIN ' +
          '  l_subscriptions := subscriptions_t(); ' +
          '  for i in 1 .. ... loop ' +
          '    l_subscription.subscriptionId := ...; ' +
          '    l_subscription.eventTypeId := ...; ' +
          '    l_subscriptions.EXTEND; ' +
          '    l_subscriptions(l_subscriptions.LAST) := l_subscription; ' +
          '  end loop; ' +
          '  :ret := l_subscriptions; ' +
          'END;',
          {
            ret: { dir: oracledb.BIND_OUT, type: ??? }
          },
          {
            // ???resultSet: true
          },
          function (err, result) {...
          })

I'm not sure how I can return the value of l_subscriptions back to JS.  I.e. what should :ret be assigned to, and what should the type of ret be set to?

Apologies in advance if this is obvious, I'm just getting started with this stuff.

Christopher Jones-Oracle

You need to create some kind of PL/SQL mapping functions/procedures to map the record type to scalar values that can be passed back to node-oracledb. See https://github.com/oracle/node-oracledb/issues/147