Using APEX with RDF Graphs on Oracle Autonomous Database

Published in

Oracle Developers

6 min readJan 9, 2023

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.

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

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

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

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.

Querying with User Input

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!