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.
Oracle Text, Oracle R Enterprise and Oracle Data Mining – Part 3
This is the third blog post of a series on using Oracle Text, Oracle R Enterprise and Oracle Data Mining. Check out the first and second blog posts of the series, as the data used in this blog post was extracted, processed and stored in a databases table.
This blog post is divided into 3 parts. The first part will build on what was covered in in the previous blog post and will expand the in-database ORE R script to include more data processing. The second part of this blog post will look at how you can use SQL to call our in-database ORE R scripts and to be able to include it in our custom applications, for example using APEX (part 3).
Part 1 – Expanding our in-database ORE R script for Text Mining
In my previous blog post we created an ORE user defined R script, that is stored in the database, and this script was used to perform text mining and to create a word cloud. But the data/text to be mined was processed beforehand and passed into this procedure.
But what if we wanted to have a scenario where we just wanted to say, here is the table that contains the data. Go ahead and process it. To do this we need to expand our user defined R script to include the loop to merge the webpage text into one variable. The following is a new version of our ORE user defined R script.
> ore.scriptCreate("prepare_tm_data_2", function (local_data) { library(tm) library(SnowballC) library(wordcloud) tm_data <-"" for(i in 1:nrow(local_data)) { tm_data <- paste(tm_data, local_data[i,]$DOC_TEXT, sep=" ") } txt_corpus <- Corpus (VectorSource (tm_data)) # data clean up tm_map <- tm_map (txt_corpus, stripWhitespace) # remove white space tm_map <- tm_map (tm_map, removePunctuation) # remove punctuations tm_map <- tm_map (tm_map, removeNumbers) # to remove numbers tm_map <- tm_map (tm_map, removeWords, stopwords("english")) # to remove stop words tm_map <- tm_map (tm_map, removeWords, c("work", "use", "java", "new", "support")) # prepare matrix of words and frequency counts Matrix <- TermDocumentMatrix(tm_map) # terms in rows matrix_c <- as.matrix (Matrix) freq <- sort (rowSums (matrix_c)) # frequency data res <- data.frame(words=names(freq), freq) wordcloud (res$words, res$freq, max.words=100, min.freq=3, scale=c(7,.5), random.order=FALSE, colors=brewer.pal(8, "Dark2")) } )
To call this R scipts using the embedded R execution we can use the ore.tableApply function. Our parameter to our new R script will now be an ORE data frame. This can be a table in the database or we can create a subset of table and pass it as the parameter. This will mean all the data process will occur on the Oracle Database server. No data is passed to the client or processing performed on the client. All work is done on the database server. The only data that is passed back to the client is the result from the function and that is the word cloud image.
> res res
Part 2 – Using SQL to perform R Text Mining
Another way you ccan call this ORE user defined R function is using SQL. Yes we can use SQL to call R code and to produce an R graphic. Then doing this the R graphic will be returned as a BLOB. So that makes it easy to view and to include in your applications, just like APEX.
To call our ORE user defined R function, we can use the rqTableEval SQL function. You only really need to set two of the parameters to this function. The first parameter is a SELECT statement the defines the data set to be passed to the function. This is similar to what I showed above using the ore.tableApply R function, except we can have easier control on what records to pass in as the data set. The fourth parameter gives the name of the ORE user defined R script.
select * from table(rqTableEval( cursor(select * from MY_DOCUMENTS), null, 'PNG', 'prepare_tm_data_2'));
This is the image that is produced by this SQL statement and viewed in SQL Developer.
Part 3 – Adding our R Text Mining to APEX
Adding the SQL to call an ORE user defined script is very simple in APEX. You can create a form or a report based on a query, and this query can be the same query that is given above.
Something that I like to do is to create a view for the ORE SELECT statement. This gives me some flexibility with some potential future modifications. This could be as simple as just changing the name of the script. Also if I discover a new graphic that I want to use, all I need to do is to change the R code in my user defined R script and it will automatically be picked up and displayed in APEX. See the images below.
WARNING: Yes I do have a slight warning. Since the introduction of ORE 1.4 and higher there is a slightly different security model around the use of user defined R scripts. Instead of going into the details of this and what you need to do in this blog post, I will have a separate blog post that describes the behaviour and what you need to do allow APEX to use ORE and to call the user defined R scripts in your schema. So look out for this blog post coming really soon.
In this blog post I showed you how you use Oracle R Enterprise and the embedded R execution features of ORE to use the text from the webpages and to create a word cloud. This is a useful tool to be able to see visually what words can stand out most on your webpage and if the correct message is being put across to your customers.
Installing Oracle Apex 4.0 on 11.2g
Over the past few days I’ve been trying to install Oracle Apex 4 on my 11.2g database. I say trying as I’ve made a number of attempts with no success. I started with the install instructions that come with Apex 4. Generally Oracle installs and install instructions have improved greatly since the 6, 7 and 8i versions.
I had high hopes of an easy install (as indicated by the various Oracle Apex books), but no matter what version of the install instructions I found there always seemed to be a step missing.
I finally came across one set of instructions that worked for me. The following steps are what I performed to get Apex 4 working.
1. Download Apex 4 from OTN to the directory
c:\apex_download
2. Unzip the Apex 4 download file. It will create the directory
c:\apex_download\Apex
3. Login into SQL*Plus as SYS with SYSDBA
4. Run the Apex 4 install script
c:\apex_download\Apex\apexins.sql SYSAUX SYSAUX TEMP /i/
where SYSAUX is the tablespace for Apex, TEMP is the temporary tablespace and /i/ is needed for possible upward compatability
This steps can take up to 30 minutes to run
5. Load the Apex images into the database.
   – Got to the c:\apex_download\Apex directory.
   – Log into SQL*Plus as SYS with SYSDBA
   – run  @apxldimg.sql
   – You will be asked to enter the directory for the images. Make sure that you enter the correct directory, otherwise it will not work. In my case it is
             c:\apex_download
6. Run the Configuration script. This will set up the XDB HTTP connection details.
       @apxconf.sql
  – enter the port number : 8080
  – password
7. Unlock the required schema
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL > ALTER USER XDB ACCOUNT UNLOCK;
SQL > ALTER USER APEX_040000 ACCOUNT UNLOCK;
SQL > ALTER USER FLOWS_FILES ACCOUNT UNLOCK;
SQL > ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
8. Open Apex. Open your browser and enter
    http://localhost:8080/apex
    there is a default workspace created
     Workspace = internal
     Username = admin
      Password = admin
6. Change the password. The fist time you login you will be prompted to change the password. The new password needs to have a number, upper and lower case characters and one special character
7. To get the the Apex Admin page