Unanswered: Oracle Forms 9i and Master-Detail Relationships
I am trying to learn Oracle Forms (with Forms Version 9i against a 9i database), but have hit a brick wall with a Master-Detail relation.
I have created a simple header table and a simple detail table. Both tables have surrogate keys, and the detail table has a column for the surrogate of the relevant header table record. This column has a foreign key constraint. Here are the relevant portions of the table creation scripts:
CREATE TABLE header
hdr_no VARCHAR2(20) NOT NULL,
hdr_id NUMBER NOT NULL,
CONSTRAINT header_pk1 PRIMARY KEY (hdr_no),
CONSTRAINT header_uk1 UNIQUE (hdr_id) USING INDEX
CREATE TABLE detail
dtl_hdr_id NUMBER NOT NULL,
dtl_no VARCHAR2(2) NOT NULL,
dtl_id NUMBER NOT NULL,
--some additional columns...
CONSTRAINT detail_pk1 PRIMARY KEY (dtl_hdr_id, dtl_no),
CONSTRAINT detail_uk1 UNIQUE (dtl_id) USING INDEX,
CONSTRAINT detail_fk1 FOREIGN KEY (dtl_hdr_id)
I created these two tables to try my hand at a simple master-detail form. I created a data block for each table (using the relevant table as the data source) using the wizard, which also walked me through creating the relation (a master detail relation, which is attached to the data block for the header). I then proceeded to create my window and canvas and to arrange the fields I wanted displayed.
The resulting form works fine for querying and updating data. I can also
(1) Create a header without any details, and
(2) Create new details under an existing header.
To make sure inserts work properly, I have PRE-INSERT triggers that populate the foreign keys.
However, the form will not allow me to create and save both a new header and new details for that header at the same time. When I attempt this, I get FRM-40508. The underlying Oracle error message is
ORA-01400: cannot insert NULL into ("user"."DETAIL"."DTL_HDR_ID")
As far as I can tell, the only reason Forms is trying to insert NULL ino DTL_HDR_ID, the foreign key column, is that it didn't save the master block first. In my PRE-INSERT trigger for the detail, I explicitly set the DTL_HDR_ID field using the HDR_ID field from the master block, so it should be non-NULL if the master is inserted first.
Furthermore, I at one point added code to PRE-INSERT trigger for the detail to set the HDR_ID field for the master block should it be NULL (and made corresponding changes to the PRE-INSERT trigger for the master block to skip the population of the HDR_ID field if it was non-NULL). The result there was a foreign key violation, again suggesting that the Forms is performing the inserts for the detail block before those for the master block.
I am pretty sure I could man-handle the form into working properly by manually coding the DDL into the PRE-INSERT trigger of the detail block and the ON-INSERT trigger of the header block (with the appropriate checks to avoid duplicate operations), but this would seem to run counter to the point of using forms in the first place.
Is there a simple way to have the form allow the user to save a new master and one or more details for it without having to save the two separately? Any advice on this would be greatly appreciated!
I found out what my problem was. Forms commits the data blocks in the order in which they appear in the GUI object navigator. I had the detail block listed before the master block, so Forms tried to perform the insertions for that block first. By putting the master block before the detail block on the GUI object navigator, I convinced Forms to perform the insertions in the right order.