CODEWITHSUNDEEP
sqloracle-databaseplsql
Peadar Ó Duinnín
Peadar Ó Duinnín

Reputation: 486

SELECT COUNT(1) FROM table in dynamic query

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?

Upvotes: 1

Views: 2053

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21043

This is the preferred solution (avoiding the concatenation of the table_name as string) using the SQL Macro (requires Oracle 19.6 or higher)

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

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.

Upvotes: 3

Pugzly
Pugzly

Reputation: 934

You can do something like this and it will alleviate the need of a wrapper to pass in the table. You can add a where clause for filtering.

This make some time to complete if you have tables with a large amount of rows

declare
tab_count number(3);
sql_stmt varchar2(100);
BEGIN
FOR cur_Rec IN (Select TABLE_NAME from user_tables) LOOP
  sql_stmt := 'SELECT count(*)  FROM '|| cur_Rec.TABLE_NAME;
  EXECUTE IMMEDIATE sql_stmt INTO tab_count ;
  DBMS_OUTPUT.PUT_LINE (cur_Rec.TABLE_NAME || ' ' || tab_count);
END LOOP;
END;

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 4

Related Questions