Display Html tags in IR from a function

Question

I have a function and inside it , I write a case to display an img tag as we see :

 FUNCTION HTML_SAMPLE (...)
....
BEGIN

SELECT 
CASE "ISACTIVE"
             WHEN 1 THEN
              '<img src="#WORKSPACE_IMAGES#ok-icon.png" />'
             WHEN 0 THEN
              '<img src="#WORKSPACE_IMAGES#Button-no-icon.png" />'
           END "ISACTIVE_ICON"
FROM PERSONS;
....
END;

and here when I use it for an interactive report , it wont display html tags correctly ! Even I set the escape special characters off

and there's a strange thing to know ! when I use the table from function directly in IR , it will work correctly ! and the source of img will be different !

here's what I've selected directly in IR :

 SELECT 
    CASE "ISACTIVE"
                 WHEN 1 THEN
                  '<img src="#WORKSPACE_IMAGES#ok-icon.png" />'
                 WHEN 0 THEN
                  '<img src="#WORKSPACE_IMAGES#Button-no-icon.png" />'
               END "ISACTIVE_ICON"
    FROM PERSONS;

How could this happen ? I'm confused

Answer 1

For some reason (I don't know why), function should return only image name, without HTML tags or image location.

CREATE OR REPLACE FUNCTION f_is_clerk (par_empno IN NUMBER)
   RETURN VARCHAR2
IS
   retval  VARCHAR2 (100);
BEGIN
   SELECT CASE
             WHEN job = 'CLERK' THEN 
                  'CB_CHECKED.JPG' 
                  -- Not this! '<img src="#WORKSPACE_IMAGES#CB_CHECKED.JPG"/>'
          END
     INTO retval
     FROM emp
    WHERE empno = par_empno;

   RETURN retval;
END;
/

Interactive Report query should be adjusted so that it concatenates starting part of HTML tags and image location with function result and ending HTML part:

  SELECT ename,
         job,
         CASE WHEN job = 'CLERK' THEN '<img src="#WORKSPACE_IMAGES#CB_CHECKED.JPG"/>' END is_clerk,
         --       
         '<img src="#WORKSPACE_IMAGES#' || f_is_clerk (empno) || '"/>' is_clerk_fnc
    FROM emp
   WHERE deptno = 10
ORDER BY ename;

Then IR shows the image as expected:

---

If function returns everything (as in your case):

SELECT CASE WHEN job = 'CLERK' THEN '<img src="#WORKSPACE_IMAGES#CB_CHECKED.JPG"/>' END
INTO retval

and IR query contains just

f_is_clerk (empno) is_clerk_fnc

result is as yours: wrong:

Therefore, do as instructed. Hopefully, someone else will be able to explain why it happens; I can't.