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;