SQL & PL/SQL

Hi. I have a PL/SQL code that generates a SQL query and this SQL query will be used as the source code of my Classic Report in APEX. The problem is the generated SQL query is too long for my CLOB variable thus returns the error ORA-06502: PL/SQL: numeric or value error. (The cause of the “ORA-06502 PL/SQL numeric or value error”: A value is being assigned to a numeric variable, but the value is larger than what the variable can handle )

Any solution to generate the large query by bypassing the CLOB's limit? or doing other possible solution? Any suggestion is appreciated.

- Jazz

Code below:

DECLARE
        p_project_id NUMBER := 9607;
        p_job_id      NUMBER := 20764;
        p_group_name  VARCHAR2(100) := 'Group-1';
        p_page_name   VARCHAR2(100) := 'Job Details Page';
        l_sql        CLOB;
        l_role_count NUMBER;
        v_url        VARCHAR2(1000);
    BEGIN
        SELECT  
                count(*) INTO l_role_count
        FROM (
                SELECT
                    ability_name_1 AS ability_name
                FROM role_level_format_vw_1
                WHERE project_id = 9607
                    AND job_id = 20764
                
                UNION
                
                SELECT ability_name_2 AS ability_name
                FROM role_level_format_vw_2
                WHERE project_id = 9607
                    AND job_id = 20764);

        IF l_role_count = 0 THEN
            dbms_output.put_line('dummy X');
        END IF;

            l_sql := 'SELECT row_data, dos_data';
            FOR c IN (
                    SELECT row_data
                        FROM (
                            SELECT row_data
                            FROM DETAILS_TBL
                            WHERE project_id = 9607
                            AND job_id = 20764
                        UNION
                            SELECT row_data
                            FROM DETAILS_TBL
                            WHERE project_id = 9607
                            AND job_id = 20764)
                        WHERE ROWNUM < 20
                            
            ) LOOP
                FOR i IN(
                    SELECT dos_data
                        FROM (
                            SELECT  dos_data
                            FROM DETAILS_TBL
                            WHERE project_id = 9607
                            AND job_id = 20764
                        UNION
                            SELECT  dos_data
                            FROM DETAILS_TBL
                            WHERE project_id = 9607
                            AND job_id = 20764)
                    WHERE dos_data LIKE 'SOD%'
                    AND ROWNUM < 3
                ) LOOP

                l_sql := l_sql
                         || apex_string.format(
                                              q'[, CASE WHEN EXISTS (SELECT 1
                                                    FROM X_MARK_TBL
                                                    WHERE project_id = m.project_id
                                                    AND job_id = %1

                                                    AND ((row_data = ROLE_NAME_COLUMN AND '%0' = ROLE_NAME_ROW) 
                                                        OR ('%0' = ROLE_NAME_COLUMN AND row_data = ROLE_NAME_ROW))
                                                    OR (m.row_data, '%0') IN (SELECT ROLE_NAME_ROW, ROLE_NAME_COLUMN FROM X_MARK_TBL 
                                                                                WHERE job_id = %1
                                                                                )
                                                     ) THEN 
                                                            CASE 
                                                                WHEN row_data = '%0' 
                                                                THEN '<a href="' || APEX_PAGE.GET_URL (
                                                                        p_page => 52, 
                                                                        p_items => 'P52_JOB_ID,P52_row_data_Y,P52_row_data_X,P52_TRIGGER_DA,P52_PAGE_NAME', 
                                                                        p_values => '%1,'  ||',%0,'|| m.row_data || ',0' || ',%2'
                                                                    ) || '" style="background-color: #d1d1d1; display: block; width: 30px; height: 30px; text-align: center; display: flex; justify-content: center; align-items: center;">X</a>'
                                                                ELSE '<a href="' || APEX_PAGE.GET_URL (
                                                                        p_page => 52, 
                                                                        p_items => 'P52_JOB_ID,P52_row_data_Y,P52_row_data_X,P52_TRIGGER_DA,P52_PAGE_NAME', 
                                                                        p_values => '%1,'  ||',%0,'|| m.row_data || ',0' || ',%2'
                                                                    ) || '" style="display: block; width: 30px; height: 30px; text-align: center; display: flex; justify-content: center; align-items: center;">X</a>' 
                                                            END 
                                                        ELSE 
                                                            CASE 
                                                                WHEN row_data = '%0' 
                                                                THEN '<span style="background-color: #d1d1d1; display: block; width: 30px; height: 30px;"></span>'
                                                                    END 
                                                        END AS "%0"]'
                                              , c.row_data
                                              , p_job_id
                                              , p_page_name
                            );
                            END LOOP;
            END LOOP;

            l_sql := l_sql
                    || ' FROM (SELECT
                                            row_data,
                                            dos_data,
                                            job_id,
                                            project_id
                                        FROM DETAILS_TBL
                                        WHERE project_id = 9607
                                            AND job_id = 20764
                                        UNION
                                        SELECT row_data,
                                                dos_data,
                                                job_id,
                                                project_id
                                        FROM DETAILS_TBL
                                        WHERE project_id = 9607
                                            AND job_id = 20764) m WHERE project_id = '
                     || to_clob(9607)
                     || ' AND job_id = '
                     || to_clob(p_job_id)
                     || ' AND dos_data LIKE ''SOD%'' ';

                   htp.p(regexp_replace(
                                 l_sql
                                 , '\s+'
                                 , ' '
                   ));

    END;