Create the Package Specifications
create or replace package pkg_table_record_counts
is
type record_type_t is record
(
table_name all_tables.table_name%type,
record_count number
);
type table_rec_count_coll is table of record_type_t;
procedure pr_table_record_counts(p_ref_cursor out sys_refcursor);
end pkg_table_record_counts;
Package created.
Create the Package Body
create or replace package body pkg_table_record_counts
is
procedure pr_table_record_counts(p_ref_cursor out sys_refcursor)
is
nested_table_rec_counts table_rec_count_coll := table_rec_count_coll();
cursor c_rec
is
select
table_name
from all_tables t1
where t1.owner = 'HR'
order by t1.table_name;
l_coll_index pls_integer := 1;
l_sql_string varchar2(4000);
l_rec_count number;
begin
for r_rec in c_rec
loop
l_sql_string := 'select count(*) from hr.' || r_rec.table_name;
execute immediate l_sql_string into l_rec_count;
nested_table_rec_counts.extend;
nested_table_rec_counts(l_coll_index).table_name := r_rec.table_name;
nested_table_rec_counts(l_coll_index).record_count := l_rec_count;
l_coll_index := l_coll_index + 1;
end loop;
open p_ref_cursor for select * from table(nested_table_rec_counts);
end pr_table_record_counts;
end pkg_table_record_counts;
Package Body created.
Anonymous Calling PL/SQL Block
declare
x_ref_cursor SYS_REFCURSOR;
l_table_name varchar2(100);
l_record_count number;
begin
dbms_output.put_line(rpad('Table Name', 20, ' ') || 'Record Count');
pkg_table_record_counts.pr_table_record_counts(p_ref_cursor => x_ref_cursor);
loop
fetch x_ref_cursor into l_table_name, l_record_count;
exit when x_ref_cursor%NOTFOUND;
dbms_output.put_line(rpad(l_table_name, 20, ' ') || l_record_count);
end loop;
end;
Statement processed.
Table Name Record Count
COUNTRIES 25
DEPARTMENTS 27
EMPLOYEES 107
JOBS 19
JOB_HISTORY 11
LOCATIONS 23
REGIONS 4