Hi,
In Oracle 21.1.7,
I have a dynamic insert sql that uses the apex_string.format() function. I do the Execute Immediate on it, it gets processed for a while then says 1 rows inserted but after checking the table it is inserting into, there is no data found. I am also quite sure that the tables used in the query generated by dynamic sql has data in it as per several testing.
DECLARE
l_sql CLOB;
CURSOR c_group
IS SELECT DISTINCT group_name
FROM TABLE_1
WHERE rownum = 1 ;
BEGIN
FOR r_group IN c_group
LOOP
FOR c IN (
SELECT role_code
FROM TABLE_1
WHERE ROWNUM = 1
) LOOP
EXECUTE IMMEDIATE (apex_string.format(
q'[
INSERT INTO SAT_XY_TBL (job_id, group_name, role_name_row, role_name_column)
SELECT %1, '%3', role_code, '%0' FROM
(SELECT role_code, CASE WHEN EXISTS (SELECT 1
FROM TABLE_2
WHERE project_id = m.project_id
AND job_id = %1
AND group_name = m.group_name
AND role_code IN ( job_role_code_1
,job_role_code_2)
AND ((job_role_code_1 = '%0' AND job_role_code_1 != role_code)
OR (job_role_code_2 = '%0' AND job_role_code_2 != role_code))
OR (m.role_code, '%0') IN (SELECT job_role_code_1,job_role_code_2 FROM TABLE_2
WHERE job_id = %1
AND group_name = m.group_name)
) THEN
'
<a href="' ||
APEX_PAGE.GET_URL (
p_page => 104,
p_items => 'P104_JOB_ID,P104_GROUP_NAME,P104_ROLE_CODE_Y,P104_ROLE_CODE_X,P104_TRIGGER_DA',
p_values => '%1,' || m.group_name ||',%0,'|| m.role_code || ',0'
)
||
'">X</a>'
END AS %0 FROM TABLE_1 m WHERE project_id = %2 AND group_name = '%3' AND job_id = %1)
WHERE %0 = 'X'
]'
, c.role_code
, 2224
, 1082
, r_group.group_name
));
END LOOP;
END LOOP;
END;
I have tried to use simple Insert query in the dynamic sql without using apex_string.format and it works well so I think the issue on how to handle Execute Immediate on apex_string.format.
Any idea or suggestion is appreciated,
Jazz