1 Reply Latest reply on Aug 3, 2016 6:11 PM by Mike Kutz

    Returing Multiple Results from PLSQL with Apex RESTFul Services

    Andrew Devenish-Meares

      Hi All,

       

      NB: Also posted this in the Apex forum, and then realised this may be a better place.

       

      I'm trying to create a REST Service in Apex to look up some user data.

       

      I need to collate some data from both OpenLDAP and a local database, so I've created a RESTFul Service Module and a Resource Handler with PLSQL as the source.

       

      I've written PLSQL to handle the LDAP search, which will allow for Wildcards, etc.  I've also set up a bunch of OUT bind variables.

       

      When the search happens, however, I only get one result, despite there being multiple results in LDAP.

       

      I would assume this is simply because what's being returned is simply the final result of the bind variables.

       

      I am having issues finding information on how I could write PLSQL to return a set of results (rather than a single result) in a REST service when the data does not exist solely in a table.

       

      If someone could provide some examples, or point to some specific documentation on how to achieve this, then that would be most appreciated.

       

      Andrew Devenish-Meares

      Solutions Analyst/Programmer

      University of New England

      Armidale, Australia

        • 1. Re: Returing Multiple Results from PLSQL with Apex RESTFul Services
          Mike Kutz

          Start with solving this:

          Andrew Devenish-Meares wrote:

           

          I am having issues finding information on how I could write PLSQL to return a set of results (rather than a single result).

          You have two methods:

          1. Your OUT parameters need to return a COLLECTION.
          2. If not, it needs to return something that understands "multiple results"  (ex XMLType)
          3. rewrite it as a PIPELINED FUNCTION
            • this could be a "wrapper" that calls (1) or (2) but returns the values as a Pipelined function

           

          At the very least, I like creating a PIPELINED FUNCTION because you can then use it in SQL.

           

          MK