asked on Add sequence number to a query.
Experts,
There are 203 Tables in my Schema. The code below gives me the count of number of records in each table.
declare
v_rowcnt integer;
begin
for t in (select table_name from user_tables order by 1) loop
execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
dbms_output.put_line(rpad(
end loop;
end;
/
--########################
The first few lines of the output look like this:
ACCOUNT...................
AD........................
AD_AGE_STATED.............
AD_BODY...................
AD_CATEGORY...............
AD_LOCATION_STATED........
AD_LOCATION_WEBSITE.......
AD_NAME_ADVERTISED........
--#####################
I would like the output to look below. Add another column to the right (First Column) to tell the sequence number of the table. How do I modify the script above to get the desired result.
1 ACCOUNT...................
2 AD........................
3 AD_AGE_STATED.............
4 AD_BODY...................
5 AD_CATEGORY...............
6 AD_LOCATION_STATED........
7 AD_LOCATION_WEBSITE.......
8 AD_NAME_ADVERTISED........
There are 203 Tables in my Schema. The code below gives me the count of number of records in each table.
declare
v_rowcnt integer;
begin
for t in (select table_name from user_tables order by 1) loop
execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
dbms_output.put_line(rpad(
end loop;
end;
/
--########################
The first few lines of the output look like this:
ACCOUNT...................
AD........................
AD_AGE_STATED.............
AD_BODY...................
AD_CATEGORY...............
AD_LOCATION_STATED........
AD_LOCATION_WEBSITE.......
AD_NAME_ADVERTISED........
--#####################
I would like the output to look below. Add another column to the right (First Column) to tell the sequence number of the table. How do I modify the script above to get the desired result.
1 ACCOUNT...................
2 AD........................
3 AD_AGE_STATED.............
4 AD_BODY...................
5 AD_CATEGORY...............
6 AD_LOCATION_STATED........
7 AD_LOCATION_WEBSITE.......
8 AD_NAME_ADVERTISED........
Oracle Database
Last Comment
slightwv (䄆 Netminder)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>>Would you written it any differently.
Yes.
I would use the XML trick in the thread I posted.
That is sort of why I posted it........
For ALL tables, remove my where clause. I just didn't want to return them all for this small test.
Yes.
I would use the XML trick in the thread I posted.
That is sort of why I posted it........
For ALL tables, remove my where clause. I just didn't want to return them all for this small test.
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 char(1));
create table tab2(col1 char(1));
insert into tab1 values('a');
insert into tab2 values('a');
insert into tab2 values('a');
commit;
SELECT row_number() over(order by table_name) rn, table_name,
TO_NUMBER(EXTRACTVALUE(
xmltype(DBMS_XMLGEN.getxml('select count(*) X from ' || table_name)),
'/ROWSET/ROW/X'
))
COUNT
FROM user_tables
where table_name like 'TAB%';
With your guidance, the code below works. Would you written it any differently.
--########################
declare
v_row integer := 0;
v_rowcnt integer;
begin
for t in (select table_name from user_tables order by 1) loop
v_row := v_row + 1;
execute immediate 'select count(*) from ' || t.table_name into v_rowcnt;
dbms_output.put_line (rpad(v_row, 5, ' ') || (rpad(t.table_name,40,'.')
end loop;
end;
/