2.2.3 Quick Start: Executing PGQL Queries in SQLcl

This tutorial provides you resources to get started on executing PGQL Queries in SQLcl.

You can execute PGQL queries in SQLcl with a plug-in that is available with Oracle Graph Server and Client. See PGQL Plug-in for SQLcl in Oracle SQLcl User’s Guide for more information.

The example in this tutorial helps you get started on executing PGQL queries on a graph in SQLcl. As a prerequisite, to perform the steps in the example, you must set up the bank graph data in your database schema using the sample data provided with the graph server installation. See Using Sample Data for Graph Analysis for more information.

The following example creates a property graph using the PGQL CREATE PROPERTY GRAPH statement, executes PGQL queries against the graph and finally drops the graph using SQLcl.

1. Start SQLcl with your database schema credentials. In the following command, graphuser is the database user used to connect to SQLcl.

   sql graphuser/<password_for_graphuser>@<tns_alias>
   
   SQLcl: Release 21.2 Production on Sun Jan 30 04:30:09 2022
   Copyright (c) 1982, 2022, Oracle.  All rights reserved.
   Connected to:
   Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
   Version 21.3.0.0.0

2. Enable PGQL mode as shown:

   SQL> pgql auto on;
   
   PGQL Auto enabled for schema=[null], graph=[null], execute=[true], translate=[false]

   Note that no arguments are used in the preceding PGQL command.

3. Create a property graph view on the bank graph data tables.

   PGQL> CREATE PROPERTY GRAPH bank_graph
     2          VERTEX TABLES (
     3            bank_accounts
     4              LABEL ACCOUNTS
     5              PROPERTIES (ID, NAME)
     6          )
     7          EDGE TABLES (
     8            bank_txns
     9              SOURCE KEY (from_acct_id) REFERENCES bank_accounts
    10              DESTINATION KEY (to_acct_id) REFERENCES bank_accounts
    11              LABEL TRANSFERS
    12              PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION)
    13*         ) OPTIONS(PG_VIEW);
   
   Graph created

4. Set bank_graph as the default graph using the graph argument when enabling PGQL mode.

   PGQL> pgql auto on graph bank_graph;
   
   PGQL Auto enabled for schema=[null], graph=[BANK_GRAPH], execute=[true], translate=[false]
   

5. Execute PGQL queries against the default graph. For example, the following PGQL query retrieves the total number of vertices as shown:

   PGQL> SELECT COUNT(*) AS num_vertices FROM MATCH(n);
   
      NUM_VERTICES
   _______________
              1000
   

   Note that in the preceding query, the graph name is not specified using the ON clause as part of the MATCH clause.

6. Reconnect to SQLcl as another schema user.

   PGQL> conn system/<password_for_system>@<tns_alias>;
   Connected.

7. Enable PGQL mode using the schema argument to set the default schema used for creating the graph. Also, set bank_graph as the default graph using the graph argument :

   PGQL> pgql auto on schema graphuser graph bank_graph;
   
   PGQL Auto enabled for schema=[graphuser], graph=[BANK_GRAPH], execute=[true], translate=[false]

8. Execute a PGQL query to retrieve all the edge properties on the graph as shown:

   PGQL> SELECT e.* FROM MATCH (n:accounts) -[e:transfers]-> (m:accounts) LIMIT 10;
   
      AMOUNT    DESCRIPTION    FROM_ACCT_ID    TO_ACCT_ID
   _________ ______________ _______________ _____________
        1000 transfer                   178           921
        1000 transfer                   178           462
        1000 transfer                   179           688
        1000 transfer                   179           166
        1000 transfer                   179           397
        1000 transfer                   179           384
        1000 transfer                   179           900
        1000 transfer                   180           855
        1000 transfer                   180           984
        1000 transfer                   180           352
   
   10 rows selected.

   Therefore, you can set a default schema and execute PGQL queries against a default graph in SQLcl.

9. Finally, drop the graph after executing the required graph queries.

   PGQL> DROP PROPERTY GRAPH bank_graph;
   
   Graph dropped
   

Also, see Execute PGQL Queries in SQLcl for more information.