drack@titan.tsd.arlut.utexas.edu (Dave Rackley) (10/17/90)
While experimenting with SQL*Forms v3.0, I came across what appears to be a
limitation or bug. The situation follows:
-- A master table with 10 fields.
-- Two detail tables with 2 fields each.
-- The master and detail tables share a common key field.
-- For each key value one detail table has approximately 10 times
the records the second detail table.
The questions: Is SQL*Forms v3.0 limited to one master, one detail relation;
if not, what is the secret incantation (trigger) that allows
retrieval and display of the forms data?
The real question is: Has anyone else experienced this shortcoming?
Thanks in advance.
--
DISCLAIMER? I don't know anything 'bout any ol' disclaimer!
+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+
| David Rackley | |
| Applied Research Laboratories | Remember, you can tuna fish, |
| The University of Texas | But you can't tuna piano! |
| Austin, TX. 78758 | |
+=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=+rad@genco.uucp (Bob Daniel) (10/18/90)
In article <DRACK.90Oct17081418@diablo.titan.tsd.arlut.utexas.edu> drack@titan.tsd.arlut.utexas.edu (Dave Rackley) writes: >The questions: Is SQL*Forms v3.0 limited to one master, one detail relation; > if not, what is the secret incantation (trigger) that allows > retrieval and display of the forms data? One CAN have more than one master-detail relation but have to define the join condition under DEFAULT block setup. 1) First define the master block. 2) Then define the detail block using DEFAULT and use a join condition on a keyed field in both tables. 3) To apply another detail block, repeat the second step.
kbittner@oracle.uucp (Kurt Bittner) (10/18/90)
In article <DRACK.90Oct17081418@diablo.titan.tsd.arlut.utexas.edu> drack@titan.tsd.arlut.utexas.edu (Dave Rackley) writes: > > [stuff deleted] > >The questions: Is SQL*Forms v3.0 limited to one master, one detail relation; > if not, what is the secret incantation (trigger) that allows > retrieval and display of the forms data? > The easy way to do this is to: 1) Use the CONSTRAINT capability provided in the Oracle RDBMS to define the primary key/foreign key relationships when you create the tables. It is also a good idea to code CHECK constraints into the table definitions since Forms 3 can use these to automatically generate validation triggers if you select "Use Constraints" on the default block screen. 2) Use the Block Default option to define your blocks. When you define the "detail" block, indicate that the "master" block is the master block. If you press "List of Values" while in the "master block" field, you'll get a pop-up menu of all valid master block choices based on blocks already defined having a foreign key reference in your current table. Selecting the master block will define the relationship automatically. 3) Generate the form. That's it. (Of course you can always do all the work manually; if you want to see what needs to get done, look at all the triggers forms generated for the default block.) Kurt Bittner "My opinions are, humbly, my own and are not likely to Consultant be shared by anyone, let alone my company." Oracle Corporation
broy@vaxa.weeg.uiowa.edu (Barbara Roy) (10/19/90)
In article <1990Oct18.152751.11893@oracle.com>, kbittner@oracle.uucp (Kurt Bittner) writes... > > 1) Use the CONSTRAINT capability provided in the Oracle RDBMS to define the > primary key/foreign key relationships when you create the tables. > It is also a good idea to code CHECK constraints into the table definitions > since Forms 3 can use these to automatically generate validation triggers > if you select "Use Constraints" on the default block screen. Oracle documentation and Oracle class instructors indicate that CONSTRAINT definitions are NOT supported until version 7. Does SQL*FORMS 3.0 use them while the RDBMS does not? > 2) Use the Block Default option to define your blocks. When you define the > "detail" block, indicate that the "master" block is the master block. > If you press "List of Values" while in the "master block" field, you'll > get a pop-up menu of all valid master block choices based on blocks > already defined having a foreign key reference in your current table. > Selecting the master block will define the relationship automatically. This works fine for the first detail block, but I have had to manually create the appropriate triggers for all subsequent detail blocks of the same master. I haven't tried using your CONSTRAINT suggestion. Is that what corrects the problem? If so, why didn't tech support clue me in (ref TAR# 99530.41). I was told it was a bug that would be corrected in the next release. There is also a problem that if you delete all blocks in the form, the clear_details and query_details procs aren't deleted. Then when those blocks are added back to the form, the procs aren't re-created. > 3) Generate the form. > >That's it. (Of course you can always do all the work manually; if you want to >see what needs to get done, look at all the triggers forms generated for the >default block.) > >Kurt Bittner "My opinions are, humbly, my own and are not likely to >Consultant be shared by anyone, let alone my company." >Oracle Corporation Barbara Roy, Weeg Computing Center, University of Iowa, Iowa City, IA. 52242 Phone: 319-335-5506 Internet: broy@vaxa.weeg.uiowa.edu Bitnet: broyva@uiamvs Disclaimer: My opinions are solely my own and may change daily.
kbittner@oracle.uucp (Kurt Bittner) (10/22/90)
In article <2797@ns-mx.uiowa.edu> broy@vaxa.weeg.uiowa.edu writes: >In article <1990Oct18.152751.11893@oracle.com>, kbittner@oracle.uucp (Kurt Bittner) writes... >> >> 1) Use the CONSTRAINT capability provided in the Oracle RDBMS to define the >> primary key/foreign key relationships when you create the tables. >> It is also a good idea to code CHECK constraints into the table definitions >> since Forms 3 can use these to automatically generate validation triggers >> if you select "Use Constraints" on the default block screen. > >Oracle documentation and Oracle class instructors indicate that CONSTRAINT >definitions are NOT supported until version 7. >Does SQL*FORMS 3.0 use them while the RDBMS does not? Yes. It's a good idea to start using them now, since the V6 database will accept the CONSTRAINT syntax. Then when database enforcement is turmed on in V7, you'll be prepared. The issue of "supported" has more to do with the fact that the constraint defs don't do anything in the rdbms right now. Note that not only are the constraints used by SF3, but DEFAULTS and CHECK constraints are used to generate triggers. > >> 2) Use the Block Default option to define your blocks. When you define the >> "detail" block, indicate that the "master" block is the master block. >> If you press "List of Values" while in the "master block" field, you'll >> get a pop-up menu of all valid master block choices based on blocks >> already defined having a foreign key reference in your current table. >> Selecting the master block will define the relationship automatically. > >This works fine for the first detail block, but I have had to manually create >the appropriate triggers for all subsequent detail blocks of the same master. >I haven't tried using your CONSTRAINT suggestion. Is that what corrects the >problem? It won't handle this, since the default form generator creates triggers called 'clear_details', etc. A second detail block for the same master would over- write these triggers, so this isn't done. One way of handling this would be to: (1) create a default form for the first master-detail relationship (2) create a second default form for the second master-detail relationship (consisting of only the master block and the second detail block) (3) use the copy/reference object facility to copy the block defs, triggers, etc. from the first form into the second form. Be sure to use different names to prevent over-writing. (4) Edit the triggers you want to enable for both blocks so that things like KEY-UP in the master block will coordinate both detail blocks. This isn't perfect, but is better than nothing, and certainly is better than forms v2. >If so, why didn't tech support clue me in (ref TAR# 99530.41). I was >told it was a bug that would be corrected in the next release. This may be a better answer. >There is also a problem that if you delete all blocks in the form, the >clear_details and query_details procs aren't deleted. Then when those blocks >are added back to the form, the procs aren't re-created. Correct. You have to remember to manually delete the triggers which do block coord. Kurt Bittner "My opinions are, humbly, my own and are not likely to Consultant be shared by anyone, let alone my company." Oracle Corporation