Oracle Analytics and AI

Josue Jimenez

I have a query to extract the image from the items, but I can't add it to a report because the image or the table field doesn't have the information in base64 (encoded). It only returns an image ID and the file name.
Do you know of a validation method I can apply to the RTF file so I can view the image, please?

SELECT ITEMBASEPEO.ITEM_NUMBER
,ITEMBASEPEO.DESCRIPTION
,ITEMBASEPEO.ORGANIZATION_ID

  ,(SELECT (SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID IN (SELECT PROFILE_OPTION_ID FROM FND_PROFILE_OPTIONS_VL WHERE PROFILE_OPTION_NAME = 'EGP_UCM_URL'))||'/cs/idcplg'||'?IdcService=GET_FILE&ID='||(SELECT DM_VERSION_NUMBER FROM FND_DOCUMENTS_VL WHERE DOCUMENT_ID = (SELECT DOCUMENT_ID FROM FND_ATTACHED_DOCUMENTS WHERE PK1_VALUE = TO_CHAR(ITEMBASEPEO.ORGANIZATION_ID) AND PK2_VALUE = TO_CHAR(ITEMBASEPEO.INVENTORY_ITEM_ID) AND CATEGORY_NAME = 'IMAGE' AND ROWNUM = 1)) FROM DUAL) AS IMAGE


  ,(SELECT (SELECT DM_VERSION_NUMBER FROM FND_DOCUMENTS_VL WHERE DOCUMENT_ID = (SELECT DOCUMENT_ID FROM FND_ATTACHED_DOCUMENTS WHERE PK1_VALUE = TO_CHAR(ITEMBASEPEO.ORGANIZATION_ID) AND PK2_VALUE = TO_CHAR(ITEMBASEPEO.INVENTORY_ITEM_ID) AND CATEGORY_NAME = 'IMAGE' AND ROWNUM = 1)) FROM DUAL) AS IMAGEE


  ,(SELECT (SELECT TITLE FROM FND_DOCUMENTS_VL WHERE DOCUMENT_ID = (SELECT DOCUMENT_ID FROM FND_ATTACHED_DOCUMENTS WHERE PK1_VALUE = TO_CHAR(ITEMBASEPEO.ORGANIZATION_ID) AND PK2_VALUE = TO_CHAR(ITEMBASEPEO.INVENTORY_ITEM_ID) AND CATEGORY_NAME = 'IMAGE' AND ROWNUM = 1)) FROM DUAL) AS TITLE


  ,(SELECT (SELECT OBJECT_CONTENT_TYPE FROM FND_DOCUMENTS_VL WHERE DOCUMENT_ID = (SELECT DOCUMENT_ID FROM FND_ATTACHED_DOCUMENTS WHERE PK1_VALUE = TO_CHAR(ITEMBASEPEO.ORGANIZATION_ID) AND PK2_VALUE = TO_CHAR(ITEMBASEPEO.INVENTORY_ITEM_ID) AND CATEGORY_NAME = 'IMAGE' AND ROWNUM = 1)) FROM DUAL) AS OBJECT

FROM EGP_SYSTEM_ITEMS_VL ITEMBASEPEO

WHERE 1=1
AND ITEMBASEPEO.INVENTORY_ITEM_ID = :ITEMID
AND ITEMBASEPEO.ORGANIZATION_ID = :ORGID

I applied this code into the RTF

Thanks team!

Riyaz Ali-Oracle

Hi @Josue Jimenez,

Try the below:

Instead of embedding the image directly, you can reference the image URL dynamically in the RTF template using BI Publisher’s url:{} syntax.

1. Steps to Implement
   Construct the Image URL in Your Query You already have this part mostly done. Ensure the final URL looks like:
   https://<UCM_HOST>/cs/idcplg?IdcService=GET_FILE&ID=<Document_ID>&dDocName=<File_Name>
   You can concatenate this in your SQL query as a column named IMAGE_URL.
2. Use url:{} in the RTF Template In your RTF template, insert a form field where you want the image to appear.
   Right-click → Insert → Form Field → Text Input
   Set the field name to:
   url:{IMAGE_URL}
3. Enable Image Download in BI Publisher Make sure your BI Publisher settings allow external image downloads:
   Go to BI Publisher Admin → Report Properties
   Enable “Allow External Image References”

Check the below:

How to upload Images in Fusion and use them in RTF — Oracle Analytics

How to use images dynamically in BI Publisher RTF template — Cloud Customer Connect

Oracle Fusion BI Publisher: How to use images dynamically in BI Publisher RTF template (Doc ID 2409855.1)

Thanks,
Riyaz Ali