E9.2 BLOB Data in xml element to print dynamic image in bi publisher

[spiluttini]

Hi all.
I'have the following question how is possible to print in jde report (rda) a blob filed?



I have to print dynamic image in BI PUBLISHER.

i found two ways.

1)​

Inserting Images with an Element Reference from an XML File
Include a link to an XML file for an image.
To insert images with element references:

1. Insert a dummy image in the template.
2. In Microsoft Word's Format Picture dialog box select the Web tab. Enter the following syntax in the Alternative text region to reference the image URL:
   url:{IMAGE_LOCATION}
   where IMAGE_LOCATION is an element from the XML file that holds the full URL to the image.
   You can also build a URL based on multiple elements at runtime. Just use the concat function to build the URL string. For example:
   url:{concat(SERVER,'/',IMAGE_DIR,'/',IMAGE_FILE)}
   where SERVER, IMAGE_DIR, and IMAGE_FILE are element names from the XML file that hold the values to construct the URL.
   This method can also be used with the OA_MEDIA reference as follows:
   url:{concat('${OA_MEDIA}','/',IMAGE_FILE)}

2)Rendering an Image Retrieved from BLOB Data​

You can include an image stored as a BLOB in a form.
If results XML contains image data that had been stored as a BLOB in the database, then use the following syntax in a form field inserted in the template where you want the image to render at runtime:
<fo:instream-foreign-object content-type="image/jpg">
<xsl:value-of select="IMAGE_ELEMENT"/>
</fo:instream-foreign-object>

where
image/jpg is the MIME type of the image (other options might be: image/gif and image/png)
and
IMAGE_ELEMENT is the element name of the BLOB in the XML data.
Note that you can specify height and width attributes for the image to set its size in the published report. BI Publisher scales the image to fit the box size that you define. For example, to set the size of the example above to three inches by four inches, enter the following:
<fo:instream-foreign-object content-type="image/jpg" height="3 in" width="4 in">
<xsl:value-of select="IMAGE_ELEMENT"/>
</fo:instream-foreign-object>

Specify in pixels as follows:
<fo:instream-foreign-object content-type="image/jpg" height="300 px" width="4 px">



the first work but i have all images already stored on database in blob field so i i want to try the second method.

But in JDE if you create a view it is impossible to select blob field.
So my question is:

it is possible via RDA report to extract blob data to create an xml source with an elemnt wich contains the blob ?

thanks in advanced.
i think is impossible....

 

[DaveWagoner]

This is quite the ask! I really hope someone figured it out, but if not, I hope you figure it out! Please report back if you do.

 

[alfredorz]

Good and interesting question!

Do you have your image in a Media Object saved?

If it is I'm thinking different ways, because images are saved in F98MODAT in fact:

• Use F98MODAT api to download file: it I'll have to investigate, because I did it long time ago but I remember was for upload, but should be a C API to download too.
• I did a BSSV as a POC, but we are going to supose BSSV it's obsolete.
• Orchestrator:
  • You can download a image from external service with an Attachment UDO, but there is pass a header with Accept ocsteam ouput, so I don't know if you could call internal from jde.
  • You can use standard MO Services: https://docs.oracle.com/en/applicat...ct/9.2/rest-api/op-v2-file-download-post.html but I'm testing with postman and I haven't had any success yet, and maybe would be very difficult call internal from jde too.
  • I think your better way: Create a orchestrator with a Data Request to F00165 with Object Name (OBNM), GenericTextKey (TXKY) and Sequence Number (MOSEQN) -or without sequence, the first row found-, get GTFileName (GTFILENM), access to Data Request to F98MODAT with GTFILENAME and get as output GenericTextBuffer (TXFT). It is base64 and you call your orchestrator, get the output, print in your UBE as RV to export to XML and get from BI Publisher and print with fo:instream-foreign-object.

Example as Orchestrator with DataRequest to F00165 and F98MODAT:








Example of execution:


the magic:



I'm thinking other alternatives, but I believe it could be a good and "simple" solution for you.

KR.

 

[spiluttini]

Hi .
Thanks a lot.
Now for some rerason i can't try the orchestrator solution.

I found this other "Way" .
It seems that it can be fine.



1) create an SQL function that convert data in to clob
( i found this source on internet)
:

create
FUNCTION proddta.EncodeBASE64(InBlob IN BLOB) RETURN CLOB IS

BlobLen INTEGER := DBMS_LOB.GETLENGTH(InBlob);
read_offset INTEGER := 1;

amount INTEGER := 1440; -- must be a whole multiple of 3
-- size of a whole multiple of 48 is beneficial to get NEW_LINE after each 64 characters
buffer RAW(1440);
res CLOB := EMPTY_CLOB();

BEGIN

IF InBlob IS NULL OR NVL(BlobLen, 0) = 0 THEN
RETURN NULL;
ELSIF BlobLen <= 24000 THEN
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(InBlob));
ELSE
-- UTL_ENCODE.BASE64_ENCODE is limited to 32k, process in chunks if bigger
LOOP
EXIT WHEN read_offset >= BlobLen;
DBMS_LOB.READ(InBlob, amount, read_offset, buffer);
res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));
read_offset := read_offset + amount;
END LOOP;
END IF;
RETURN res;

END EncodeBASE64;

2) insert clob in a file on JDE in a char field :


INSERT INTO proddta.Fdeletem



select

trim(proddta.EncodeBASE64(bbtxft)) ,
ROW_NUMBER() OVER( ORDER BY BBcpil)AS num_row
from proddta.f55schdb


3) so now you have blob as string



In report i think you have to substring field in subfield less than 256 and than concatenate on bi template
I read that in JDE maximum long feld allowed is 256.

 

[spiluttini]

This is quite the ask! I really hope someone figured it out, but if not, I hope you figure it out! Please report back if you do.

Hi .
Thanks a lot.
Now for some rerason i can't try the orchestrator solution.

I found this other "Way" .
It seems that it can be fine.



1) create an SQL function that convert data in to clob
( i found this source on internet)
:

create
FUNCTION proddta.EncodeBASE64(InBlob IN BLOB) RETURN CLOB IS

BlobLen INTEGER := DBMS_LOB.GETLENGTH(InBlob);
read_offset INTEGER := 1;

amount INTEGER := 1440; -- must be a whole multiple of 3
-- size of a whole multiple of 48 is beneficial to get NEW_LINE after each 64 characters
buffer RAW(1440);
res CLOB := EMPTY_CLOB();

BEGIN

IF InBlob IS NULL OR NVL(BlobLen, 0) = 0 THEN
RETURN NULL;
ELSIF BlobLen <= 24000 THEN
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(InBlob));
ELSE
-- UTL_ENCODE.BASE64_ENCODE is limited to 32k, process in chunks if bigger
LOOP
EXIT WHEN read_offset >= BlobLen;
DBMS_LOB.READ(InBlob, amount, read_offset, buffer);
res := res || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));
read_offset := read_offset + amount;
END LOOP;
END IF;
RETURN res;

END EncodeBASE64;

2) insert clob in a file on JDE in a char field :


INSERT INTO proddta.Fdeletem



select

trim(proddta.EncodeBASE64(bbtxft)) ,
ROW_NUMBER() OVER( ORDER BY BBcpil)AS num_row
from proddta.f55schdb


3) so now you have blob as string



In report i think you have to substring field in subfield less than 256 and than concatenate on bi template
I read that in JDE maximum long feld allowed is 256.