Re: HELP: Form with different db tables
Date: 19 Jun 1998 03:58:25 GMT
Message-ID: <1998061903582500.XAA03255_at_ladder01.news.aol.com>
>Using Oracle Forms 4.5
>
>We have a multitude of tables which all have the same schema. We would like
>to have one Oracle Form which prompts the user for a table name; and then
>pulls column information from the specified table.
>
>Can it be done? I appreciate any help!
>
>Thank you,
>Steve Chapin
>schapin_at_energygraphics.com
>
>
If by the same schema you mean the same table definition (column names and
data types),
there is a way to do it...
Step 1. Create a view that is union of all the tables you want to allow to be processed with the form. In the create view statement, include a new column that is a text string that identifies the table it came from (I called it
'source'). It might look something like this: select 'table 1' source,* from table_1 union select 'table 2',* from table_2 union select 'table 3',* from table_3 ...
(you can use the first select to give the columns alias)
Step 2. When you start your form, pass in a parameter that identifies the table you want to view.
Step 3. In the WHEN-NEW-FORM instance set the default where clause to use the passed-in parameter as a condition on the appropriate column in the view (on 'source' in my example)
Step 4. In the on-update, on-insert and on-delete triggers, you can either use DBMS_SQL calls to build a string to process the correct table, or a list of IF-THEN-ELSIF statements to pick the right table.
I don't know how big the tables are, or how many there are; somewhere along the line you might run into performance problems if you have lots of tables and lots of rows.
Good luck
Dan Hekimian-Williams Received on Fri Jun 19 1998 - 05:58:25 CEST