Hi all,
seems there is a restriction for the source code length when process update on IG? Couldn't reproduce it with APEX 18, but occours in 19.2 and 20.1., maybe since you can define source type "Table / View" instead of SQL Query?
I get "ORA-06502: PL/SQL: numeric or value error: associative array key violates its type constraints":
Steps:
Create an editable interactive grid, source type Table/View, add a column, Type Display Only with source SQL Expression and some long inner select (more than 256 char), something like:
nvl((
SELECT anz
FROM(
SELECT e.deptno d\_deptno, COUNT(e.empno) anz
FROM eba\_demo\_ig\_emp e
WHERE EXISTS(
SELECT NULL
FROM eba\_demo\_ig\_emp t
WHERE t.empno = e.empno
AND t.job = 'SALESMAN'
)
GROUP BY e.deptno
)
WHERE d\_deptno = deptno
), 0)
IG Source: Table EBA_DEMO_IG_DEPT (from Sample Interactive Grids App)
or Query:
select ROWID,
DEPTNO,
DNAME,
LOC,
NOTES
from EBA_DEMO_IG_DEPT
Then do some edit and try to save, you shoud get an error with:
Error Stack: ORA-06502: PL/SQL: numeric or value error: associative array key violates its type constraints
Backtrace: ORA-06512: at "APEX_200100.WWV_FLOW_INTERACTIVE_GRID", line 8204
Exception in "IG source table with error - Interaktive Grid-Daten speichern":
Error Stack: ORA-06502: PL/SQL: numeric or value error: associative array key violates its type constraints
ORA-06512: at "APEX_200100.WWV_FLOW_INTERACTIVE_GRID", line 8368
ORA-06512: at "APEX_200100.WWV_FLOW_INTERACTIVE_GRID", line 8204
ORA-06512: at "APEX_200100.WWV_FLOW_INTERACTIVE_GRID", line 8391
ORA-06512: at "APEX_200100.WWV_FLOW_PROCESS_NATIVE", line 1179
ORA-06512: at "APEX_200100.WWV_FLOW_PLUGIN", line 2586
Backtrace: ORA-06512: at "APEX_200100.WWV_FLOW_INTERACTIVE_GRID", line 8368
ORA-06512: at "APEX_200100.WWV_FLOW_INTERACTIVE_GRID", line 8204
ORA-06512: at "APEX_200100.WWV_FLOW_INTERACTIVE_GRID", line 8391
ORA-06512: at "APEX_200100.WWV_FLOW_PROCESS_NATIVE", line 1179
ORA-06512: at "APEX_200100.WWV_FLOW_PLUGIN", line 2586
ORA-06512: at "APEX_200100.WWV_FLOW_PROCESS", line 203
Workaround: include column in query and select "query only" or minify inline select to <256 chars.
The IG on page "IG source SQL no error" with this Source SQL works fine:
select ROWID,
DEPTNO,
DNAME,
LOC,
NOTES,
nvl((
SELECT anz
FROM(
SELECT e.deptno d\_deptno, COUNT(e.empno) anz
FROM eba\_demo\_ig\_emp e
WHERE EXISTS(
SELECT NULL
FROM eba\_demo\_ig\_emp t
WHERE t.empno = e.empno
AND t.job = 'SALESMAN'
)
GROUP BY e.deptno
)
WHERE d\_deptno = deptno
), 0) testcol
from EBA_DEMO_IG_DEPT
see:
https://apex.oracle.com/pls/apex/wstestlukas/r/test-ig1
user: demo
pw: FCKCRN2020
I think there is a problem using an inner selects and some issue with the alias when creating the update statement in that process, or what do you think?
Thanks,
Lukas