How to display the OA Framework Page attachment image stored in FND_LOBS table in the BI Publisher report output

 Problem: 

The attachments in the form images that are uploaded from OA Framework Pages will be stored in FND_LOBS tables, How to display these images in the BI Publisher report output.

Solution: 

Step 1: First we need to identify the record in fnd_lobs table .

        select FILE_ID,FILE_NAME ,FILE_DATA    from fnd_lobs where file_id = 695482.

        The file_data column actually contains the image content.

Step 2: Now the data that is stored in file_data column of fnd_lobs table is of type BLOB and we cannot display the BLOB data type directly in BI Publisher reports and we need to convert them to CLOB.

Step 3: create function to convert blob to clob

CREATE OR REPLACE FUNCTION APPS.BLOB_CLOB_TEST(p_source BLOB)
RETURN CLOB IS v_result CLOB;
BEGIN 

DBMS_LOB.createtemporary(lob_loc=>v_result,CACHE=>FALSE,dur=>0);
Wf_Mail_Util.EncodeBLOB(p_source,v_result);
RETURN (V_RESULT);
END BLOB_CLOB_TEST; 

Step 4: Use this function in the below SQL query to get the CLOB data type for column file_data.

            select FILE_ID,FILE_NAME ,BLOB_CLOB_TEST(FILE_DATA) IMAGE_FILE

    from fnd_lobs where file_id = 695482

Step 5: Use the query written in Step 4 in BI Publisher data xml.

Step 6: Display the element IMAGE_FILE in the RTF with below syntax in the field.

        <fo:instream-foreign-object content-type="image/jpg" height="1.25 in" width="1.5                 in">  <xsl:value-of select=".//IMAGE_FILE"/>  

Step 7: Run BI Publisher program and the output will show the image.