I am representing a Many-To-Many relationship by using a Popup dialog to save mappings. My tables look like the ones below.
So, we have an IG for the SAT_RESULT_SOURCE table and it uses a ListAgg to show the hosts that are currently mapped to it. There is a link allowing the end users to change these mappings.
SELECT RESULT_SOURCE_ID,
RESULT_SOURCE_SDLC_LEVEL_ID,
RESULT_SOURCE_SERVICE_NAME,
'<span class="controlImage fa fa-home fa-sm" aria-hidden="true"></span><span class="controlImage fa fa-database fa-sm" aria-hidden="true"></span>' AS HOST_LINK_ICON,
( SELECT ListAgg( RESULT_SOURCE_HOST_NAME, ', ' ) WITHIN GROUP (ORDER BY RESULT_SOURCE_HOST_NAME )
FROM SAT_RESULT_SOURCE_HOST
JOIN SAT_RESULT_SOURCE_HOST_MAP
ON( SAT_RESULT_SOURCE_HOST.RESULT_SOURCE_HOST_ID = SAT_RESULT_SOURCE_HOST_MAP.RESULT_SOURCE_HOST_ID )
WHERE SAT_RESULT_SOURCE_HOST_MAP.RESULT_SOURCE_ID = SAT_RESULT_SOURCE.RESULT_SOURCE_ID ) AS HOSTS,
CONTAINS_SAT_REPOSITORY
FROM SAT_RESULT_SOURCE
That link pops up a Modal Dialog and passes in the selected "Database", i.e. RESULT_SOURCE_ID.
The RESULT_SOURCE_ID is, in turn, used in the source query on the popup.
SELECT RESULT_SOURCE_ID,
RESULT_SOURCE_HOST_ID
FROM SAT_RESULT_SOURCE_HOST_MAP
WHERE RESULT_SOURCE_ID = :P15_RESULT_SOURCE_ID
So, there are two columns involved, as with the SAT_RESULT_HOST_MAP table shown in the ER-Diagram snippet above; the ID of the Result Source, aka Database and the ID of the mapped Host.
I have the settings for the RESULT_SOURCE_ID column as:
Type: Hidden
Query Only: Off
Primary Key: On
I have the settings for the RESULT_SOURCE_HOST_ID column as:
Type: Select List
Query Only: Off
Primary Key: On
The LOV used for the RESULT_SOURCE_HOST_ID column is simply:
Return: RESULT_SOURCE_HOST_ID
Display: RESULT_SOURCE_HOST_NAME
etc.
The processing is just the built-in type "Interactive Grid - Automatic Row Processing (DML)" without any modification whatsoever.
When we attempt to change the existing host by choosing a different host in the Select List, we get the ORA-0174 error. Usually this means we're using some sort of reserved word but, clearly looking at the data model above, we are not doing this.
The debug log shows it failing here:
Hiding error additional_info, as it contains ORA error message: ORA-01747: invalid user.table.column, table.column, or column specification
...
- error_statement: update (SELECT RESULT_SOURCE_ID,
RESULT_SOURCE_HOST_ID
FROM SAT_RESULT_SOURCE_HOST_MAP
WHERE RESULT_SOURCE_ID = :P15_RESULT_SOURCE_ID
)
set
where "RESULT_SOURCE_ID"=:APEX$PK1 and"RESULT_SOURCE_HOST_ID"=:APEX$PK2
When we attempt to insert a new row, we get the ORA-02291 error. This error makes no sense since it is an LOV that is literally supplying the Host ID value from a live SELECT.
The debug log shows it failing here:
parsing generated DML statement: insert into (SELECT RESULT_SOURCE_ID,
RESULT_SOURCE_HOST_ID
FROM SAT_RESULT_SOURCE_HOST_MAP
WHERE RESULT_SOURCE_ID = :P15_RESULT_SOURCE_ID
)
("RESULT_SOURCE_ID","RESULT_SOURCE_HOST_ID")values(:APEX$VAL1,:APEX$VAL2)returning "RESULT_SOURCE_ID","RESULT_SOURCE_HOST_ID" into :APEX$RET1,:APEX$RET2
...
Exception in "do_execute_dml, row 1":
Error Stack: ORA-02291: integrity constraint (DB_SAT.SAT_RESULT_SOURCE_HOST_FK) violated - parent key not found
ORA-06512: at "APEX_200200.WWV_FLOW_DYNAMIC_EXEC", line 1182
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_200200", line 685
ORA-06512: at "APEX_200200.WWV_FLOW_DYNAMIC_EXEC", line 1177
Backtrace: ORA-06512: at "APEX_200200.WWV_FLOW_DYNAMIC_EXEC", line 1182
ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_200200", line 685
ORA-06512: at "APEX_200200.WWV_FLOW_DYNAMIC_EXEC", line 1177
ORA-06512: at "APEX_200200.WWV_FLOW_EXEC_LOCAL", line 2609
...
- error_statement: (SELECT RESULT_SOURCE_ID,
RESULT_SOURCE_HOST_ID
FROM SAT_RESULT_SOURCE_HOST_MAP
WHERE RESULT_SOURCE_ID = :P15_RESULT_SOURCE_ID
)
I don't feel like we're doing anything unusual. Anyone have any idea what's wrong or how to make it work?
Thanks,
-Joe