TRUNCATE TABLE rewrite_table;

DECLARE
  no_of_msgs NUMBER;
  Rewrite_Array SYS.RewriteArrayType :=  SYS.RewriteArrayType();
  i NUMBER :=1;
  querytxt VARCHAR2(1500) := 
    'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,  sum(s.quantity_sold) as quantity  FROM sales s , customers c, products p WHERE   c.cust_state_province = ''Dublin'' and (c.cust_id = s.cust_id)  AND (s.prod_id = p.prod_id) GROUP BY c.cust_id, p.prod_id';

BEGIN
  dbms_mview.Explain_Rewrite(querytxt, '', Rewrite_Array);
  no_of_msgs := Rewrite_Array.count;
  FOR i IN 1..no_of_msgs LOOP
    DBMS_OUTPUT.PUT_LINE(Rewrite_Array(i).message);
  END LOOP;
END;
/
