Hi,
I have a procedure that will dynamically create a table. This procedure is executed via a job. After I click on the button that executes the job and I check the job logs, my job has failed and returned the error ORA-29477: SQL statement size in bytes is too big. I have checked that the query generated by my procedure is only around 900+ million characters and should be able to fit in the CLOB variable as it can hold 4 GB or 4 billion characters (Reference: https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/datatype-limits.html#GUID-963C79C9-9303-49FE-8F2D-C8AAF04D3095)..)
Below is the procedure's code:
PROCEDURE create_xy_chart_tbl (
p_in_proj_id IN NUMBER,
p_in_job_id IN NUMBER
) AS
l_scheduler_job_name VARCHAR2(100);
v_sql_create VARCHAR2(1000);
v_count NUMBER;
l_sql CLOB;
clob_len NUMBER;
BEGIN
l_sql := 'CREATE TABLE XY_CHART_TEST AS SELECT role_code, rule_code';
FOR cols_rec IN(SELECT ability_name
FROM (
SELECT ability_name_1 as ability_name
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866
AND rule_code LIKE 'SOD%'
UNION
SELECT ability_name_2 as ability_name
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866
AND rule_code LIKE 'SOD%'
)
WHERE ability_name IS NOT NULL
) LOOP
FOR rows_rec IN (SELECT role_code, rule_code
FROM (SELECT role_code, rule_code
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866
UNION
SELECT role_code, rule_code
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866)
where rule_code LIKE 'SOD%'
) LOOP
l_sql := l_sql
|| apex_string.format(
q'[, CASE WHEN EXISTS (
SELECT * FROM (
SELECT role_code, rule_code, ability_name_1 as ability_name
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866
UNION
SELECT role_code, rule_code, ability_name_2 as ability_name
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866
) WHERE role_code = '%0'
AND rule_code = '%3'
AND ability_name = '%5'
AND project_id = 9607
AND job_id = 20866
) THEN
CASE WHEN role_code = '%0' AND rule_code = '%3'
THEN 'X'
ELSE '-'
END
ELSE
CASE WHEN role_code = '%0' AND rule_code = '%3'
THEN 'N'
END
END AS "%5"]'
, rows_rec.role_code
, 9607
, 'p_page_name'
, rows_rec.rule_code
, SYS_GUID()
, cols_rec.ability_name
);
END LOOP;
END LOOP;
l_sql := l_sql
|| ' FROM (SELECT
role_code,
rule_code,
job_id,
project_id
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866
UNION
SELECT role_code,
rule_code,
job_id,
project_id
FROM SAT_TXN_INTRA_ROLE_VIOLATION_DETAILS_TBL
WHERE project_id = 9607
AND job_id = 20866) m WHERE project_id = '
|| to_clob(9607)
|| ' AND job_id = 20866'
|| ' AND rule_code LIKE ''SOD%'' ';
-- count length of generated query
dbms_output.put_line('length of query: '|| length(l_sql));
EXECUTE IMMEDIATE l_sql;
END create_xy_chart_tbl;
I do not understand why I get the error despite knowing that my query should be able to fit in the CLOB variable. Any ideas, suggestions or recommendations to resolve this is appreciated.