Forum Stats

  • 3,816,408 Users
  • 2,259,185 Discussions
  • 7,893,475 Comments

Discussions

Provision in ORDS for handling queries involving joins across multiple tables

Iqb
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:

employee:

empid

empname

deptid

dept:

deptid

deptname

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.

Answers

  • 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.

    {"name":"VW_MATERIAL_DTLS","primarykey":[],"members":[{"name":"mtl_id","type":"NUMBER"},{"name":"mtl_type_desc","type":"VARCHAR2"},{"name":"mtl_desc_short","type":"VARCHAR2"},{"name":"mtk_type_cd","type":"NUMBER"},{"name":"mtl_desc","type":"VARCHAR2"},{"name":"mtl_size","type":"VARCHAR2"},{"name":"mtl_spec1","type":"VARCHAR2"},{"name":"mtl_spec2","type":"VARCHAR2"},{"name":"mtl_price","type":"NUMBER"}],"links":[{"rel":"collection","href":"http://localhost:9090/ords/temp_db/metadata-catalog/","mediaType":"application/json"},{"rel":"canonical","href":"http://localhost:9090/ords/temp_db/metadata-catalog/vw_material_dtls/","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:9090/ords/temp_db/open-api-catalog/vw_material_dtls/","mediaType":"application/openapi+json"},{"rel":"describes","href":"http://localhost:9090/ords/temp_db/vw_material_dtls/"}]}
    


  • 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)?