APEX

Hello,

I have a classic report that shows data in a special way: each column appears stacked on top of each other. To achieve this, I'm using a template called "value attribute pairs – column."

I also needed to hide some columns based on certain conditions. Here's how I did it:

1. In the column formatting section, I used this code to hide the "LESSON" column if a condition is met:

{if LESSON_INCLUDE/} #LESSON# {else/} <span class="x hide">#LESSON#</span>{endif/}

2. In the template's CSS section, I used this code to hide the column heading if the column is hidden:

dt:has(+dd>span.x-hide){display: none;} dd:has(span.x-hide){display: none;}

3. Here's the final code for the column template:

<dd class="t-AVPList-value" style="color: auto; font-weight: normal; font-family: Avenir; margin-top: 0; padding-top: 0; width: 100%;">
  <span style="color: red; font-weight: bold; font-family: Avenir;">#COLUMN_HEADER#</span>: <pre style="display: inline; white-space: pre-wrap; margin: 0; padding: 0;">#COLUMN_VALUE#</pre>
</dd>

<style>
  /* Hide columns with span.x-hide class */
  dt:has(+dd>span.x-hide){
    display: none;
  }
  dd:has(span.x-hide){
    display: none;
  }

  /* Make column headers red */
  dt.t-AVPList-label {
    color: red;
  }
  .t-AVPList-value {
    white-space: pre-wrap;
    word-wrap: break-word;
  }
</style>

And here's the SQL query for my report:

SELECT 
WP.WEEKLY_PLAN_DETAIL_ID, 
W.WEEKLY_PLAN_ID, 
WP.DOMAIN, 
WP.DOMAIN_INCLUDE, 
WP.CATEGORY, 
WP.CATEGORY_INCLUDE, 
WP.STANDARD_CODE, 
WP.STANDARD_CODE_INCLUDE, 
WP.STANDARD_STATEMENT, 
WP.STANDARD_STATEMENT_INCLUDE, 
WP.LEARNING_TARGETS, 
WP.LEARNING_TARGETS_INCLUDE, 
WP.ESSENTIAL_QUESTION, 
WP.ESSENTIAL_QUESTION_INCLUDE, 
WP.TEACHING_PROCEDURE, 
WP.TEACHING_PROCEDURE_INCLUDE, 
WP.ASSESSMENT, 
WP.ASSESSMENT_INCLUDE, 
WP.ASSIGNMENT, 
WP.ASSIGNMENT_INCLUDE, 
WP.PROJECT, 
WP.PROJECT_INCLUDE, 
WP.TEACHING_TIPS, 
WP.TEACHING_TIPS_INCLUDE, 
WP.CONNECTIONS, 
WP.CONNECTIONS_INCLUDE, 
WP.RESOURCES, 
WP.RESOURCES_INCLUDE, 
WP.DIFFERENTIATION, 
WP.DIFFERENTIATION_INCLUDE, 
WP.VOCABULARY, 
WP.VOCABULARY_INCLUDE, 
WP.REAL_WORLD_CONNECTION, 
WP.REAL_WORLD_CONNECTION_INCLUDE, 
WP.LESSON, 
WP.LESSON_INCLUDE, 
WP.SUB_LEARNING_TARGETS, 
WP.SUB_LEARNING_TARGETS_INCLUDE, 
WP.LEARNING_OUTCOMES, 
WP.LEARNING_OUTCOMES_INCLUDE, 
WP.ANNOUNCEMENTS, 
WP.ANNOUNCEMENTS_INCLUDE, 
VIDEO1, 
VIDEO1_INCLUDE, 
VIDEO2, 
VIDEO2_INCLUDE, 
VIDEO3, 
VIDEO3_INCLUDE, 
PDF1, 
PDF1_INCLUDE, 
PDF2, 
PDF2_INCLUDE, 
PDF3, 
PDF3_INCLUDE, 
PPT1, 
PPT1_INCLUDE, 
PPT2, 
PPT2_INCLUDE, 
PPT3, 
PPT3_INCLUDE 
FROM 
WEEKLY_PLAN W, WEEKLY_PLAN_DETAIL WP 
WHERE 
W.WEEKLY_PLAN_ID = WP.WEEKLY_PLAN_ID AND 
W.SCHOOL_SESSION = :P174_SESSION_ID 
AND W.QUARTER = :P174_QUARTER_ID 
AND W.WEEK = :P174_WEEK_ID 
AND W.SUBJECT = :P174_SUBJECT_ID 
AND W.GRADE = :P174_GRADE_ID_1 

Everything works well, but for some reports, I get an error (ORA-06502: PL/SQL: numeric or value error: character string buffer too small). After looking into it, I found that the "WP.LEARNING_TARGETS" column is causing the issue, because the occurs when this column is included in the report and does not occour when exclueded; I also discoved that the WP.LEARNING_TARGETS is having data of about 40 characters and the WP.LEARNING_TARGETS column in my table is a VARCHAR2(4000) column.

I want to include special characters and new lines in my report while keeping the format as it appears in the database. Can anyone please advise me on how to avoid this error? Thank you!

Please note that i am using Oracle APEX 23.2.5.