Provision in ORDS for handling queries involving joins across multiple tables

Iqb

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.

thatJeffSmith-Oracle

Create a VIEW, and then REST enable that view.

user525840

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

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

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