Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to Find Total Rows by Owner
In article <7cpgfs$dan$1_at_nnrp1.dejanews.com>,
Mike J McCall <mjmccall_at_my-dejanews.com> wrote:
> Is there any way to find the total number of rows in all the tables for one
> owner ? In other words, how can I do an SQL "select count(*) from
> table1,table2,table3,...' that prints one number representing the combined
> number of rows in all tables?
>
> I don't think a join or union is the answer. I also tried a cursor in PL/SQL
> that dynamically selects individual counts from each table, but this won't
> compile because the tablename cannot be a variable. I am looking to use the
> total number of rows as a simple aggregate metric for all owned tables.
>
> Any ideas thanks in advance.
>
> Software Developer from the Past
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
U cud probably try a dynamic cursor in PL/SQL or a Pro *C program. Dynamic cursor will server ur purpose.
Bcos the SQL statement is formed as a string so u can place the name of the table in that string.
I dont remember the syntax exactly.
But u can define a variable of type cursor and change the SQL statement
create a main cursor for table names owned by a particular owner.
declare a variable of type cursor.
spin thru each of this and form the SQLstring for the cursor like
vSQLString="select count(*) from" + vTableName +";"
Well just check for the syntax. If u have any questions email me.
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 18 1999 - 21:07:37 CST