Re: HELP: Form with different db tables

From: DanHW <danhw_at_aol.com>
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

Original text of this message