Avatar of AidenA
AidenA
 asked on

plsql: get number of records from 10 different tables efficiently

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
Oracle DatabaseASP.NETSQL

Avatar of undefined
Last Comment
AidenA

8/22/2022 - Mon
slightwv (䄆 Netminder)

AidenA

ASKER
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?)
awking00

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 ).
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
AidenA

ASKER
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
slightwv (䄆 Netminder)

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.
AidenA

ASKER
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;
/
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>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,
AidenA

ASKER
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?
AidenA

ASKER
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
slightwv (䄆 Netminder)

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');
slightwv (䄆 Netminder)

>>OPEN outResults FOR    SELECT CTC_Table,

CTC_TABLE is an object not a string...

OPEN outResults FOR    SELECT 'CTC_Table',
johnsone

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
AidenA

ASKER
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)
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
AidenA

ASKER
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

slightwv (䄆 Netminder)

>>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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
AidenA

ASKER
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
slightwv (䄆 Netminder)

>>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'''"
AidenA

ASKER
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 https://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...)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>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.
AidenA

ASKER
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

AidenA

ASKER
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!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck