My Oracle Support Banner

How to create Primary detail forms based on PL/SQL Proc. with automatic PK (Doc ID 146683.1)

Last updated on JUNE 21, 2021

Applies to:

Oracle Designer - Version 6.5 and later
Information in this document applies to any platform.


 NOTE: In the examples below user details represent a fictitious sample. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Consider a Primary Detail Forms module based on DEPT/EMP for example.
A Foreign Key Constraint is defined between Primary and Detail Table. This is

- MCDEPT is the module component based on table DEPT,
- MCEMP is the module component based on table EMP,
- property 'Datasource Type' is set to table for both MCDEPT and MCEMP,
- property 'Datatarget Type' is set to PL/SQL Procedure for both MCDEPT and MCEMP,
- MCEMP.DEPTNO 'Query ?', 'Insert ?' and 'Update ?' properties are set to No.

Primary Key Columns of both DEPT and EMP tables are filled by a sequence:
- DEPT.DEPTNO is filled by a sequence called SEQ_DEPT,
- EMP.EMPNO is filled by a sequence called SEQ_DEPT.
Sequences (respectively SEQ_DEPT and SEQ_EMP) are set in the property 'Sequence'
of the column definition (respectively DEPT.DEPTNO and EMP.EMPNO).

Once the module definition is copmlete, generate tables DEPT and EMP,
the Table API for DEPT and EMP, the Module Component API for the Forms module
and then the Forms Module itself.
The Module Component API is necessary in this case because the Datatarget Type
is set to PL/SQL and not to Table.

Run the module and insert a Primary record by filling DNAME and LOC.
Then go straight forward and insert a detail record (without saving in between):
enter at least a value for ENAME. Commit the whole transaction.

Expected behavior is that the MCDEPT.DEPTNO is filled automatically by a
sequence number and MCEMP.DEPTNO is filled with the same value as MCDEPT.DEPTNO,
even if MCEMP.DEPTNO is not visible. However, this is not the case. You get
the following error message instead:

API Error: Value for Deptno is required


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.