Kayshav.com
About Developer Oracle 12c Oracle 19c Technology Information Sitemap

Oracle Query using WITH clause based function
-- Oracle 12c: Function to compute query within a query
-- Using the WITH syntax, we can get counts for several tables that 
-- have "DIM_" in their name.  A PL/SQL function is created to 
-- compute counts in a WITH clause and calling the function in
-- a query.

WITH 
 FUNCTION fn_get_counts(i_table IN VARCHAR2)
 RETURN  NUMBER
 IS
    v_cnt   NUMBER;
 BEGIN
   --If parallel option enabled it can use parallel processing 
   EXECUTE IMMEDIATE 'SELECT /*+ parallel(t, 8) */ COUNT(*) 
    FROM '||i_table||' t' INTO  v_cnt;

   RETURN v_cnt;
 EXCEPTION 
   WHEN OTHERS THEN
    RETURN  -1;
 END fn_get_counts;
SELECT
 owner||'.'||table_name  as table_name,
 fn_get_counts(i_table => owner||'.'||table_name) table_cnts 
FROM  all_tables
WHERE INSTR(table_name, 'ALL_') >0;

Count Table Data



WITH syntax in Oracle 12c    Partition Count

WITH syntax for phone number pattern

Oracle 12c Index    Oracle 19c Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: June 28th, 2016

  11060