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:

Symptoms

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
called EMP_FOREIGN_KEY: EMP.DEPTNO points to DEPT.DEPTNO.

- 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

Cause