Re: HELP: Forms 4.5 - Multiple tables

Hi Steve,

I've posted this again as I can't see any sign of my earlier answer in the newsgroup so it may have dropped out of the psotamns bag into the virtual bit bucket :-).

As Dan Hekimian-Williams says you can of course have a Union view but then all the Forms Insert, update etc functionality has to be replicated by yourself.

But if you are not on Ora 8 then it would certainly be better than populating a control block. It may depend on your table sizes as to how resource hungry the union view would be unless you are using 7.3 with the implicit optimisation of partition views, and you might have to consider the fact that the explicit optimiser support for partition views will be desupported in future releases.

regards

Rod

Original post

In Forms 4.5 the table is not a changeable block characteristic but you could have a control block and populate it yourself dynamically. This of course means that you also have to handle all the transactional work that Forms would normally handle for you, locks, inserts, updates etc. If you were in Forms 5 you could base a block on a PLSQL function but you would still have to handle all the transactions yourself.

I presume that you don't want to have umpteen forms exactly the same other than the table block characteristic and call-form them according to the user entered value.

It sounds as if you either have a series of local tables perhaps each representing part of a data set, eg one years sales or perhaps a distributed set with each table being say a regions worth of data.

You don't say which version of the database you are using, if you are using Version 8 is there any mileage in considering setting the tables up as partitions of a single table with a partition key equal to a table id.

In this way your form could address each partition depending on the user-entered value, one form - no problem. Existing applications looking at the present discrete tables would look at the partitioned table via a view with the same name as the original discrete table but looking only at its relevant partition, so there would be no code changes. The distribution of the partitions could be the same as the data distribution of the existing tables so potentially the final physical distribution could be the same but Oracle 8 handling the partition management.

You might have issues of distribution of the partitions and potential performance problems for existing applications if the existing tables are remote on database links, but might be worth considering.

Regards

Rod Corderey
Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

schapin_at_my-dejanews.com wrote:
>
> Using Oracle Forms 4.5
>
> We have a multitude of tables which all have the same table definition
> (column names and data types). 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.
>
> So, the form will perform just like any other form, but the display will refer
> to the user-entered table. All querys, updates will affect the user-entered
> table.
>
> Can it be done? I appreciate any help!
>
> Thank you,
> Steve Chapin
> schapin_at_energygraphics.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading

schapin_at_my-dejanews.com wrote:
>
> Using Oracle Forms 4.5
>
> We have a multitude of tables which all have the same table definition
> (column names and data types). 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.
>
> So, the form will perform just like any other form, but the display will refer
> to the user-entered table. All querys, updates will affect the user-entered
> table.
>
> Can it be done? I appreciate any help!
>
> Thank you,
> Steve Chapin
> schapin_at_energygraphics.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Sat Jun 27 1998 - 12:34:56 CEST