Image view In BI Publisher Report In Oracle Apex

 1. First we need to create procedure which convert image to pdf. The procedure will like bellow.

CREATE OR REPLACE FUNCTION HRMS.PDF2IMAGE (p_blob IN BLOB)

   RETURN CLOB

IS

   l_clob   CLOB;

   l_step   PLS_INTEGER := 24573;--12000;

--        make sure you set a multiple of 3 not higher than 24573

BEGIN

   FOR i IN 0 .. TRUNC ( (DBMS_LOB.getlength (p_blob) - 1) / l_step)

   LOOP

      l_clob :=

            l_clob

         || UTL_RAW.cast_to_varchar2 (

               UTL_ENCODE.base64_encode (

                  DBMS_LOB.SUBSTR (p_blob, l_step, i * l_step + 1)));

   END LOOP;

   RETURN l_clob;

END;

2. We need to create sample query to bring data from database.

SELECT UNIQUE_ID, EMPLOYEE_NAME,

          (SELECT CODE_DESC

             FROM CODE_MASTER

            WHERE HARD_CODE = 'DSG' AND SOFT_CODE = DESIGNATION_CODE)

       || CHR (10)

       || (SELECT CODE_DESC FROM CODE_MASTER  WHERE HARD_CODE = 'CDV' 

         AND  SOFT_CODE = COMPANY_DIVISION_ID)

       || CHR (10)

       || 'ID: '

       || UNIQUE_ID

       || CHR (10)

       || 'Blood Group: '

       || (SELECT CODE_DESC

             FROM CODE_MASTER

            WHERE HARD_CODE = 'BLD' AND SOFT_CODE = BLOOD_GROUP)

          EMPLOYEE_DETAILS,

          'Date of Issue: '

       || TO_CHAR (SYSDATE, 'DD.MM.RRRR')

       || CHR (10)

       || 'Date of Expire: '

       || TO_CHAR (ADD_MONTHS (SYSDATE, 24), 'DD.MM.RRRR')

          ISSUE_DATE,

       PDF2IMAGE (PICTURE) PICTURE

  FROM EMPLOYEE_INFORMATION

 WHERE UNIQUE_ID =:P464_UNIQUE_ID;

3. In BI Publisher Image Filed Properties change with Following code

 <fo:instream-foreign-object content-type="image/jpg"  height="1.2  in" width="1  in" border="1  in" border-color="blue" >

<xsl:value-of select="PICTURE"/>

</fo:instream-foreign-object>.

4. Image Resize Procedure.

CREATE OR REPLACE PROCEDURE HRMS.RESIZE_IMAGE (P_UNIQUE_ID NUMBER)

IS

   V_PHOTO        BLOB;

   V_THUMD        BLOB;

   V_MIME_TYPE    VARCHAR2 (200);

   V_FILE_NAME    VARCHAR2 (300);

   V_IMAGE_SIZE   NUMBER;

   V_WIDTH        NUMBER := 360;

   V_HEIGHT       NUMBER := 410;

BEGIN

   SELECT TO_NUMBER (CEIL (SUM (DBMS_LOB.GetLength (picture) / 1024)))

     INTO V_IMAGE_SIZE

     FROM EMPLOYEE_INFORMATION

    WHERE UNIQUE_ID = P_UNIQUE_ID;

 IF V_IMAGE_SIZE > 20

   THEN

      SELECT PICTURE,

             PICTURE,

             MIME_TYPE,

             FILENAME

        INTO V_PHOTO,

             V_THUMD,

             V_MIME_TYPE,

             V_FILE_NAME

        FROM EMPLOYEE_INFORMATION

       WHERE UNIQUE_ID = P_UNIQUE_ID

      FOR UPDATE;

      -----------Scale The Image Size

      ORDSYS.ORDIMAGE.process (V_THUMD, 'maxScale=250 320');

      ORDSYS.ORDIMAGE.process (V_PHOTO,

                               'maxScale=' || V_WIDTH || ' ' || V_HEIGHT);

      UPDATE EMPLOYEE_INFORMATION

         SET PICTURE = V_PHOTO,

             MIME_TYPE = V_MIME_TYPE,

             FILENAME = V_FILE_NAME

       WHERE UNIQUE_ID = P_UNIQUE_ID;

   END IF;

   COMMIT;

END;

/