Forum Stats

  • 3,815,996 Users
  • 2,259,128 Discussions


How to get multiple outputs in reply from database

3514954 Member Posts: 1


I am trying to do a service using BPEL.I need to get details of all the entries with one input.

Say: I had a user in the database and am trying to retrieve all the projects he had entered in the database.

I choseret1.pngret2.png rest adapter as an external service and database as exposed service. In BPEL I used for each activity to do this.

Resolve this error for me.Thanks in advance

Martien van den Akker


  • Eric Elzinga
    Eric Elzinga Member Posts: 2,690
    edited Aug 31, 2017 2:01AM

    When you created the db adapter you also added a parameter like p_user to it.

    When you click through the wizard at the end it will show an example query which it will generate.

    If you copy this query and execute it directory on the db, does it return a list of projects for the p_user param?

    select * from projects where p_user = '1234'

    if this query returns a list of projects, it will also return a collection in your bpel process.

    Note sure why the db invoke is in the for-each, but i would say, place it outside of the for-each, so all the projects for the user will be retrieved only once.

    After that you could use a for-each to call some other service, or just use a xslt to transform the outputVariable of the db-select to your outputVariable of the restservice.

    or is the for-each there for some other reason?

    Martien van den Akker
  • Krishna Hanumantharao
    Krishna Hanumantharao Member Posts: 613
    edited Aug 31, 2017 2:14AM

    Follow Eric's answer to get all records in one go instead of forEach.

    Based on the warning, that is in your screenshot, you are actually getting multiple records. To remove the warnings,in your xpath expression you need to use predicates to get a specific record (like Projects[1] ) .

    Martien van den Akker
  • Vikrant Korde.
    Vikrant Korde. Member Posts: 338 Bronze Badge
    edited Sep 5, 2017 12:51AM

    If you are comfortable with stored procedure or package, you can write one of these with multiple OUT parameters. In the OUT parameters you can send a cursor (list of records) or individual values.


    Vikrant Korde.

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited Sep 5, 2017 2:44AM


    A few years ago I created a framework based on xslt and xml queries on the datadictionary to generate oracle types on a datamodel in the database. Having that it's really easy to create a procedure that fetches a complete  hierarchical set of data in one pl/sql call. This year I open sourced it, but did not had a change to work on it. It works like a charm, but need some documentation. See Darwin-IT: Introducing Darwin Oracle Type Accelerator .


This discussion has been closed.