Oracle Analytics Publisher

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
48
Views
3
Comments

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:

  1. 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?
  2. Are there built-in Oracle functions or packages that support BLOB-to-Base64 encoding natively?
  3. 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?
  4. Please provide sample SQL or PL/SQL code snippets, or references to Oracle documentation, for implementing this transformation.
  5. Are there performance considerations or limitations when handling large BLOBs in this context?

Answers

  • Jorge Marañón-Oracle
    Jorge Marañón-Oracle Rank 3 - Community Apprentice

    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

  • 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

  • User508102-Oracle
    User508102-Oracle Rank 1 - Community Starter

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