Tuesday, September 05, 2006

Updateable view

Although this feature already exist a long time in Oracle, I'd like to share my experience using "updateable views" together with APEX.

My situation
I've a table "Teams", a table "Team Groups" and a table "Team Team Groups" (which teams are in which groups - many/many relationship).

While I was inputting the Champions League teams I had to
1. input into table Teams
2. input into table Team Team Groups

To speed up the inserts in an APEX application, you wouldn't create two insert pages (one per table), but only one page which handles the inserts for you. You can for ex. write some (APEX form) processing which will insert the data into the two tables or tell all items to which table they belong and adapt the standard processing.

I didn't use any of these, I did the insert through a view... I built an APEX tabular form on top of the view and had my GUI very quick and easy!

An updateable view exists of a regular view and an "instead of" trigger...

My view
SELECT t.team_id, t.team_name, t.image_id, t.team_comment, t.is_active, g.team_group_id
FROM tdg_team t, tdg_team_team_group g
WHERE t.team_id = g.team_id;

My trigger
CREATE OR REPLACE TRIGGER tdg_vw_teams_groups_ioi_trg
INSTEAD OF INSERT ON tdg_vw_teams_groups
DECLARE
BEGIN
INSERT INTO tdg_team
(team_id, team_name, image_id, team_comment, is_active)
VALUES (:NEW.team_id, :NEW.team_name, :NEW.image_id, :NEW.team_comment, :NEW.is_active);
INSERT INTO tdg_team_team_group
(team_id, team_group_id)
VALUES (:NEW.team_id, :NEW.team_group_id);
END tdg_vw_teams_groups_ioi_trg;

Almost the same for Update and Delete...

To see if the view is really updateable, run this statement
SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'TDG_VW_TEAMS_GROUPS';

My insert statement
INSERT INTO tdg_vw_teams_groups
(team_id, team_name, team_comment, image_id, is_active, team_group_id)
VALUES (tdg_team_seq.NEXTVAL, 'Shakhtar', NULL, NULL, 1, 1011);

I found it very useful to speed up my inserts like this and this solution also works from SQL Plus, SQL Developer, etc.

7 comments:

Anonymous said...

Hi,
It's an interesting "view" of the problem, but what about handling exceptions in appex? For instance, a user inserts an invalid date into field. How do you pass the exception text into appex page?

Paulo

Anonymous said...

Hi...
I am also concerned about the problem that Paulo talked about. Can you answer us ?

Fakada

Dimitri Gielis said...

Well, good question! (and sorry for the late reply)
In fact, I made the APEX page you "couldn't" make mistakes (use of dropdownbox etc.)... but I changed it and tested it for you.

I don't have an exception handler in my triggers (updateable view), so what hapenned when I put a too large number in a field? APEX gave me an error:
--
Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01438: value larger than specified precision allows for this column, update "DGIELIS"."TDG_VW_TEAMS_GROUPS" set "TEAM_ID" = :b1
Error Unable to process update
--

Then I built some exception handling in my trigger for ex.:
EXCEPTION
WHEN OTHERS THEN
raise_application_error (-20001,'Dimi');

But this error didn't come up in APEX because my error didn't get raised (he didn't come into the trigger, as Oracle sees it before).

So, to force it, I gave a "normal" value and did a Raise of a manual created exception in my trigger.

Then the error of APEX returned my exception:
--
Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: Dimi
...
--

I suppose it would be nicer if the error is the one you specify in "Process Error Message" in an APEX Process. However, how you do this, I didn't investigate.

Anonymous said...

u can use a oracle table function and write to any number of tables.

Anonymous said...

Great post!
thank you so much Dimitri, your post has lighten my day :D

Οδυσσέας said...

Which fields did you use as primary keys when creating the view? Using ROWID doesn't work...

BalazsB said...

Is this way still working with Apex 5.1?
Thx in advance !