plsql: get number of records from 10 different tables efficiently

AidenA
AidenA used Ask the Experts™
on
Hi,

I want to find out the number of records in about 10 different tables in an oracle database. What is the most efficient way of doing this without having to run 10 different 'select count(field1) from table1' queries?

If that's the only way of doing it... how would I write that? As a transaction one after the other? Is there a way of placing each result into an array and then returning that information instead of having to run each query separately from the source code?

Thanks, Aiden
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

Author

Commented:
getting error on 'from dba_tables where owner = 'mySchema'

PL/SQL: ORA-00942: table or view does not exist

Is that for dba_tables or for mySchema. How do I get the name of my schema actually... not 100% whether that is my database name... in fact, yeah, I guess my database is actually orcl and so what I 'call' my database is actually my schema (i.e. I log in using the username 'myusername'... which I guess is actually the schema name?)
awking00Information Technology Specialist

Commented:
If you're just looking for the counts of tables in your own schema, just use 'from user_tables' (no need for filter on owner ).
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
ok I changed it to this

'select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from ALL_TABLES;'

but getting error in the select part 'PLS-00428: an INTO clause is expected in this SELECT statement'

not sure what I'm suppose to be doing with that query. If I substitute in a real table for 'table_name' (not sure if that was supposed to be 'table_name' or a real table) then there is an error on the second part where for the second instance of 'table_name' (we'll call it CTC_Table) I get the error

PL/SQL: ORA-00904: "CTC_TABLE": invalid identifier
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Inside PL/SQL, a select needs a place to go.

You can return a ref cursor or some other array or even the raw XML itself.

If you can provide more detail into what you are doing with the results, we can tweak the solution.

Author

Commented:
my code was something like the below using ADO.NET and I want to move it into Oracle

SELECT Count(*) AS T1 FROM Table1
SELECT Count(*) AS T2 FROM Table2
SELECT Count(*) AS T3 FROM Table3
...
SELECT Count(*) AS T10 FROM Table10

So, it wasn't very efficient and just wanted to find a cleaner way of getting the number of rows in each table.

Currently i have something like this in Oracle... so I guess I would want to use that outResults refcursor to put an array of the values in... maybe you can clarify?

CREATE OR REPLACE PROCEDURE getSchemaInfo(outResults out sys_refcursor)
IS
BEGIN
  select CTC_Table,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '|| CTC_Table))
          ,'/ROWSET/ROW/X')) count
from ALL_TABLES;

EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
 
END;
/
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>so I guess I would want to use that outResults refcursor to put an array of the values in

You can use a proceudre if you want to or just issue the select in the .Net code just like you did the individual counts.  You can then use a dataReader for whatever processing you wish.

If you wish to still use the procedure make the following changes:

CREATE OR REPLACE PROCEDURE getSchemaInfo(outResults out sys_refcursor)
IS
BEGIN
  open outResults for  select CTC_Table,

Author

Commented:
need to use procedures (well, not absolutely necessary, but that's what I was asked to do for all the queries)

so, still getting the error in the second line 'to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from ' || CTC_Table))'

where it says 'ORA-00904: "CTC_TABLE": invalid identifier'

there must be something syntatically wrong there? the table name is correct?

Author

Commented:
wait yeah, something's off there... if i change it to

  OPEN outResults FOR  
  SELECT CTC_Table,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from CTC_Table'))

that looks more like it's probably correct, but now the error is in the 'Select CTC_Table' line. I assume a table isn't suppose to be in there then is it?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
table_name in the original is a variable.  for a single table, you need a string literal:

to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from CTC_TABLE'))'


For your original reaulrement, for 10 tables, leave the select as-is and add the list of table names to the where clause (if al lthe tables are in the same schema, use USER tables):


select 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 in ('CTC_TABLE'','NEXT_TABLE');
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>OPEN outResults FOR    SELECT CTC_Table,

CTC_TABLE is an object not a string...

OPEN outResults FOR    SELECT 'CTC_Table',
johnsoneSenior Oracle DBA

Commented:
How accurate do your counts need to be?  If accuracy to the last time that statistics was run is good enough, then this should work:

select table_name, num_rows from user_tables where table_name in ('CTC_TABLE','NEXT_TABLE');

Should be a lot faster as well.  However, you do sacrifice accuracy.

Author

Commented:
Hey, I guess I'm not really understanding how this query works. Ok, I tried the following using ODP and VB.NET

Dim Table_List() As String = {"CTC_Table", "Online_Queries"}

         lstOracleParameter = New List(Of OracleParameter)
         lstOracleParameter.Add(New OracleParameter("Table_List", OracleDbType.Varchar2, Table_List, ParameterDirection.Input))
         lstOracleParameter.Add(New OracleParameter("resultCursor", OracleDbType.RefCursor, ParameterDirection.Output))

         dataReaderODP = objCC.ExecuteQuery("ExecuteReader", "getSchemaInfo", lstOracleParameter)

         If dataReaderODP.HasRows Then
            While (dataReaderODP.Read())
               Dim strOutPut As String = dataReaderODP(0)
            End While
         End If

Open in new window


Then in the database the query is written as

CREATE OR REPLACE PROCEDURE getSchemaInfo(inTable_List in varchar2, outResults out sys_refcursor)
IS
BEGIN
  OPEN outResults FOR  
  SELECT inTable_List,
  		 to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from ' || inTable_List))
         ,'/ROWSET/ROW/X')) count
