Re: Forms 3.0 Selecting multiple records into a block? Not Querying...
Date: 1997/05/13
Message-ID: <01bc5ff3$6ecc36a0$cc567ec2_at_cavett.globalnet.co.uk>#1/1
Hi Mark -
I also work at a site (still) running Forms v.3 and I guess we are not alone. I'll try and help you out with the following suggestions:
Option 1: A copy of the original table(s)
If you just want one original copy of the tables data then why not create a copy of the tables (including its contents) in SQL*Plus, before doing any table updates via Forms, for example for table1 (with similar statements for table2, table3):
CREATE TABLE orig_table1 AS (SELECT * FROM table1);
You then can update the 'live' table via Forms 3 or whatever, with a 'safe' copy of the original table data.
Option2: Record auditing
Reading between the lines of your message it sounds like you want to keep an audit trail or snapshot of the table data before it is updated by users. Is this correct?
If it is correct I suggest a simplified approach:
Since you have 3 tables (lets say table1, table2, table3) as your original tables. Create 3 corresponding audit tables (lets say audit_table1, audit_table2, audit_table3 with exactly the same table structures as their 'sources' plus perhaps some audit trial columns (eg. audit_user, audit_date, audit_action etc.)
As far as the forms processing goes: have 3 blocks with the base tables being tables table1, table2, table3 defined as multi-row blocks (see block definition screen). Create a form level procedure to insert an audit trail record - one for each of the tables (you could use a database trigger to do this) which is called from PRE-UPDATE (and PRE-DELETE if appropriate) block level trigger(s) - the unchanged record(s) will then be copied (before amendment) into the corresponding audit table.
You'll then have a complete audit history of the tables data changes (Of course you'll need to remove the primary key contraint from the audit table since you'll get a row created each time there is an amendment to the table data).
General point
To get all rows to display in a multi-row block you don't use SELECT INTO . . . type constructs to populate the rows - define the block's base table as one of your 3 tables then just hit the EXECUTE_QUERY key - use the 'default block definition' as a first cut design, specifying the no. of rows you what displayed and take it from there.
If you need further info. try my work email as I pick up mail more frequently there.
Regards
- Martyn Cavett Senior ORACLE Analyst LGT Asset Management London, UK. home: cavett_at_globalnet.co.uk work: Martyn.Cavett_at_gtplc.com
These views are my own and not those of my employer. Normal conditions apply.
Mark S Reichman <reichmanm_at_rl.af.mil> wrote in article
<5l9rdm$g6_at_news.rl.af.mil>...
> Otay...
>
> Yes we still use forms 3.0 and do not use froms 4.5. We are however
> converting to Designer/Developer 2000 progs soon. So... That aside..
> My question..
>
> I need to select multiple records into a block. Up to this point I have
> never had to do this. I do not want to query the records because I do
not
> want to destroy queried records with user commited updates.
> I plan to select records into 3 multiple record blocks from three tables
then
> insert the selected/user updated records into another 3 identical tables.
This
> is to preserve the original tables data for future use.
<snip>
Received on Tue May 13 1997 - 00:00:00 CEST