Creating a Database Operation

You can create a composite database operation using the DBMS_SQL_MONITOR package subprograms.

The following example creates a database operation named DBOP_EXAMPLE. The example begins the database operation. It has a PL/SQL procedure that selects the maximum sales amount by customer by city. It then has a SQL statement that selects the maximum sales amount of all customers from cities that have at least two customers. Finally, it ends the database operation.

Example 5-1 Creating a Database Operation

VAR eid NUMBER
EXEC :eid := DBMS_SQL_MONITOR.BEGIN_OPERATION('DBOP_EXAMPLE');
declare
--
v1 number;
--
CURSOR c1 IS
SELECT cust_city
  FROM (SELECT COUNT(*) cnt, cust_city 
        FROM sh.customers GROUP BY cust_city 
        ORDER BY 1 desc);
--
BEGIN
FOR i IN c1
LOOP
--
v1 := 0;
--
SELECT MAX(amount_sold)
INTO v1
FROM sh.sales
WHERE cust_id IN (select cust_id FROM sh.customers WHERE cust_city=i.cust_city);
--
DBMS_OUTPUT.PUT_LINE('Amount: '||v1);
--
END LOOP;
--
END;
/
SELECT MAX(asld) FROM 
(SELECT MAX(amount_sold) asld, cust_id FROM sh.sales WHERE cust_id IN 
  (SELECT cust_id FROM sh.customers WHERE cust_city IN 
    (SELECT cust_city FROM
     (SELECT count(*) cnt, cust_city FROM sh.customers
      GROUP BY cust_city HAVING COUNT(*) > 1)
    ))
GROUP BY cust_id)
/
 
EXEC DBMS_SQL_MONITOR.END_OPERATION('DBOP_EXAMPLE',:eid);

Note:

Starting with Oracle Database 19c, database users without the administrative privileges can also create composite database operations using the DBMS_SQL_MONITOR package subprograms and view the SQL execution details of those operations, including the execution plans and performance metrics, by navigating to the Monitored SQL Executions page of Cloud Control.