Forum Stats

  • 3,816,408 Users
  • 2,259,185 Discussions


Provision in ORDS for handling queries involving joins across multiple tables

Iqb Member Posts: 1

ORDS enables the Oracle database objects as REST API on which CRUD operations can be performed. However I would like to understand is there any provision there to handle the queries invoving multiple tables via JOINS.

For example, say I have a query like this:

select empname from employee e,dept d where e.deptid=d.deptid and deptname='IT';

Below is the table meta-data:








I do understand that we can create a handler wherein we can mention this query in the source and expose an ORDS API corresponding to this. However, I would like to expose only the database objects as REST APIs instead of creating handlers for each query.


  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,520 Employee
    edited Dec 26, 2018 12:11PM

    Create a VIEW, and then REST enable that view.

  • user525840
    user525840 Member Posts: 1 Blue Ribbon

    I have exactly same requirement as mentioned and based on your suggestion, I have created DB view and tried using it via endpoint URL. However it is not working for me as it don't find PK.

    Here is my view metadata: Where you can see primary key is null.


  • Mike Kutz
    Mike Kutz Member Posts: 6,189 Silver Crown

    Not all VIEWS are updateable.

    CRUD may not be the most appropriate method to manipulate data.

    Model-View-Contol [MVC] could be best for this situation.

    • hide the data Model
    • Access via VIEW
    • Control data manipulation via Transactional API [XAPI]

    XAPI is nothing more then a Package.

    You can REST Enable the Package.

    (@thatJeffSmith-Oracle ) but - I don't think you can link a XAPI to a Table/View.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,520 Employee

    Be specific, you want queries that hit multiple tables, w/o having to hard-code the queries in your handlers? Create a view and REST Enable it.

    Or, you want those collections to be full GET, PUT, POST, DELETE (CRUD on a view, maybe)?