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;

The CTE's name is only known in the scope of the query. The function resides outside the query, so it does not know what TABLE_1 and TABLE2 are. What you actually want to do is pass the CTE as a cursor to the function, so the function can read the rows and return the count. I tried this, but have not been able to accomplish that. It looks like it's not possible to define the function with a sys_refcursor parameter and then pass the CTE to this function. Well, maybe someone else knows better :-) — Thorsten Kettner, Oct 09 '20 at 17:57

score 4 · Accepted Answer · answered Oct 09 '20 at 16:30

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

Alex Poole · Answer 2 · 2020-10-09T17:24:47.503

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

Thanks for the response Alex. Is there any way to make this work per my comment on Sujitmohanty30's post above? I perhaps wasn't entirely clear in my question so apologies for the clarification — DJC, Oct 09 '20 at 17:13
Why do you need the function then? You can't use dynamic SQL in that function to refer to (i.e. count) transitory data in a CTE, but is there a reason you think you need to do that? — Alex Poole, Oct 09 '20 at 17:21
gotcha. the simplified use case is that i have a list of clients who participated in a program with start and end dates for each. i cross join that table to a full calendar table to get one row per client per day. i then obtain the average number of active clients per day over the entire year. i wanted to write a function to automate the process of the cross join --> yearly average as I have to do this for five different programs. however, now that i'm writing it out, I realize I can just union the five program tables w/ start and end dates into 1, and then perform all remaining steps — DJC, Oct 09 '20 at 17:27
also recognize that i could use the simple count(*) function to accomplish what i wanted in the original query - was using the created function just to mimic how my endeavored upon end-function would interact with the table (i.e. select function(table_name) from dual). thanks again for the help w/ this — DJC, Oct 09 '20 at 17:29
@DJC, I don't see any comments and probably got deleted. As I understood now, your original question was completely different and it was missing all these information which you provided at later point. Nice that you have the understanding now to go ahead so I wouldn't ask any questions further. Thank you. — Sujitmohanty30, Oct 09 '20 at 17:52

score 1 · Answer 3 · answered Oct 09 '20 at 16:32

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;
/

Using a user-defined function within a CTE

3 Answers3

Query

Improved Function