How to Encode/Encrypt BLOB Column to Base64 Format for BIP Job

Question

Product:

Oracle BI Publisher / Oracle Database

Description:

We have a BIP job that retrieves data from an Oracle Database table containing a BLOB column (e.g., storing PDF or image files). For downstream processing and integration, we need to encode (or encrypt, if possible) the BLOB data into Base64 format within the SQL query or as part of the BIP data model.

Questions/Requirements:

* What is the recommended approach to encode a BLOB column to Base64 format in Oracle SQL, so that the output can be consumed by BIP and downstream systems?
* Are there built-in Oracle functions or packages that support BLOB-to-Base64 encoding natively?
* If encryption is also required, what are the best practices or supported methods for encrypting BLOB data before or after Base64 encoding for use in BIP jobs?
* Please provide sample SQL or PL/SQL code snippets, or references to Oracle documentation, for implementing this transformation.
* Are there performance considerations or limitations when handling large BLOBs in this context?

User508102-Oracle · Answer

Please see this forum post https://forums.oracle.com/ords/apexds/post/how-can-i-convert-a-blob-to-base64-7597

Varun Malhotra-Support-Oracle · Answer

Hi @Anita_ABR12

Could you please review below CCC post on how to fetch product image in RTF Template BIP Report

https://community.oracle.com/customerconnect/discussion/comment/1289884#Comment_1289884

Regards,

Varun

If my response has answered your question, please click "yes" below to accept the answer or comment with any additional queries. You can also read the Cloud Customer Connect Guidelines for Accepted Answer

Jorge Marañón-Oracle · Answer

Hello,

Q1, Q2, Q4:

Around encoding the BLOB column to Base64 in Oracle SQL, I recommend asking in their Forums here:

https://forums.oracle.com/ords/apexds/domain/dev-community/category/3063-general_questions

or here:

https://forums.oracle.com/ords/apexds/domain/dev-community/category/sql_and_pl_sql

Q3:

I could not find anything specific, I think it could be reviewed on a case by case basis and for this, the best approach is to create an SR with your specific scenario.

Q5:

These two Documents can be helpful to better understand limitations and usage on the Reports:

How To Expose Files Stored In An Oracle Database BLOB Column Within Analyses and Dashboards (Doc ID 2106746.1)

How to Insert Images and BLOBs Dynamically into Word Template or Document? (Doc ID 443957.1)

And this Documentation link, it includes some limitations:

https://docs.oracle.com/en/middleware/bi/analytics-server/datamodel-oas/model-binary-large-object-blob-data-and-character-large-object-clob-data.html

Best Regards.

Jorge