CODEWITHSUNDEEP
databaseoracle-databasedesigner
Brudas
Brudas

Reputation: 43

Custom query which returns to data block output in Oracle Forms Builder?

I have these tables

BOOKS

ID | GENREID(number) | COUNTRYID(number) |

GENRES

ID | TITLE

COUNTRIES

ID | TITLE

In my form BOOKS, I need to display:

ID GENERES.TITLE COUNTRIES.TITLE

All I need is to JOIN tables or SELECT data from all tables and display it. It seems that this is impossible with Oracle forms designer. My data block output displays data from the BOOKS table with inserted ids, but not with titles corresponding to those ids from separate tables (e.g. GENRES, COUNTRIES). I can't find where to put a custom query to data block.

I am using latest oracle dev suite home. How do I do this?

Upvotes: 1

Views: 6297

Answers (2)

JOTN
JOTN

Reputation: 6317

There's two common ways to do this:

  • Use a post-query trigger to populate non-database display fields and an LOV to allow them to insert and update those fields.

  • Create a view in the database and base your block on that.

The first one is the easiest but they won't be able to search on those non-database fields without a lot of extra work.

The second option allows search but adding and updating data in a view introduces some problems. I usually use an on-insert, on-update, on-delete, and on-lock trigger to handle those function manually and modify the base tables.

Upvotes: 2

Jokke Heikkilä
Jokke Heikkilä

Reputation: 928

http://sheikyerbouti.developpez.com/from_clause/from_clause.htm

--> Read the Step 3

I'm using that one a lot. With that you can build your own queries to Forms and also get block which you can insert/update/delete (of course to one table). And this is how I for example do block which has like user id and user name fields.

But at least in my Forms version it's not possible to change query in run time because it's changing data_source_type back to default.

Upvotes: 2

Related Questions