CODEWITHSUNDEEP
sqloracle-database
JabbaWook
JabbaWook

Reputation: 685

select count(*) from table_name returns error

I am attempting query my oracle database to determine the number of entries in each table. I spooled the result of the following query to a sql file : 

select 'select count(*) '||table_name||';' from dba_tables;

This created a sql file of the format

select count(*) from Table_Name1;
select count(*) from Table_Name2;
etc

I then spooled the output of running this sql file, however it only returned a count for some of the tables, for other tables it simply says:

'This table or view does not exist'

How can that be if I've just populated that list of table names automatically from the database?

The end aim of this exercise is to have a file with the number of all fields in each table, then make a change to the program using this database and run the same operation again into a new file and compare the 2 so that I can see which tables change when I make a change to the program.

Upvotes: 1

Views: 1513

Answers (1)

user330315
user330315

Reputation:

You forgot to fully qualify the names:

select 'select count(*) '||owner||'.'||table_name||';' 
from dba_tables;

In case you have tables that have non-standard names, you should throw double quotes in there as well:

select 'select count(*) "'||owner||'"."'||table_name||'";' 
from dba_tables;

Upvotes: 6

Related Questions