Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Provision in ORDS for handling queries involving joins across multiple tables

IqbDec 24 2018 — edited Dec 26 2018

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.

Comments

Keyser
Answer

it's the same model, only the view has changed (i think)

I don't know what element is triggering your code but I don't think this...

var trigger = $(this.triggeringElement);

var rowId = trigger.closest('tr').data('id');

...will get you what you want when you are in single record view mode

I tried this...

apex.region("theIG").widget().interactiveGrid("getViews", "grid").getActiveRecordId()

and it works if you are in edit mode...(using empno as my primary key column)

apex.region("theIG").widget().interactiveGrid("getViews", "grid").getActiveRecordId()

"7499"

but if you aren't in edit mode it returns nothing, so instead I tried this...

apex.region("theIG").widget().interactiveGrid("getViews", "grid").getSelectedRecords()

seeing as we are in single row view mode, it gives you the current visible row

pastedImage_4.png

but that's positional (same with getContextRecord()) so if someone moves a column then it may break your code, so you could get the position from...

apex.region("theIG").widget().interactiveGrid("getViews", "grid").getColumns()

there maybe something more appropriate, have a look at what is available...(change "theIG" to your static ID)

apex.region("theIG").widget().interactiveGrid("getViews", "grid")

you can then use the primary key column value to access the model as before (i think)

EDIT : and please change you username to something more recognisable than "3052157"

Marked as Answer by John like many others · Sep 27 2020
John like many others

All your provided information were VERY helpful! All your assumptions where correct...

For other having the same problem:

- getActiveRecordId() delivers me only an ID if the record is in EDIT mode what I can't guarantee

- The user CAN switch the order of the columsn (allowed)

- Luckily getSelectedRecords([0]) provides the index (attribute 'index') in order to get the correct value in array of getColumns()

I check (again) my name but couldn't find/change it in profile.

Thanks a lot, very appreciated!

1 - 2

Post Details

Added on Dec 24 2018
4 comments
535 views