SELECT COUNT(1) FROM table in dynamic query-CodePudding

I'm iterating through an array of table name strings and passing them to the following function:

FUNCTION table_empty(table_name VARCHAR2)
RETURN BOOLEAN
AS
    row_count NUMBER := 0;
    empty_sql VARCHAR2(255);
BEGIN
    empty_sql := 'SELECT COUNT(1) FROM :tab_name';
    EXECUTE IMMEDIATE empty_sql INTO row_count USING table_name;

    DBMS_OUTPUT.PUT_LINE('Row Count: ' || table_name);

    RETURN row_count > 0;
END table_empty;

However, I get this error

Unexpected error: ORA-00903: invalid table name

Does anybody have any idea what causes this?

CodePudding user response：

Bind variables are for variables, not identifiers. The table name (and other identifiers) need to be known when the dynamic statement is parsed; variable values are then applied when it is executed. At the moment when the statement is parsed the table name is interpreted literally as :tab_name, which is an invalid name.

You need to concatenate the name:

    empty_sql := 'SELECT COUNT(1) FROM ' || table_name;
    EXECUTE IMMEDIATE empty_sql INTO row_count;

You might want to validate the table name first though.

CodePudding user response：

This is the prefered solution (avoiding the concatination of the table_name as string) using the SQL Macro (requires Oracle 19c or higher)

In a SQL Macro you may pass safe a table name as a paramater.

Define (table) SQL Macro

create or replace  function my_count (t DBMS_TF.Table_t)
return  varchar2 SQL_MACRO
is
begin
return q'[
  select count(*) cnt from t]';
end;
/

Usage for table TAB

select cnt from my_count(tab);

More information and examples of SQL Macros can be found here and there.