Forum Stats

  • 3,875,860 Users
  • 2,266,976 Discussions
  • 7,912,356 Comments

Discussions

Returning a collection from PL/SQL using node-oracledb

850148a8-3ea2-40f9-b4bf-558d2cc1f568
edited Oct 23, 2015 1:04AM in Node.js

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.

Answers