Use Oracle Database 23c with Oracle Graph on Oracle Cloud Infrastructure

Published in

Oracle Developers

9 min readMar 12, 2024

In this article, we will install (1) Create Oracle Database 23c on OCI Oracle Base Database Service, (2) Create a SQL Property Graph in SQL Developer, and (2) install Oracle Graph Server, using the Oracle Cloud Marketplace image, to visualize graph queries and run graph algorithms.

Prerequisites

- You have an Oracle Cloud Infrastructure account.
- Your OCI user has the necessary permissions to create databases and compute instances.
- You have created a virtual cloud network and public subnet with ports 22, 1521 and 7007 open for TCP protocol.
- You have a valid SSH key pair.
- You have SQL Developer installed.

Provision Oracle Database 23c

In this section, we will create an instance of Oracle Database 23c in the Cloud.

Log into your OCI account.

2. Navigate to Oracle Base Database Service.

3. Select the compartment you want to create your Database System in the click “Create DB system”.

4. In the “Create DB system” dialog, input the necessary configuration settings. For this, you will need an ssh public key, a VCN created in OCI, and a public subnet created with ports 22 and 7007 open for TCP protocol (see example below):

5. Click “Next” and enter the Database information, then select “Change database Image”.

6. Change the database version to Oracle Database 23c, then click “Select”.

7. Set a password for the sys user.

8. Click “Create DB system” to kick off the provisioning. The provisioning process can take a few minutes. When the database is available, your DB systems details page should look like the image below.

Connect to SQL Developer

In this section, we will create a connection in SQL Developer for your Oracle Database 23c running on Oracle Cloud Infrastructure.

From your DB Systems dialog, scroll down, select Nodes, and copy the Public IP address. Save this in a notepad for now, we will use it later to connect from SQL Developer.

2. Select the Database from the same DB Systems page.

3. Select the Pluggable Database.

4. Select PDB connection and copy the final portion of the Easy Connect string after the “/”, starting with the PDB name.

5. Launch SQL Developer and create a new connection with the following information:
Name: A meaningful name for this connection, so you can easily find it.
Username: “Sys”
Password: Password you created for your sys user.
Role: “SYSDBA”
Hostname: The IP Address you copied in step 1.
Service name: The string you copied in step 4.
Port: “1521”

6. Test to ensure the connection is successful and connect.

Create Graph User

It is not best practice to use the SYS user for development activities. So, in this section, we will create a graph enabled user, named GRAPHUSER.

Open a SQL Worksheet with the connection you made in the previous steps and run the following statement to create a graph user.

CREATE USER GRAPHUSER IDENTIFIED BY "WelcomE123!123!";

2. Run the following statement to grant unlimited tablespace to the user.

ALTER USER GRAPHUSER QUOTA UNLIMITED ON USERS;

3. Run the following statement to grant the necessary graph privileges to the graph user.

GRANT CREATE SESSION, CREATE TABLE, CREATE PROPERTY GRAPH, 
CREATE ANY PROPERTY GRAPH, CREATE VIEW, CREATE MATERIALIZED VIEW, 
ALTER ANY PROPERTY GRAPH, DROP ANY PROPERTY GRAPH, 
READ ANY PROPERTY GRAPH, GRAPH_DEVELOPER TO GRAPHUSER;

4. Create a new connection in SQL Developer with your graph user.

5. Verify your connection by clicking “Test”, and if the status changes to “Success”, click “Connect”.

Load Data

SQL Property Graphs are created by making a graph view on relational tables, so the next step is to create the tables to hold the source data. In this section, we will load two tables to create a graph of bank accounts and transfers between those accounts.

Download the sample bank accounts and bank transfers datasets.
Let’s import the bank accounts dataset. In SQL Developer, right click on the Tables for your graph user and select “Import Data”.

3. In the Data Import Wizard, select the BANK_ACCOUNTS.csv file you downloaded earlier, and click “Next”.

4. Name the table “BANK_ACCOUNTS” and click “Next”.

5. Verify that all three columns are selected and click “Next”.

6. Verify that the data types for ID, name and balance are number, varchar2, and number respectively. Then click “Next”.

7. Click “Finish”.

8. Now, let’s import the bank transfers dataset. In SQL Developer, right click on the Tables for your graph user and select “Import Data”.

9. In the Data Import Wizard, select the BANK_TRANSFERS.csv file you downloaded earlier, and click “Next”.

10. Name the table “BANK_TRANSFERS” and click “Next”.