from USER_TABLES;

EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL; 
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  
END;
/

Open in new window


but, when I ran this, I got the following error? Can you maybe be more explicit as to what exactly I need to write here, because I'm obviously not getting it!?

ORA-19202: Error occurred in XML processing ORA-00933: SQL command not properly ended ORA-06512: at "SYS.DBMS_XMLGEN", line 176 ORA-06512: at line 1

(I need accuracy for this by the way so I suppose this method is the appropriate solution)
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>inTable_List

By the name I assume this is a CSV list of multiple tables?

If so: re:http:#a37997877,  leave it table_name and add the list to the where clause.

Also, debug pieces individually.  Make sure the procedure works before you try calling it from .Net.  Then you know where the error is.

Try the following tweaks to your procedure and my test of it.

The var and exec commands are for sqlplus.

CREATE OR REPLACE PROCEDURE getSchemaInfo(inTable_List in varchar2, outResults out sys_refcursor)
IS
BEGIN
  OPEN outResults FOR
  'SELECT 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 in (' || inTable_List || ')'
;

EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL; 
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  
END;
/

show errors

var mycur refcursor

exec getSchemaInfo('''TAB1'',''TAB2''', :myCur);

print mycur

Open in new window

Author

Commented:
getting the following error now, but the number of parenthesis look ok to me...

ORA-00907: missing right parenthesis ORA-06512: at "CTC.GETSCHEMAINFO", line 15 ORA-06512: at line 1

btw, inTableList is just an ordinary array of strings...

Dim Table_List() As String = {"CTC_Table", "Online_Queries"}

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>getting the following error now, but the number of parenthesis look ok to me...

Look at the strings you are passing into Oracle and what I used in my test.

You need to make sure the single quotes work out properly and what you pass in is a single string not an array of strings.

Author

Commented:
ok changed to normal string (sorry for making a mess of this... just wasn't sure what format I'm suppose to be using for these things). Tried the below, also tried with "CTC_Table, Online_Queries"

Dim Table_List As String = "CTC_Table"

Open in new window


again getting error for the above case

ORA-00904: "CTC_TABLE": invalid identifier ORA-06512: at "CTC.GETSCHEMAINFO", line 15 ORA-06512: at line 1
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>ORA-00904: "CTC_TABLE": invalid identifier

Check my syntax in the test I posted.  Look at the single quotes and number/placement.

Alos, Oralce object names are upper case by default.

Try:
Dim Table_List As String = "'''CTC_TABLE'', ''ONLINE_QUERIES'''"

Author

Commented:
tried that and there was no error but also no data was returned. I'm trying to simplify but not getting anything. For instance, i checked, and the query below works fine

SELECT table_name, num_rows FROM user_tables WHERE table_name IN ('CTC_TABLE');

Open in new window


Now, if I try and alter that so num_rows = what was in http://www.experts-exchange.com/Database/Oracle/10.x/Q_24096252.html?#a23502848 then I get

SELECT 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 in ('CTC_Table');

Open in new window


but whether I do it that way, or the way you suggested ("'''CTC_Table'', ''Online_Queries'''") I'm still getting no data returned... (whereas I do get data for the first query above as I say...)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>I'm still getting no data

Are you passing in the table names in as UPPER case?  re: http:#a38002831

Test the procedure outside your .Net code.  Verify you get data, then test with .Net.

Author

Commented:
argh! ok... put into uppercase and the following query worked... so nearly there

Dim Table_List As String = "CTC_TABLE"

However, this did not work

Dim Table_List As String = "'CTC_TABLE'" (adding the single quotes around it... )

and more importantly, this did not work

Dim Table_List As String = "CTC_TABLE, ONLINE_QUERIES"

The query currently looks like this (prefer to not incase the whole thing in a string i think). So, it worked with the first string just using CTC_TABLE with no single quotes

SELECT 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 in (inTable_List);

Open in new window

Author

Commented:
actually, most importantly this DOES work

SELECT 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 in ('CTC_TABLE', 'ONLINE_QUERIES');

Open in new window


So, I think that's good enough for me. Don't necessarily need to feed the table list in to the query

Thanks for your help again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial