Wednesday, August 24, 2016

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.

NewImage

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.

NewImage

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.

NewImage

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.

NewImage

Posted by Brendan Tierney at 3:02 PM
Labels: , , ,

7 comments:

  1. Juergen SchusterAugust 26, 2016 at 7:56 PM

    Hi Brendan, it would be nice if you could register on apex.world then I can post your blog post in the news section.

    ReplyDelete
    Replies
    1. Brendan TierneyAugust 27, 2016 at 11:05 AM

      Hi. I've registered on apex.world :-)
      Let me know when you have shared the post

      Delete
  2. UnknownAugust 27, 2016 at 7:55 PM

    Brendan, thanks for this and welcome to apex.world.

    ReplyDelete
  3. Kevin ZhangSeptember 18, 2017 at 8:49 PM

    Hi Brendan:

    Is it possible for you to share the demo APEX application you did for this post?

    So far, I am able to duplicate all the steps and I am approaching to the last step: APEX.

    I am also an APEX developer for 8 years (since APEX 3.x) + Oracle EBS development for many years ... recently, I stepped into the Big Data project ... I am so excited to see your excellent post, especially it demo that APEX can be leveraged for Big Data/Predictive analysis ...

    if you can share your APEX export file with me kevin.zhang@knowles.com, that will be super!

    I am based in Chicago, USA.

    I just ordered your 2 books:

    - Predictive Analytics Using Oracle Data Miner: Develop & Use Data Mining Models in Oracle Data Miner
    - Oracle R Enterprise: Harnessing the Power of R in Oracle Database

    I am looking forward to learning more from you.


    Thanks in advance!

    Kevin

    ReplyDelete
    Replies
    1. OralyticsSeptember 18, 2017 at 10:38 PM

      Check out the very next blog post after this one for the details you are looking for.
      It depends on the version of APEX, what approach you need to take to display the image

      Delete
    2. Kevin ZhangSeptember 19, 2017 at 2:56 AM

      Hi Brendan:

      Can you tell me which blog post you are referring "Check out the very next blog post after this one for the details you are looking for"

      Sorry to keep bothering ... I am so desperate to wrap up the last piece (display wordcloud image) in APEX application.

      in my Oracle BigDataLite V4.7 VM, I am having APEX version 5.0 installed.

      Do you mind share more insight for how you display image (blob column) in the APEX application?

      in my trial, I have created a view:

      CREATE OR REPLACE VIEW demo_text_minining_ore
      AS
      SELECT
      md.doc_pk,
      md.doc_title,
      md.doc_extracted,
      md.data_source,
      md.doc_text,
      (
      SELECT image
      FROM TABLE(rqTableEval( CURSOR(SELECT * FROM RQUSER.MY_DOCUMENTS ll WHERE ll.doc_pk = 1),
      NULL,
      'PNG',
      'RQUSER.prepare_tm_data_2'))
      ) wordcloud
      FROM
      rquser.my_documents md

      Then I created an interactive report (IR) using this view.

      when I configured the column worldcloud in APEX, I can't figure out what is right configuration for this column. I tried both "Download Blob" and "Display Image" type ...

      Can you share more? If you defined any view in the database, can you also share your view source code?

      my email is kevin.zhang@knowles.com

      Thanks!

      Kevin

      Delete
    3. OralyticsSeptember 19, 2017 at 3:05 AM

      Check the blog post dated September 1, 2916

      Delete

Subscribe to: Post Comments (Atom)