11. Verify that all five columns are selected and click “Next”.

12. Verify that the data types for TXN_ID, src_acct_id, dst_acct_id, description and amount are number, number, number, varchar2, and number respectively. Then click “Next”.

13. Click “Finish”.

14. Notice that you now have the BANK_ACCOUNTS and BANK_TRANSFERS tables visible to you in SQL Developer.

Create SQL Property Graph

In this section, we will create a SQL Property Graph from the dataset we loaded in the last section and run a couple queries in SQL Developer to ensure the graph was created successfully.

In a SQL worksheet, paste the following Create Property Graph statement:

CREATE PROPERTY GRAPH BANK_GRAPH 
    VERTEX TABLES (
        BANK_ACCOUNTS
        KEY (ID)
        PROPERTIES (ID, Name, Balance) 
    )
    EDGE TABLES (
        BANK_TRANSFERS 
        KEY (TXN_ID) 
        SOURCE KEY (src_acct_id) REFERENCES BANK_ACCOUNTS(ID)
        DESTINATION KEY (dst_acct_id) REFERENCES BANK_ACCOUNTS(ID)
        PROPERTIES (src_acct_id, dst_acct_id, amount)
    );

2. Run the statement. The output should state “Property GRAPH created.”.

3. Run the following statement to list all SQL Property Graphs for your user. Notice that BANK_GRAPH is listed.

SELECT * FROM user_property_graphs;

4. You can run SQL Property Graph queries from the same SQL worksheet. For example, run the following query to find the top 10 accounts by incoming transfers.

SELECT acct_id, COUNT(1) AS Num_Transfers 
    FROM graph_table ( BANK_GRAPH 
        MATCH (src) - [IS BANK_TRANSFERS] -> (dst) 
        COLUMNS ( dst.id AS acct_id )
    ) GROUP BY acct_id ORDER BY Num_Transfers DESC 
FETCH FIRST 10 ROWS ONLY;

Find more queries for this dataset in this GitHub repository.

Launch Graph Server and Client OCI Marketplace Image

If you want to visualize your SQL Property Graph queries, you can do so using the Graph visualization tool. This requires an install of Oracle Graph Server, which we will install in this section. You can also run graph algorithms on your SQL Property Graph in Graph Server.

From your Oracle Cloud Infrastructure console, navigate to the OCI Marketplace.

2. In the marketplace, search for and select “Oracle Graph Server and Client”.

3. Select the latest version of Oracle Graph Server and Client and select “Launch Stack”.

4. In the Stack Information page, leave everything as default, and click Next.

5. Now, input the necessary configuration settings, then click Next (see example below). You will need to create a JDBC URL for this step, which can be created using the hostname, port and service name used to connect to the database with SQL Developer in this format:

jdbc:oracle:thin:@//<hostname>:<port>/<service_name>

6. Review your settings, enable “Run apply”, and select “Create”

7. You will then be brought to the Job details page where you can monitor the status of the stack deployment. When the job is complete, the box on the top left will turn green and the status will change to “succeeded”.

8. Once the job is complete, you can click the Application Information tab, and navigate directly to the graph visualization application.

9. It may take a few additional minutes after the job is complete for Graph Server to finish setting up, so you may not see the login screen immediately. When it is finished setting up, you can log into the graph visualization tool using your graph user.

Visualize SQL Graph Queries

In this section, we will run a SQL Property Graph query, and display it as a graph, using the Graph Visualization tool.

Select the Database (SQL Property Graphs) tab.

2. Run the following query to visualize all vertices connected by an edge. Notice that the columns clause has vertex_id and edge_id. These are required to display the result set as a graph.

SELECT id_a, id_e, id_b
 FROM GRAPH_TABLE ( BANK_GRAPH
  MATCH (a) -[e]-> (b)
  COLUMNS (vertex_id(a) AS id_a, edge_id(e) AS id_e, vertex_id(b) AS id_b )
) FETCH FIRST 100 ROW ONLY

Next Steps

Congratulations! You now have Graph Server instance with Oracle Database 23c on OCI with a sample graph. You can continue visualizing your own graph queries using the syntax outlined here. You can also learn more about the visualization settings available in the graph visualization tool here.

Please learn more about Oracle Graph from:

Medium (= all articles with Oracle Graph tag)
Stack Overflow (= questions with oracle-graph tag)
Slack AnDOUC (= invitation link, please visit #graph)
Slack OracleDevRel (= invitation link, please visit #oracle-db-graph)