Oracle Developers
Published in

Oracle Developers

Using APEX with RDF Graphs on Oracle Autonomous Database

Oracle Autonomous Database includes Graph Studio and Oracle APEX. Graph Studio automates graph data management, making it simple to model, analyze and visualize data. Oracle APEX is a low code development tool that simplifies the application development lifecycle.

This article will show you how to create an application using SPARQL and RDF graphs in minutes. We will leverage the built in capabilities of the Oracle Autonomous Database to easily run SPARQL queries on an RDF Graph and output the result to a report in APEX.

Prerequisites:

  • Provision an Autonomous Database instance with Graph enabled user. If you have not done this, see this blog post. In this blog post, we assume that user is named GRAPHUSER
  • Create an RDF Graph. If you have not done this, see this article.

Enable Java in your Database

SPARQL queries are run in APEX using the SEM_MATCH API, which requires Java to be enabled on your Autonomous Database instance.

  1. From cloud console, navigate to your Autonomous Database instance, select Database actions.

2. Select SQL.

3. Run the following code snippet to enable Java in the database.

BEGIN
DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
feature_name => 'JAVAVM' );
END;

4. Restart your Autonomous Database. When a dialog appears asking you to confirm, press Restart. It may take a few minutes to restart.

Create APEX Workspace

  1. From cloud console, navigate to your Autonomous Database instance, select the Tools tab, and select Open APEX.

2. Sign into Administrative Services using your Admin password.

3. Select Create Workspace.

4. Select Existing Workspace to create a workspace for GRAPHUSER.

5. Create a workspace for your graph user. In this example, we will use GRAPH USER as the database user, workspace name and workspace username. Then select Create Workspace.

6. Sign out of Administrative Services, so you can sign into the workspace that we created.

Create APEX Application

  1. From cloud console, navigate to your Autonomous Database instance, select the Tools tab, and select Open APEX.

2. Sign in using the workspace credentials that you made for GRAPHUSER.

3. Select App Builder > Create New App > New Application

4. Name the Application. In this example we will use the name ‘RDF Graph Sample App’. Then click Create Application.

Create Report with SPARQL Query

  1. Within App Builder for your new application, select the default home page

2. From the Regions section, drag the Interactive Report into the body of the page designer.

3. Change the title of the interactive report to ‘My RDF Query’. Change the type to SQL Query, and expand the SQL Query text box.

4. Paste the following code snippet to search for all the distinct genres that each actor has been in. This snippet uses the sem_match API to run the SPARQL query against the moviestream graph.

SELECT actor, genres from table (sem_match('
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ms: <http://www.example.com/moviestream/>

SELECT ?actor (GROUP_CONCAT(DISTINCT STR(?genre) ; separator=", ") as ?genres)
WHERE { ?movie ms:actor/ms:name ?actor .
?movie ms:genre/ms:genreName ?genre .
}
GROUP BY ?actor', sem_models('moviestream'), null, null, null, null, ' ', null, null, 'GRAPHUSER', 'RDF_NETWORK'));

5. Click the check mark at the top of the dialog box to validate, then click OK.

6. Click the run button at the top right of the page designer.

7. Log in using your GRAPHUSER credentials, and you will see the results of that SPARQL Query displayed in an interactive grid!

If you do not see the results displayed in the interactive grid, see the troubleshooting section at the end of this article.

  1. In your page designer, drag in a new Text Field and name it USER_INPUT.

2. Drag in a new Interactive Report. Name it ‘Genres by Actor’, change the type to a SQL Query and expand the SQL Query text box.

3. Paste in the following code snippet which will query for the genre of movies that an actor has been in by the specified user input.

SELECT actor, gname from table (sem_match('
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ms: <http://www.example.com/moviestream/>

SELECT DISTINCT ?actor ?gname
WHERE {
?movie ms:actor/ms:name ?actor;
ms:genre/ms:genreName ?gname .
}
ORDER BY ASC(?gname)', sem_models('moviestream'), null, null, null, null, ' ', null, null, 'GRAPHUSER', 'RDF_NETWORK'))
where actor$_prefix = :USER_INPUT;

4. Validate and select OK.

5. Right Click on the USER_INPUT field and select Create Dynamic Action.

6. Name the Dynamic Action ‘On Return’, and ensure the event is set to ‘Change’.

7. Change the True action to be Submit Page.

8. Click the run button at the top right of the page designer.

9. You will notice that the report is blank. this is because we haven't specified a user input yet.

10. If we type ‘Keanu Reeves’ into the input box, and press enter, we will see all the genres that Keanu Reeves has made movies in, by running a SPARQL query based on the user input field!

Troubleshooting

If your columns do not display in the interactive grid, you can explicitly select the columns from the Actions dropdown.

If this is the case, select the Actions dropdown, and select Columns.

Select the Move All button to display all columns in the report, and select Apply.

Please learn more about Oracle Graph from:

If you’re curious about the goings-on of Oracle Developers in their natural habitat, come join us on our public Slack channel!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rahul Tasker

Product Manager of Oracle Spatial and Oracle Graph. The views expressed here are my own, and the sample scripts in the articles are not supported.