Using a user-defined function within a CTE

Question

I have a simple function which is used to count rows in a data frame (recycling code from this tutorial here) in an Oracle database.

CREATE OR REPLACE FUNCTION TABCOUNT (tbl_name IN VARCHAR2)
   RETURN PLS_INTEGER
IS
   retval   PLS_INTEGER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tbl_name INTO retval;

   RETURN retval;
END;

If I want to apply this function to a stored table, it executes fine:

CREATE TABLE EXAMPLE_TABLE ("ROW_ID" INT);
INSERT INTO EXAMPLE_TABLE VALUES (1);
INSERT INTO EXAMPLE_TABLE VALUES (2);

SELECT TABCOUNT('EXAMPLE_TABLE') AS "N_ROW" FROM DUAL;

However, I would like to use this type of a function inside a CTE, as shown below.

WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE)
SELECT 'T1' AS "TABLE_NAME", TABCOUNT (TABLE_1) AS "N_ROW"
  FROM DUAL;

For some reason, I can't get this to run. Can anybody show me how to do so? Apologies if this is an elementary question as I am quite new to PL/SQL functions.

The simplified reason I would like to do this in a CTE is because, using the example above, I would like to apply it to a variety of different tables in the CTE and union the results, i.e.:

Edit: In the example below, 'EXAMPLE_TABLE' and 'EXAMPLE_TABLE_2' are placeholders. Imagine that TABLE_1 and TABLE_2 are intermediate tables that result from processing further up the query. 'EXAMPLE_TABLE' and 'EXAMPLE_TABLE_2' were included simply to make the CTE in the example run.

CREATE TABLE EXAMPLE_TABLE_2 AS SELECT * FROM EXAMPLE_TABLE;

WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE),
     TABLE_2 AS (SELECT * FROM EXAMPLE_TABLE_2)
SELECT 'T1' AS "TABLE_NAME", TABCOUNT (TABLE_1) AS "N_ROW" FROM DUAL
UNION ALL
SELECT 'T2' AS "TABLE_NAME", TABCOUNT (TABLE_2) AS "N_ROW" FROM DUAL;

Answer 1

In your query when you do TABCOUNT (TABLE_1) the only TABLE_1 is the CTE; you would need to refer to a column in that CTE rather than the table itself, which isn't what you want (as the value of row_id isn't what you intended to pass as tbl_name). The CTE will return two rows with values 1 and 2, not the names of the tables they come from. But that's academic as the query isn't using the CTE - you're querying against dual.

You could just union together the table names:

SELECT 'EXAMPLE_TABLE' AS "TABLE_NAME", TABCOUNT ('EXAMPLE_TABLE') AS "N_ROW" FROM DUAL
UNION ALL
SELECT 'EXAMPLE_TABLE_2' AS "TABLE_NAME", TABCOUNT ('EXAMPLE_TABLE_2') AS "N_ROW" FROM DUAL;

but presumably you don't want to duplicate that much code, particularly if you have lots of tables to look at; so if you want to use a CTE then make that generate the names:

WITH CTE (TBL_NAME) AS (
  SELECT 'EXAMPLE_TABLE' FROM DUAL
  UNION ALL
  SELECT 'EXAMPLE_TABLE_2' FROM DUAL
)
SELECT TBL_NAME AS "TABLE_NAME", TABCOUNT (TBL_NAME) AS "N_ROW" FROM CTE;

or to avoid the repetition in the CTE, use a collection:

SELECT COLUMN_VALUE AS "TABLE_NAME", TABCOUNT (COLUMN_VALUE) AS "N_ROW"
FROM TABLE(sys.odcivarchar2list('EXAMPLE_TABLE', 'EXAMPLE_TABLE_2'));

You might find this XML-based trick useful as it doesn't need the function; adapted to use the same collection approach to supply the names and to avoid deprecated functions:

select column_value as table_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(*) as c from "' || column_value || '"'))
    returning content)) as n_row
from table(sys.odcivarchar2list('EXAMPLE_TABLE', 'EXAMPLE_TABLE_2'));

db<>fiddle

---

In the example below, 'EXAMPLE_TABLE' and 'EXAMPLE_TABLE_2' are placeholders. Imagine that TABLE_1 and TABLE_2 are intermediate tables that result from processing further up the query.

I recognize that I could just apply the function to the tables directly, but in the actual use case, TABLE_1 and TABLE_2 are tables that are the end result of processing further up the CTE (rather than just mirror copies of EXAMPLE_TABLE and EXAMPLE_TABLE_2).

You don't need a function or dynamic SQL to do this then; in fact you can't because the CTEs will be out of scope to the dynamic SQL context. But you can just do a simple count:

WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE),
     TABLE_2 AS (SELECT * FROM EXAMPLE_TABLE_2)
SELECT 'T1' AS "TABLE_NAME", COUNT(*) AS "N_ROW" FROM TABLE_1
UNION ALL
SELECT 'T2' AS "TABLE_NAME", COUNT(*) AS "N_ROW" FROM TABLE_2;

db<>fiddle

Answer 2

The problem is that you are referencing TABLE_1 and TABLE_2 as if it was a column, but it is a table. You need to pass the name of your table to the function like the example query below.

Query

WITH
    table_list (table_name)
    AS
        (SELECT 'ALL_TABLES' FROM DUAL
         UNION ALL
         SELECT 'ALL_OBJECTS' FROM DUAL)
SELECT table_name, TABCOUNT (table_name) AS N_ROW
  FROM table_list;

Improved Function

If you do actually intend to implement a function like this one, I would recommend putting in some protection to prevent SQL injection because the way it currently is, it is very vulnerable. See my improved version below.

CREATE OR REPLACE FUNCTION TABCOUNT (p_table_name   all_tables.table_name%TYPE,
                                     p_owner        all_tables.owner%TYPE DEFAULT NULL)
    RETURN PLS_INTEGER
IS
    retval   PLS_INTEGER;
BEGIN
    EXECUTE IMMEDIATE   'SELECT COUNT(*) FROM '
                     || CASE
                            WHEN p_owner IS NOT NULL THEN DBMS_ASSERT.schema_name (p_owner) || '.'
                        END
                     || DBMS_ASSERT.sql_object_name (p_table_name)
        INTO retval;

    RETURN retval;
END;
/

Answer 3

If I have understood you correct then you need something like below,

with table_list 
as 
( select 'EXAMPLE_TABLE' table_name from dual union all
  select 'EXAMPLE_TABLE_2' from dual
)
select t.table_name,tabcount(table_name)  no_of_rows
  from table_list t

However if you are on version 12c or greater you can PL/SQL declaration section in the WITH clause as well,

with 
function tabcount (tbl_name in varchar2)
return pls_integer 
is
  retval pls_integer;
begin
   execute immediate 'select count(*) from ' || tbl_name into retval;
   return retval;
end;
table_list 
as 
( select 'EXAMPLE_TABLE' table_name from dual union all
  select 'EXAMPLE_TABLE_2' from dual
)
select t.table_name,tabcount(table_name)  no_of_rows
  from table_list t

More details with respect to WITH clause here