How to count all records from all tables from data container using a loop

I am trying to setup a script that would count the records from all tables from a container.
I’m almost finished, but I have only on issue… the execute immediate statement is not storing the result of the query in l_cnt. I dont understand why.

declare
  l_cnt  varchar := 0;
  l_stmt varchar;
begin
  create or replace table temp@inmemorystorage
  as
  select name 
  from   systemtables@datadictionary 
  where  provider_data_container_alias like 'fd' 
  and    type like 'table'
  ;
  for r in (select * from temp@inmemorystorage)
  loop
    begin
      l_stmt := 'select count(*) into l_cnt from ' || r.name || '@fd';
      dbms_output.put_line(l_stmt);
      execute immediate l_stmt;
      dbms_output.put_line(r.name||' : '||l_cnt);
    exception
      when others
      then
        dbms_output.put_line(r.name||' : no count');
    end;
  end loop;
end;

l_cnt always remains at 0

Nice piece of code to count the rows of all tables!

Add error logging

To analyze the “no count”, the block was rewritten to:

declare
  --
  -- Data type changed from varchar to number.
  --
  l_cnt  number;
  l_stmt varchar;
begin
  --
  -- DDL removed to reduce dependencies on existance of object and
  -- requiring another procedure to create the temp table before
  -- this block starts.
  --
  for r in 
  ( select ste.name
    ,      ste.provider_data_container_alias
    from   systemtables@datadictionary ste
    where  true
    --and    ste.provider_data_container_alias = 'fd' 
    and    ste.type = 'table'
  )
  loop
    begin
      l_stmt := 'select count(*) into l_cnt from ' || r.name || case when r.provider_data_container_alias is not null then '@' || r.provider_data_container_alias end;
      dbms_output.put_line(l_stmt);
      execute immediate l_stmt;
      dbms_output.put_line(r.name||' : '||l_cnt);
    exception
      when others
      then
        --
        -- The SQL-statement fails with something like:
        -- CounterPartyStreetAddresses has no count due to: itgensql056: Syntax error between the two '***' on line 1, column 60:
        -- So the statement is not correct.
        --
        dbms_output.put_line(r.name || ' has no count due to: ' || sqlerrm);
    end;
  end loop;
end;

Please note that the “no count” now also prints the last SQL error. The error is then displayed on DBMS output. In your environment there might be errors; please study the errors in your environment carefully.

Why is l_cnt null?

However, some statements will succeed, such as in the test environment on XML Auditfiles:

16-02-2022 08:55:03.38319 select count(*) into l_cnt from Transactions
16-02-2022 08:55:03.78821 Transactions :

Rewriting these to a PSQL-block, the block prints 98 in my case:

declare
  l_cnt number;
begin
  select count(*) 
  into   l_cnt 
  from   Transactions
  ;
  dbms_output.put_line(l_cnt);
end;

But wait, l_cnt is a PSQL-variable, and the execute immediate runs a dynamic SQL-statement. PSQL-variables should not be used in SQL-statements unless wrapped in a PSQL-block. Even worse, the use of a PSQL-variable in a SQL-statement should raise an error such as the following case:

select count(*) into dummyvariable from Transactions

But no error occurs. The variable is not assigned a value, so the result is the default null of the type.

A bug has been registered to ensure that an error is raised in a future release when a PSQL-variable is used as into within SQL-statements.

Retrieve the number of rows count into a PSQL-variable

However, the PSQL-block was already largely correct. Just one change is needed to return the number of rows counted. The into l_cnt should not be in the SQL-statement, but linked by the execute immediate statement. The essential code change is to not refer to l_cnt in the SQL-statement and instead bind the output of the last SQL-statement immediately executed using into:

l_stmt := 'select count(*) from ' || r.name || case when r.provider_data_container_alias then '@' || r.provider_data_container_alias end;
execute immediate l_stmt into l_cnt;

with result:

16-02-2022 09:03:08.57276 select count() from TransactionRegisters
16-02-2022 09:03:09.06402 TransactionRegisters : 5
16-02-2022 09:03:09.06502 select count(
) from Transactions
16-02-2022 09:03:09.55373 Transactions : 98

The full code to count the number of rows per table then becomes:

declare
  --
  -- Data type changed from varchar to number.
  --
  l_cnt  number;
  l_stmt varchar;
begin
  --
  -- DDL removed to reduce dependencies on existance of object and
  -- requiring another procedure to create the temp table before
  -- this block starts.
  --
  for r in 
  ( select ste.name
    ,      ste.provider_data_container_alias
    from   systemtables@datadictionary ste
    where  true
    and    ste.provider_data_container_alias is null
    and    ste.type = 'table'
  )
  loop
    begin
      l_stmt := 'select count(*) from ' || r.name || case when r.provider_data_container_alias then '@' || r.provider_data_container_alias end;
      dbms_output.put_line(l_stmt);
      execute immediate l_stmt into l_cnt;
      dbms_output.put_line(r.name||' : '||l_cnt);
    exception
      when others
      then
        --
        -- The SQL-statement fails with something like:
        -- CounterPartyStreetAddresses has no count due to: itgensql056: Syntax error between the two '***' on line 1, column 60:
        -- So the statement is not correct.
        --
        dbms_output.put_line(r.name || ' has no count due to: ' || sqlerrm);
    end;
  end loop;
end;

Dear Master,
the young padawan wrote this script a long time ago.
Now that the force as strengthened, it appears clear that execute immediate l_stmt into l_cnt; is the right way it should be used.
cheers

Too much honor, but still flattered.

As an update, the releases 22.0.46+ will raise an error itgensst021 when an into is used outside a PSQL-scope (such as in a SQL-statement). This should reduce future confusions by Invantive SQL users. Thanks for pointing out this usability bug.