Hi

I am using below for counting rows, but it's throwing table or view does not exist at line 7. Can you please guide me a better way without creation of fucntion or stored procedure on DB?

declare
v_count integer;
begin

for r in (select table_name,owner from all_tables at WHERE table_name not like'%$%' )
loop
execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;
INSERT INTO ROWCOUNT_TBL(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;

end;

Regards
Hari

execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;

The first reply by Tom in this discussion, in which he created a user defined function can now in 12c be done in single SQL without creating function but using the same function in WITH clause

WITH
function get_rows( p_tname in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
end;
select user, table_name,
get_rows(user||'.'||table_name) cnt
from user_tables

right?

What you hope to achieve? Can you please explain?

One thing is performance is better than creating the udf and calling it.

May be to see whichbtables are large enough to consider for partitioning

A valid reason to do it might be to validate an external ETL process that claims to have inserted a precise number of rows into a set of tables, and then stopped. If that process is the only one that can insert rows, the counts can be used to validate that claim. I can't think of another way to do it.

SQL%ROWCOUNT may have your answer.

The first solution Tom gave, and the solution I (Asim) gave on 8th August 2022 ( which is just copying the tom's function in the WITH clause), and the video of connor, none of us considered or said anything about the transaction isolation level, which means we are assuming default ie statement level, due to which the solution may produce read consistency issues of SQL calling PL/SQL calling SQL mentioned here

https://blogs.oracle.com/sql/the-problem-with-sql-calling-plsql-calling-sql


Here is the query for ready referrence

WITH
function get_rows( p_tname in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
end;
select user, table_name,
get_rows(user||'.'||table_name) cnt
from user_tables


So, I think we should set the transaction isolation level to serializeable or read only to get read consistency, otherwise if some other users change the no. of rows (delete or insert) in any of the tables during execution of above query, the results will not be of same point in time

Hi Tom,
pls help me i have a requirement ,
in my database there are 50 tables i have to validate 10 tables only for count, without writing select count(*) from table...each time
I have to place required table names in single file and parameterize that file, so whatever the table names in that file that count should i need to get
pls put your comments asap

I am running Change Data Capture (Differential Extract) from Oracle to Microsoft Sql Server.
It has faults.
In order to troubleshoot, I compare the count of record on Oracle with the result count of record on MSSQL.
This gives me an idea what is wrong.