How to prevent the interpretation of HTML code in the header of a dynamic CR

Soukaina IDRISSI

Hello experts,

Im using APEX 21.2. In my project, I created a CR with dynamic header, i mean in Attributes tab > Heading section i selected PL/SQL Function Body as Type, by putting this code

DECLARE 
res VARCHAR2(255);

BEGIN
WITH res AS
  (SELECT d.deptno AS dept_no,
          LISTAGG(e.empno || ', ' || e.ename || '<br/>' ||  job || '<br/>' , ' + ') 
          WITHIN GROUP (ORDER BY d.deptno) AS emps
   FROM emp e
   INNER JOIN dept d ON e.deptno = d.deptno
   WHERE d.deptno = 10
   GROUP BY d.deptno)

SELECT ':' || LISTAGG(res.emps, ':') WITHIN GROUP (ORDER BY res.emps)
 INTO res 
FROM res ;

RETURN res;
END;

By running this code i get this result: (Im using custom report template)

Template Type: Generic Columns (column template)

And the code of Column Heading Template is:

<th #ALIGNEMENT# id="#COLUMN_HEADER_NAME#" class="custom-header" #COLUMN_WIDTH#>#COLUMN_HEADER#</th>

By reproducing the same code in apex.oracle.com, I get this result

I can't figure out where this difference comes from.

For more details i created a demo, on apex.oracle.com with this credentials:

Workspace: ws_initiation

Username: demo

Password: azerty1234

I used the Application 91527 - Custom CR / Page 2 - Custom Group Column, Region Example BR Header

Thank you for help.

fac586

By running this code i get this result: (Im using custom report template)

Looks like there is an invalid invisible character in the HTML. Did you copy the tag from a web page?

Delete the HTML strings in their entirety and re-enter them manually.

What are you actually trying to do here? What is the context? That content doesn't appear to make much sense as a column heading. Additionally this is a non-conforming use of the br element as the line breaks are not intrinsic to the content.

Soukaina IDRISSI

Hi fac586,

Thank you for reply,

In this example I'm trying to list all the employees in department 10. (It's true that it doesn't make sense but it's just an example)

What I want to do is: to display each employee, job in a line.

And even if Im using <p> tag which is inline. This is what i get when i inspect element

the <p> tag is interpreted as text 

fac586

There seems to be something wrong with the character encoding of the header.

What about the ¿¿¿/abc? Is that what you expect from the data?

Soukaina IDRISSI

yes, these are Japanese characters 

fac586

yes, these are Japanese characters

So presumably you are expecting to see Japanese characters rather than ¿¿¿?

Soukaina IDRISSI

Yes, display Japanese characters with a line break.

But even if I put normal letters it shows me the same problem 

fac586

I can't figure out where this difference comes from.

There is clearly a difference/problem in your environment.

Post the following information:

• Results of select * from nls_database_parameters where parameter like '%SET'
• From the (?) > About link in the App Builder toolbar:
  • REQUEST_CHARSET
  • REQUEST_IANA_CHARSET
  • NLS_CHARACTERSET
  • DAD_CHARACTERSET
• The exact code used in the report heading function
• DDL definitions of all tables used in the report heading function

Soukaina IDRISSI

knowing that I don't have to change the NLS_NCHAR_CHARACTERSET to 'AL32UTF8'

DECLARE
  rp_header VARCHAR2(5000);
BEGIN
WITH gp_i AS (SELECT r.gp_id AS gp_id,
                        LISTAGG(i.name || '<br/>' || jp_name || '<br/>' ||  sp_name , ' + ') 
                        WITHIN GROUP (ORDER BY r.i_id) AS lst
                 FROM REFC r
                    INNER JOIN ING      i ON i.i_id = r.i_id
                    INNER JOIN ING_GP   g ON g.gp_id = r.gp_id
                 WHERE refc_id = :P101_REF_ID 
                 GROUP BY r.gp_id)

SELECT ':::' || LISTAGG(gp_i.lst, ':') WITHIN GROUP (ORDER BY gp_i.lst) || 
         ':' || DECODE(:f_lang, 'FR', 'Résultat', 'Result') AS report_header 
INTO rp_header
FROM gp_i;

RETURN rp_header;
END;

Structure of tables:

REFC
------
refc_id	VARCHAR2(18 CHAR)
i_id	VARCHAR2(18 CHAR)
gp_id	NUMBER

ING
-----
i_id	VARCHAR2(18 CHAR)
i_name	VARCHAR2(255 BYTE)

ING_GP
------
gp_id	NUMBER
jp_name NVARCHAR2(50 CHAR)
sp_name	VARCHAR2(50 CHAR)

fac586

It's not possible to store Japanese characters in a VARCHAR2 variable in a WE8ISO8859P1 database.

Try changing the type of rp_header to NVARCHAR2. It might be necessary to cast name and sp_name to NVARCHAR2 as well.

It's quite possible that this won't work because of subsequent processing: it's obviously necessary for APEX to split the colon-delimited list of headers to get the header for each column, and that's likely to be VARCHAR2-based.

You might want to start with a basic proof-of-concept with a header function that simply returns an NVARCHAR2 value containing colon-delimited Japanese characters.

Soukaina IDRISSI

But I think that the Japanese characters are not the cause, that the line break does not work, and that the HTML tags are not interpreted , isn't it ?