APEX
How to Display a BLOB image in an APEX Report
Do you want to display an image on a report in APEX ?
Is the image stored as a BLOB data type in your schema or the blob is returned by some functions?
If so, then displaying the BLOB is not a simple or straight forward task.
Actually it is a simple and straight forward task, as long as you know “the trick” you need to create/defined in your APEX report.
The following steps outlines what you need to do to create a report with a BLOB images. Most of these are the standard steps, except for Step 4. That is the important one.
1. Create the Report using the APEX wizard
Create a new report. In my example here I’m going to create a classic report.
Enter a title for the report, and accept the default settings
Create as new navigation menu entry
2. Define the Table or Query for the Report
Select the table or view that contains the data or define the SQL Query to return the results. It might be best to select this later option as it will make things clearer and easier to change in Step 4.
Click next on the next 2 screens of the wizard and then click the Create button.
3. Set the BLOB attribute settings
When you run the report you will get something like the following being displayed. As you can see it clearly does not display the BLOB image.
Next we need to setup the BLOB attribute settings. As shown in the following.
When we run the report now, we now get an error message.
4. Change the report query to return the length of the BLOB
Now this is the magic bit.
To get the image to display you need to go back to the Report level and change the query in the SQL Query box, to contain function below that get the length of the image in the BLOB attribute, dbms_lob.getlength() (in my example this attribute is call IMAGE)
select ID,
dbms_lob.getlength(image) image
from V_DOCUMENT_TM_IMAGE
5. The BLOB object now appears 🙂
That’s it. Now when you run your report the image will be displayed.
So now you know how to display a BLOB image in an APEX Report.
(Thanks to Roel and Joel for the help in working out how to do this)
How to get ORE to work with APEX
This blog post will bring you through the steps of how to get Oracle R Enterprise (ORE) to work with APEX.
The reason for this blog posts is that since ORE 1.4+ the security model has changed for how you access and run in-database user defined R scripts using the ORE SQL API functions.
I have a series of blog posts going out on using Oracle Text, Oracle R Enterprise and Oracle Data Mining. It was during one of these posts I wanted to show how easy it was to display an R chart using ORE in APEX. Up to now my APEX environment consisted of APEX 4 and ORE 1.3. Everything worked, nice and easy. But in my new APEX environment (APEX 5 and ORE 1.5), it didn’t work. This is the calling of an in-database user defined R script using the SQL API functions didn’t work. Here is the error message that is displayed.
So something extra was needed with using ORE 1.5. The security model around the use of in-database user defined R scripts has changed. Extra functions are now available to allow you who can run these scripts. For example we have an ore.grant function where you can grant another user the privilege to run the script.
But the problem was, when I was in APEX, the application was defined on the same schema that the r script was created in (this was the RQUSER schema). When I connect to the RQUSER schema using ORE and SQL, I was able to see and run this R script (see my previous blog post for these details). But when I was in APEX I wasn’t able to see the R script. For example, when using the SQL Workshop in APEX, I just couldn’t see the R script.
Something strange is going on. It turns out that the view definitions for the in-database ORE scripts are defined with
owner=SYS_CONTEXT('USERENV', 'SESSION_USER');
(Thanks to the Oracle ORE team and the Oracle APEX team for their help in working out what needed to be done)
This means when I’m connected to APEX, using my schema (RQUSER), I’m not able to see any of my ORE objects.
How do you overcome this problem ?
To fix this problem, I needed to grant the APEX_PUBLIC_USER access to my ORE script.
ore.grant(name = "prepare_tm_data_2", type = "rqscript", user = "APEX_PUBLIC_USER")
Now when I query the ALL_RQ_SCRIPTS view again, using the APEX SQL Workshop, I now get the following.
Great. Now I can see the ORE script in my schema.
Now when I run my APEX application I now get graphic produced by R, running on my DB server, and delivered to my APEX application using SQL (via a BLOB object), displayed on my screen.
Oralytics 