Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Dynamic Content report cannot handle more than 5 pages giving "ORA-06502: PL/SQL: numeric or value error"

sharatsinghJul 19 2024 — edited Jul 19 2024

I am trying to use dynamic content report. It works fine with 5 pages. But beyond that, I am getting “ora_sqlerrm: ORA-06502: PL/SQL: numeric or value error”

Please help me out. My code is attached below:

DECLARE
    --
    v_school_batch_id  school_batch.id%TYPE;
    v_school_sector_id school_sector.id%TYPE;
    v_term_code        school_term.term_code%TYPE;
    v_student_id       std_class_sec.student_id%TYPE;
    v_report_type      school_report.report_type%TYPE;
    v_examination_type VARCHAR2(400);
    v_page_no          NUMBER := 1;
    v_total_page       NUMBER := 0;
    v_counter          NUMBER := 0;
    
    --
    ---cursors are written here.....
    --
    l_rep           CLOB := '<div id="report-container">';
BEGIN
    --
    v_school_batch_id := :p1565_school_batch_id;
    v_school_sector_id := :p1565_school_sector_id;
    v_term_code := :p1565_term_code;
    v_report_type := :p1565_report_type;
    v_student_id := TO_NUMBER ( :p1565_student_id );
    
    --
    FOR i IN cur_school_report_template(v_school_sector_id, v_term_code, v_report_type, v_student_id) LOOP
        
        --
        l_rep := l_rep || '<div class="single-page">';
        
        --
        l_rep := l_rep || '<div class="page-header">';
        l_rep := l_rep || '<span class="student-name">';
        l_rep := l_rep || '<h3 class="student-name"><span id="student-name">' || i.student_name || '</span><span id="student-id"> [ID#' || i.student_id || ']</span></h3>';

        l_rep := l_rep || '<span id="file-name">' || i.student_name || '_' || i.student_id || '</span>';

        l_rep := l_rep || '</span>'; --student-name
        l_rep := l_rep || '<span class="examination-name">';
        l_rep := l_rep || '<h3 class="class-name">' || i.class_name || '&mdash;' || i.exam_type || '</h3>';

        l_rep := l_rep || '</span>'; --examination-name
        l_rep := l_rep || '</div>'; --page-header
        
        --
        l_rep := l_rep || '<div class="page-content">';
        
        --
        l_rep := l_rep || '<div class="school-student-wrapper">';
        l_rep := l_rep || '<div class="logo-school-wrapper">';
        l_rep := l_rep || '<div class="school-logo">';
        --l_rep := l_rep || '<img class="school-logo-blank" src="' || :app_files || 'logo/school_logo_130x130.png">';
        l_rep := l_rep || '</div>';
        
        --
        l_rep := l_rep || '<div class="school-wrapper">';
        l_rep := l_rep || '<h3 class="report-heading">' || 'PROGRESS REPORT' || '</h3>';
        l_rep := l_rep || '<div class="school-name">' || i.school_name || '</div>';
        l_rep := l_rep || '<div class="school-address">' || i.school_address || '</div>';
        l_rep := l_rep || '<div class="examination-name">' || i.exam_type || '</div>';
        l_rep := l_rep || '</div>'; --school-wrapper
        l_rep := l_rep || '</div>'; --logo-school-wrapper
        
        --
        l_rep := l_rep || '<div class="student-wrapper">';
        l_rep := l_rep || '<div class="student-name">';
        l_rep := l_rep || '<span class="student-name-label">' || 'Student''s Name' || '</span>';
        l_rep := l_rep || '<span class="student-name-value">' || i.student_name || '</span>';
        l_rep := l_rep || '</div>'; --student-name
        
        --
        l_rep := l_rep || '<div class="class-name">';
        l_rep := l_rep || '<span class="class-name-label">' || 'Class' || '</span>';
        l_rep := l_rep || '<span class="class-name-value">' || i.class_name || '</span>';
        l_rep := l_rep || '</div>'; --class-name
        
        --
        l_rep := l_rep || '<div class="roll-name">';
        l_rep := l_rep || '<span class="roll-name-label">' || 'Roll Number' || '</span>';
        l_rep := l_rep || '<span class="roll-name-value">' || i.roll_no || '</span>';
        l_rep := l_rep || '</div>'; --roll-name
        
        
        --
        l_rep := l_rep || '</div>'; --student-wrapper

        l_rep := l_rep || '<div class="student-identity-wrapper">';
        l_rep := l_rep || '<div class="student-dob">';
        l_rep := l_rep || '<span class="student-dob">' || 'Date Of Birth' || '</span>';
        l_rep := l_rep || '<span class="student-dob-value">' || i.dob || '</span>';
        l_rep := l_rep || '</div>'; --student-dob
        
        l_rep := l_rep || '<div class="student-house">';
        l_rep := l_rep || '<span class="student-house">' || 'School''s House' || '</span>';
        l_rep := l_rep || '<span class="student-pen-value">' || i.house_name || '</span>';
        l_rep := l_rep || '</div>'; --student-house

        l_rep := l_rep || '<div class="student-id">';
        l_rep := l_rep || '<span class="student-id-label">' || 'Identity No' || '</span>';
        l_rep := l_rep || '<span class="student-id-value">' || i.student_id || '</span>';
        l_rep := l_rep || '</div>'; --student-id

        IF i.registration_id IS NOT NULL THEN
            l_rep := l_rep || '<div class="student-registration">';
            l_rep := l_rep || '<span class="student-registration-label">' || 'Registration No' || '</span>';
            l_rep := l_rep || '<span class="student-registration-value">' || i.registration_id || '</span>';
            l_rep := l_rep || '</div>'; --student-registration
        END IF;

        l_rep := l_rep || '</div>';  --student-identity-wrapper

        l_rep := l_rep || '</div>'; --school-student-wrapper
        
        --
        l_rep := l_rep || '<div class="physical-section">';
        l_rep := l_rep || '<h3 class="physical-section-heading">' || 'PHYSIQUE' || '</h3>';
        l_rep := l_rep || '<div class="physical-section-wrapper">';
        
        --
        l_rep := l_rep || '<div class="student-photo">';
        --l_rep := l_rep || '<img src="data:' || i.mime_type || ';base64, ' || apex_web_service.blob2clobbase64(i.image) || '">';
                --l_rep := l_rep || '<img class="school-logo-blank" src="' || :app_files || 'logo/school_logo_130x130.png">';

        l_rep := l_rep || '</div>';
        l_rep := l_rep || '<div class="student-physique">';
        l_rep := l_rep || '<table class="student-physique-table">';
        l_rep := l_rep || '<tr>';
        l_rep := l_rep || '<th colspan="2">' || 'MEASUREMENT' || '</th>';
        l_rep := l_rep || '</tr>';
        FOR t IN cur_std_physique(i.school_code, i.batch_no, i.class_code, i.stream_code, i.student_id) LOOP
            l_rep := l_rep || '<tr><th>HEIGHT</th><td>' || t.height || '</td></tr>';
            l_rep := l_rep || '<tr><th>WEIGHT</th><td>' || t.weight || '</td></tr>';
            l_rep := l_rep || '<tr><th>BMI</th><td>' || t.bmi_note || '</td></tr>';
            l_rep := l_rep || '<tr><th>' || 'SPECIAL TYPE' || '</th><td>' || t.special_type || '</td></tr>';
            l_rep := l_rep || '<tr><th>' || 'RECORDED ON' || '</th><td>' || t.recorded_on || '</td></tr>';
        END LOOP;

        l_rep := l_rep || '</table>'; ---student-physique-table

        l_rep := l_rep || '</div>'; --student-physique
        l_rep := l_rep || '</div>'; --physical-section-wrapper
        l_rep := l_rep || '</div>'; --physical-section
 
        --
        l_rep := l_rep || '<div class="overall-performance">';
        l_rep := l_rep || '<h3 class="performance-section-heading">' || 'OVERALL COMPETENCY' || '</h3>';
        l_rep := l_rep || '<ul class="overall-performance-ul">';
        FOR l IN cur_std_performance(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id) LOOP
            l_rep := l_rep || '<li class="overall-performance-li">';
            l_rep := l_rep || '<span class="learning-color-code" style="background-color: ' || l.color_hex || '"></span>';
            l_rep := l_rep || '<span class="learning-type-span">' || l.learning_type || '</span>';
            l_rep := l_rep || l.score_percentage;
            l_rep := l_rep || '</li>';
        END LOOP;

        l_rep := l_rep || '</ul>'; ---overall-performance-ul
        l_rep := l_rep || '</div>'; --overall-performance
        
        --
        l_rep := l_rep || '<div class="attendance-section">';
        l_rep := l_rep || '<h3 class="attendance-section-heading">' || 'ATTENDANCE' || '</h3>';
        
        --
        l_rep := l_rep || '<table class="attendance-table">';
        FOR t IN cur_std_attendance_th(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id) LOOP
            l_rep := l_rep || '<tr>'; 
            l_rep := l_rep || t.l_matrix_th; 
            l_rep := l_rep || '</tr>';
        END LOOP;

        FOR d IN cur_std_attendance_data(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id) LOOP
            l_rep := l_rep || '<tr>';
            l_rep := l_rep || '<td class="day-type">' || d.day_type || '</td>' || d.l_matrix_td;
            l_rep := l_rep || '</tr>';
        END LOOP;

        l_rep := l_rep || '</table>';
        l_rep := l_rep || '</div>'; --attendance-section
        l_rep := l_rep || '</div>'; --page-content
        
        --
        OPEN cur_total_page(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id);
        FETCH cur_total_page INTO v_total_page;
        CLOSE cur_total_page;
        
        --
        l_rep := l_rep || '<div class="page-footer">';
        l_rep := l_rep || '<span class="school-name">';
        l_rep := l_rep || '<h3 class="school-name">' || i.school_name || '</h3>';
        l_rep := l_rep || '</span>'; --school-name
        l_rep := l_rep || '<span class="page-name">';
        l_rep := l_rep || '<h3 class="page-name">' || 'Report ' || v_page_no || ' of ' || v_total_page || '</h3>';

        l_rep := l_rep || '</span>'; --page-name
        l_rep := l_rep || '</div>'; --page-footer
        
        --
        l_rep := l_rep || '</div>'; --single-page
        
        --
        FOR k IN 1..( 5 - 1 ) LOOP
               -- FOR k IN 1..( v_total_page - 1 ) LOOP

            l_rep := l_rep || '<div class="single-page">';
            
            --
            v_page_no := v_page_no + 1;
        
            --
            l_rep := l_rep || '<div class="page-header">';
            l_rep := l_rep || '<span class="student-name">';
            l_rep := l_rep || '<h3 class="student-name"><span class="student-name">' || i.student_name || '</span><span id="student-id"> [ID#'|| i.student_id || ']</span></h3>';

            l_rep := l_rep || '</span>'; --student-name
            l_rep := l_rep || '<span class="examination-name">';
            l_rep := l_rep || '<h3 class="class-name">' || i.class_name || '&mdash;' || i.exam_type || '</h3>';

            l_rep := l_rep || '</span>'; --examination-name
            l_rep := l_rep || '</div>'; --page-header
        
            --
            l_rep := l_rep || '<div class="page-content">';
            
            --
            FOR l IN cur_super_subject(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id, k) LOOP
                l_rep := l_rep || '<div class="subject-content">';
                l_rep := l_rep || '<h3 class="super-subject">' || l.super_name || '</h3>'; --super-name
                        
                        --
                l_rep := l_rep || '<div class="outcome-content">';
                l_rep := l_rep || '<h3 class="outcome-content-heading">' || 'LESSON OUTCOMES' || '</h3>';
                        
                        --
                l_rep := l_rep || '<ol class="ol-outcome-list">';
                FOR t IN cur_lesson_outcome(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, l.super_code) LOOP
                    l_rep := l_rep || '<li>' || t.lesson_outcome || '</li>';
                END LOOP;

                l_rep := l_rep || '</ol>'; --ol-outcome-list
                l_rep := l_rep || '</div>'; --outcome-content
                        
                --
                l_rep := l_rep || '<div class="metric-content">';
                l_rep := l_rep || '<h3 class="metric-content-heading">' || 'GENERAL COMPETENCY' || '</h3>';
                        
                --
                OPEN cur_examination_type(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id, l.super_code);

                FETCH cur_examination_type INTO v_examination_type;
                IF v_examination_type = 'EXTERNAL_INTERNAL'
                THEN
                    l_rep := l_rep || '<div class="b-internal-competency"><h3 class="assessment-content-heading">FORMATIVE ASSESSMENT</h3>';
                    
                    --
                    l_rep := l_rep || '<div class="visualized-chart">';
                    l_rep := l_rep || '</div>'; --visualized-chart
                        
                    --
                    l_rep := l_rep || '<div class="rubric-list">';
                    l_rep := l_rep || '<ul class="super-performance-ul">';
                    FOR t IN cur_std_performance(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id, l.super_code, 'INTERNAL') LOOP
                        l_rep := l_rep || '<li class="super-performance-li">';
                        l_rep := l_rep || '<span class="learning-color-code" style="background-color: ' || t.color_hex || '"></span>';
                        l_rep := l_rep || '<span class="learning-type-span">' || t.learning_type || '</span>';
                        l_rep := l_rep || t.score_percentage;
                        l_rep := l_rep || '</li>';
                    END LOOP;
                    l_rep := l_rep || '</ul>'; ---super-performance-ul
                    l_rep := l_rep || '</div>'; --rubric-list
                    l_rep := l_rep || '</div>'; --b-internal-competency
                    
                    --
                    l_rep := l_rep || '<div class="b-external-competency"><h3 class="assessment-content-heading">SUMMATIVE ASSESSMENT</h3>';

                    --
                    l_rep := l_rep || '<div class="visualized-chart">';
                    l_rep := l_rep || '</div>'; --visualized-chart
                        
                    --
                    l_rep := l_rep || '<div class="rubric-list">';
                    l_rep := l_rep || '<ul class="super-performance-ul">';
                    FOR t IN cur_std_performance(i.school_code, i.batch_no, i.class_code, i.stream_code, i.term_code, i.student_id, l.super_code, 'EXTERNAL') LOOP
                        l_rep := l_rep || '<li class="super-performance-li">';
                        l_rep := l_rep || '<span class="learning-color-code" style="background-color: ' || t.color_hex || '"></span>';
                        l_rep := l_rep || '<span class="learning-type-span">' || t.learning_type || '</span>';

                       
                        l_rep := l_rep || t.score_percentage;
                        l_rep := l_rep || '</li>';
                    END LOOP;
                    l_rep := l_rep || '</ul>'; ---super-performance-ul
                    l_rep := l_rep || '</div>'; --rubric-list
                    l_rep := l_rep || '</div>'; --b-external-competency

                END IF;

                IF v_examination_type = 'INTERNAL'
                THEN
                    l_rep := l_rep || '<div class="a-internal-competency"><h3 class="assessment-content-heading">FORMATIVE ASSESSMENT</h3>';
                    
                    --
                    l_rep := l_rep || '<div class="visualized-chart">';
                    l_rep := l_rep || '</div>'; --visualized-chart
                    
                    --
                    l_rep := l_rep || '<div class="rubric-list">';
                    l_rep := l_rep || '</div>'; --rubric-list
                    l_rep := l_rep || '</div>'; --a-internal-competency
                END IF;

                IF v_examination_type = 'EXTERNAL'
                THEN
                    l_rep := l_rep || '<div class="a-external-competency"><h3 class="assessment-content-heading">SUMMATIVE ASSESSMENT</h3>';
                    
                    --
                    l_rep := l_rep || '<div class="visualized-chart">';
                    l_rep := l_rep || '</div>'; --visualized-chart
                    
                    --
                    l_rep := l_rep || '<div class="rubric-list">';
                    l_rep := l_rep || '</div>'; --rubric-list
                    l_rep := l_rep || '</div>'; --a-external-competency
                END IF;

                CLOSE cur_examination_type;
                l_rep := l_rep || '</div>'; --graph-content
                    
                --
                l_rep := l_rep || '</div>'; --subject-content
            END LOOP;

            l_rep := l_rep || '</div>'; --page-content

            l_rep := l_rep || '<div class="page-footer">';
            l_rep := l_rep || '<span class="school-name">';
            l_rep := l_rep || '<h3 class="school-name">' || i.school_name || '</h3>';
            l_rep := l_rep || '</span>'; --school-name
            l_rep := l_rep || '<span class="page-name">';
            l_rep := l_rep || '<h3 class="page-name">' || 'Report ' || v_page_no || ' of ' || ( v_total_page ) || '</h3>';

            l_rep := l_rep || '</span>'; --page-name
            l_rep := l_rep || '</div>'; --page-footer
        
            --
            l_rep := l_rep || '</div>'; --single-page
        END LOOP; --single-page
    END LOOP; --cur_school_report_template
    
    --
    l_rep := l_rep || '</div>'; ---report-container
    RETURN l_rep;
END;

360_report_card.sql
This post has been answered by SmithJohn45 on Jul 20 2024
Jump to Answer

Comments

Post Details

Added on Jul 19 2024
#advancedreportinginapex, #dynamiccontent, #oracleapex
2 comments
215 views
1 mention