DBA Forms Dilemma

From: jared still <jkstill_at_teleport.com>
Date: 1998/02/18
Message-ID: <34ea43a4.240230063_at_news.teleport.com>#1/1

Perhaps some of you with Oracle Forms experience could help me out. We don't have the in-house experience here to solve a problem we have run into. The DBA's have no experience with Forms, and the developers don't have too much more experience than we do.

This is Forms 5.0, but this problem appears to be so basic that your if your comments are based on 4.5, they would probably be apropos.

The problem is this:

The tables look like this: ( somewhat simplified for clarity )

CONTRACT_TYPE: ( a lookup table with 5 rows )

	code
	description


RATES:
	id
	amount
	cntrct_type_cd ( fk to contract_type )

for every id in the RATES table, there are 5 contract types

for one set of RATES there will be 5 rows:

ID AMOUNT CNTRCT_TYPE_CD
-- ------ --------------

1   10.25   1
1   15.75   2
1   22.50   3
1   13.15   4
1   18.00   5

The users want to be able to view and update the data in forms in a spreadsheet format:

ID AMT_1 AMT_2 AMT_3 AMT_4 AMT_5

---

1 | 10.25 | 15.75 | 22.50 | 13.15 | 18.00

---

2 | 14.56 | 12.45 | 19.50 | 09.40 | 15.00

---

3 ...
4 ...

We have shown the developers how to code the view, and how to use PL/SQL to update the tables from bind variables, based on changes that would be made on the screen.

Our demo consisted of selecting from the view into a record, changing the values in the record, then using UPDATE with DECODE to update the tables. This was done in straight PL/SQL.

Apparently this does not work for the developers for some reason. I know it can be done, because I have seen it. Alas, I don't know much about the magic of Forms.

The developers solution is to eliminate the CONTRACT_TYPE table, and create 5 columns in RATES to represent each rate. Like so:

RATES:

	id
	amount
	amt_1
	amt_2
	amt_3
	amt_4
	amt_5

This allows them to do a Form without any SQL. ( that's what I'm told anyway )

Denormalizations are sometimes OK, and we have allowed several on tables where it has minimal impact on the design.

This particular table ( CONTRACT_TYPE ) holds some key information however, and at least one other system in development relies on its existence.

Your mission, should you choose to accept it, is to give us some guidelines to follow, some clues, ( some code ? ) so that we will be able to use to teach the developers how to do this without altering the database.

We have purchased a tome on Forms, and are willing to learn enough to learn how to do this ourselves, so that we will have a ready solution for this problem now and in the future.

If you respond, please send your message to one of the addresses in the signature as well as usenet if possible.

Thanks!

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;) Regence Blue Cross Blue Shield of Oregon jkstill_at_bcbso.com
jkstill_at_teleport.com Received on Wed Feb 18 1998 - 00:00:00 CET