-- Oracle 12c: Function to get table partition counts using WITH clause

WITH
 FUNCTION get_partition_count(p_table_name IN VARCHAR2, p_partition IN VARCHAR2)  
  RETURN NUMBER
 IS
   v_count   NUMBER :=0;
 BEGIN
   --If parallel option enabled it can use parallel processing
   EXECUTE IMMEDIATE 'SELECT /*+ PARALLEL(t,8) */ COUNT(*) FROM '||
         p_table_name||' t PARTITION ('||p_partition||')'
   INTO  v_count;
  RETURN  v_count;
 EXCEPTION
   WHEN OTHERS THEN
    RETURN -1; 
 END get_partition_count;
SELECT
  table_name,
  partition_name,
  get_partition_count(
     p_table_name => table_name,
     p_partition  => partition_name)  partition_count
FROM  all_tab_partitions
WHERE  INSTR(table_name,'FACT') >0
;