If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Oracle Forms 9i and Master-Detail Relationships

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 2
Post 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:

Code:
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) 
      REFERENCES esw_hdr_table(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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 2
Post RESOLVED

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.

Thanks to all those who looked over my post!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On