%sql
-- Get Current user
SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;

Example: Visualization Using Charts

You can visualize any tabular output from a SQL query using charts in a notebook paragraph. For example, the following SQL query to determine the products bought by a specific customer, is visualized using a Bar Chart:

%sql
SELECT p.prod_name, count(p.prod_name) AS sold
FROM sh.products p, sh.sales s, sh.customers c
WHERE p.prod_id = s.prod_id AND s.cust_id = c.cust_id AND c.cust_id= 3221
GROUP BY p.prod_name;

Description of the illustration sql_viz.png

Example: Creating, Querying, Visualizing, and Deleting SQL Property Graphs

If you are using an Autonomous Database instance with Oracle Database 23ai, then you can create, query, and visualize SQL property graphs using the SQL interpreter.

The following code uses the CREATE PROPERTY GRAPH DDL statements for creating a SQL property graph in a notebook paragraph:

%sql
CREATE PROPERTY GRAPH bank_sql_pg
  VERTEX TABLES (
    bank_accounts
      KEY (id)
      LABEL account
      PROPERTIES ALL COLUMNS
  )
  EDGE TABLES (
    bank_txns
      KEY (txn_id)
      SOURCE KEY (from_acct_id) REFERENCES bank_accounts (id)
      DESTINATION KEY (to_acct_id) REFERENCES bank_accounts (id)
      LABEL transfer
      PROPERTIES ALL COLUMNS
  );

You can query the SQL property graph using SQL graph queries.

%sql
SELECT * FROM GRAPH_TABLE (bank_sql_pg
  MATCH
  (a IS account WHERE a.id = 816) -[e IS transfer]-> (b IS account)
  COLUMNS (a.id AS acc_a, e.amount AS amount, b.id AS acc_b)
);

The preceding query produces the following output:

ACC_A	AMOUNT	ACC_B
816	8781	287
816	6381	590
816	9011	934
816	6890	289
816	4443	812

You can also visualize the output of SQL graph queries. In order to visualize the vertices and edges of the SQL graph query, you must return the vertex and edge IDs. For example:

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 10 ROWS ONLY

Note that the COLUMNS clause in the preceding query uses the VERTEX_ID and EDGE_ID operators. The visualization output of the SQL graph query is as shown:

Finally, you can delete the SQL property graph using the DROP PROPERTY GRAPH DDL statement as shown:

%sql
DROP PROPERTY GRAPH bank_sql_pg;

Example: Creating and Using Custom Database Views for PGQL Property Graphs

Another example scenario is to create custom database views using the SQL interpreter, which are then used to create a property graph. Note that this example scenario applies only for PGQL property graphs.

As shown in the following sequence of SQL paragraphs, database views are created on the SALES and CUSTOMERS tables in SH schema. Also, the primary key and foreign key constraints are defined for the views.

%sql
CREATE VIEW sh_customers 
AS SELECT cust_id, cust_first_name, cust_last_name, country_id, cust_city, cust_state_province 
FROM sh.customers;

%sql
ALTER VIEW sh_customers
ADD CONSTRAINT shcustomers_id PRIMARY KEY (cust_id) 
DISABLE NOVALIDATE;

%sql
CREATE VIEW sh_sales 
AS SELECT rownum sale_id, cust_id, prod_id, channel_id, promo_id, quantity_sold, amount_sold 
FROM sh.sales;

%sql
ALTER VIEW sh_sales 
ADD CONSTRAINT shsales_id PRIMARY KEY (sale_id) 
DISABLE NOVALIDATE;

%sql
ALTER VIEW sh_sales 
ADD CONSTRAINT shsale_cust_fk FOREIGN KEY (cust_id) 
REFERENCES sh_customers DISABLE NOVALIDATE;

You can then create a PGQL Property Graph graph using these database views (see Create a Property Graph from Existing Relational Tables) and then perform graph visualizations in a PGQL (PGX) paragraph as shown:

Example: XML Support in Table Visualization

Graph Studio provides support for visualizing tabular data with XMLType and CLOB data type columns. The results of these columns are parsed and rendered as tree of items. You can modify the rendering by changing the XML Expansion Level in the table visualization settings. The default is 1.