plsql: get number of records from 10 different tables efficiently

Check out the answer here:
https://www.experts-exchange.com/questions/24096252/how-do-i-get-table-name-and-no-of-records.html?&anchorAnswerId=23502848#a23502848

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

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

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

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.

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;
/

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

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?

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?

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');

>>OPEN outResults FOR    SELECT CTC_Table,

CTC_TABLE is an object not a string...

OPEN outResults FOR    SELECT 'CTC_Table',

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.

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

Select allOpen 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;
/

Select allOpen 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)

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"}

Select allOpen in new window

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

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"

Select allOpen 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

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

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');

Select allOpen in new window

Now, if I try and alter that so num_rows = what was in https://www.experts-exchange.com/questions/24096252/how-do-i-get-table-name-and-no-of-records.html?&anchorAnswerId=23502848#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');

Select allOpen 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...)

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

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);

Select allOpen in new window

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');

Select allOpen 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!