Re: Forms 4.5 - Block based on a View
Date: 1996/05/17
Message-ID: <319bd17b.658426_at_dcsun4>
rbrisson_at_centrum.dk (René Brisson) wrote:
>In article <4mhg9s$avp_at_news.mel.aone.net.au>, simonc_at_oakton.com.au (Simon) says:
>>
>>G'day all,
>>
>>I have 2 tables:
>>
>>table_a (table_a_id number, desc_a varchar2(10), table_b_id number )
>>table_b (table_b_id number, desc_b varchar2(10) )
>>
>>Originally I had a simple block based on table_a,
>>with a post_query trigger to look up table_b.desc_b based
>>on table_a.table_b_id.
>>i.e. A block based on table_a with columns for:
>> table_a_id, desc_a, desc_b
>>
>>The *User* now wants to have the block ordered by desc_b.
>>
>>Does anybody have ideas on a "nice" way to do this (I am sure someone has
>>had to do it before!)?
>>
>>
>>What I had in mind was to create a view:
>>CREATE VIEW TABLE_A_VIEW AS
>> SELECT TABLE_A_ID, DESC_A, DESC_B
>> FROM TABLE_A, TABLE_B
>> WHERE TABLE_A.TABLE_B_ID = TABLE_B.TABLE_B_ID;
>>
>>& then base my block on table_a_view. Of course this block wouldn't be
>>updateable, so I would have to replace the update, insert, delete functionality
>>manually by using the ON-INSERT, ON-UPDATE, ON-DELETE triggers.
>>
>>Then there is the problem of locking the record when the user trys to change it.
>>Forms 4.5 doco says to use the ON-LOCK trigger to "lock underlying tables
>>for non-updateable views" - but it doesn't say how!!
>>
to extrapolate on #2 below.....
What I would suggest is this (example shows updates being support against TABLE_A only, if you want TABLE_B you would add table_b's rowid into the view as well and do basically the same thing).
create view table_a_view as
select table_a_id, desc_a, desc_b, a.rowid a_rid
^^^^^^^^^^^^^
from table_a, table_b
where table_a.table_b_id = table_b.table_b_id;
Create the base table on the view, include the a_rid column in the base table block but make it hidden.
Then the on-lock trigger would look like:
ON-LOCK declare
dummy varchar2(5);
begin
select 'x'
into dummy
from table_a
where rowid = :block.a_rid
and a.table_a_id = :block.table_a_id
and a.desc_a = :block.desc_a
for update nowait;
exception
when no_data_found then
message( 'Record has been updated by another user, please requery' );
raise form_trigger_failure;
when others then
message( sqlerrm );
raise form_trigger_failure;
end;
The on-update would look like:
begin
update table_a
set table_a_id = :block.table_a_id,
a_desc = :block.a_desc
where rowid = :block.a_rid;
end;
The on-insert would look like:
begin
insert into table_a ( table_a_id, a_desc ) values ( :block.table_a_id, :block.a_desc );
- need to fill in the ROWID column so we can lock it later
- if the user decides to update the newly inserted data
select rowid into :block.a_rid from table_a where table_a_id = :block.table_a_id;
- The following syntax is wrong, i don't have an example nearby set_record_status( QUERIED ) end;
the on-delete would look like:
begin
delete from table_a where rowid = :block.a_rid; end;
>>
>>My question is this - if what I have outlined above is the appropriate
>>course of action (if not - what is a better one!), how to I simulate the
>>locking & then updating of records at appropriate times???
>>
>>
>>Any suggestions/comments etc appreciated.
>>
>>Thanks.
>>
>>-- Simon.
>>
>You'll have to deal with the locking using standard oracle locking mechanisms.
>
>There are two solutions:
>
>1. In your on-lock trigger you lock the table exclusive, which gives you
>a lock of all records in the table. (Code is lock table c in exclusive mode)
>Then your update-code would be a standard update using the keysof table_a and
>table_b.
>
>This solution is however not satisfacting if locking is a problem - and
>when is locking not a problem?
>
>2. The nice and best solution is to select .. for update of * in the two tables.
>you will have to create two non-visible char-fieldswith a length of at least 32
>chars. Then your on-lock trigger should do two selects where you lock the
>record in the two tables and save the rowid. (you dont need to save the rowid but
>its no big deal!) The selects could look like this
>
>select rowid from table_a where table_a_id=:block.table_a_id
>for update of *
>
>The on-update trigger should then update the tables using the rowids, the code
>should be something like update table_a set .... where rowid=:block.table_a_rowid.
>
>I hope this helps.
>
>Rene Brisson
>RBrisson_at_centrum.dk
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Fri May 17 1996 - 00:00:00 CEST